IF Statement Help (again)

[afv123]

I have a lot of trouble with IF statements. I'm trying to use a series of IF statements to summarize data vs. a pivot table and having to remember to refresh the Pivot when the data changes.

I would like to find the Minimum, Average, and Maximum from a series of dollar values based on an assigned category.

I'm summarizing data by each category and based on the category I want to find the min, the average, and the max. In the file, there is a Category column (4 categories 1-4) and a $ column. I want to summarize the min, avg, max of how much $'s are in each category.

I tired the following for average, and it's not working:
averageif(category column F:F, category on summary, $ column)

 

[NickFru]

Hello - just to give you an example of combining IF & MAX together with AVERAGE

Name. Sales
John. 100
Joe. 102
Chris. 104
Amy. 106

Let's say we want to enter a condition with If and max by saying;

=IF(MAX(Sales Range)>100, AVERAGE(Sales Range)). This would equal 103. However, say if the condition is > 150, it would return FALSE. Basically, we are asking the question, is the MAXIMUM value in the range greater than 100, yes, is it greater than 106, no, therefore it will return a FALSE. If you wanted to return nothing, just enter, "") after the AVERAGE. Hope this may help.

 

[afv123]

Attached is an example of what I'm trying to get to, in the fields that I've placed the "?" using the data listed below in the example.

 

[Joe4]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With Excel 2019, they introduced new functions called MAXIFS, MINIFS, and AVERAGEIFS that do exactly what you want - get the MAX, MIN, and AVERAGE values based on certain conditions.

 

[dreid1011]

Something like this:

If your version of Excel supports them, you can use the formulas in columns B:D, otherwise something like the formulas in F:H.

Book1
	A	B	C	D	E	F	G	H
1	SUMMARY
2	Category	Min	Average	Max
3	1	$50.00	$75.00	$100.00		$50.00	$75.00	$100.00
4	2	$25.00	$37.50	$50.00		$25.00	$37.50	$50.00
5	3	$20.00	$47.50	$75.00		$20.00	$47.50	$75.00
6	4	$10.00	$67.50	$125.00		$10.00	$67.50	$125.00
7
8
9	DATA
10	Category	Amount
11	1	$50.00
12	1	$100.00
13	4	$10.00
14	2	$25.00
15	3	$75.00
16	4	$125.00
17	2	$50.00
18	3	$20.00
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=MINIFS($B$11:$B$18,$A$11:$A$18,A3)
C3:C6	C3	=AVERAGEIF($A$11:$A$18,A3,$B$11:$B$18)
D3:D6	D3	=MAXIFS($B$11:$B$18,$A$11:$A$18,A3)
F3:F6	F3	=MIN(IF($A$11:$A$18=A3,$B$11:$B$18,""))
G3:G6	G3	=AVERAGE(IF($A$11:$A$18=A3,$B$11:$B$18,""))
H3:H6	H3	=MAX(IF($A$11:$A$18=A3,$B$11:$B$18,""))

 

[afv123]

Great, thank you

 

[dreid1011]

You're welcome, and happy to help. You should follow Joe's suggestion too and update your profile to show which version of Excel you are using so you get the most accurate help in the future.