How to calculate average, minimum and maximum if criteria in former column is met.

[Hadekkk]

Hi,

I'm trying to calculate the average, minimum and maximum number of each different theme in column B. Please see the attached workbook file.

Example_Count .xlsx

Schermafbeelding 2012-06-24 om 22.30.32.png

Could someone help me with the correct formula's? Thank you!

Kind regards,
Hans

[jeffreybrown]

Hi Hans & Welcome to the Forum,

F3 =IFERROR(AVERAGEIF($B$3:$B$32,E3,$C$3:$C$32),"")

Array Entered (Ctrl + Shift + Enter)
G3 =IF(F3<>"",MIN(IF($B$3:$B$32=E3,$C$3:$C$32)),"")
H3 =IF(F3<>"",MAX(IF($B$3:$B$32=E3,$C$3:$C$32)),"")

[Hadekkk]

Hi Jeff, thanks for the fast reply.

I know i'm doing something wrong.. When entering the formula (=IFERROR(AVERAGEIF($B$3:$B$32,E3,$C$3:$C$32),"")) in F3 and then Entering, i see it as text. When entering for instance a 1 between the "" i get a prompt: The formula contains an error.

Am i doing something wrong?

Thanks!
Hans

[jeffreybrown]

Hi Hans,

When you say you see text, do you mean you see the formula and not a number?

If this is the case, for some reason the cell is only recognizing itself as text.

Do this, on that cell >> Ctrl + H
Find what: =
Replace with: =

[Cutter]

If, in F3, you are seeing this: (=IFERROR(AVERAGEIF($B$3:$B$32,E3,$C$3:$C$32),""))
You have enclosed the entire formula (including the equals sign) within brackets. Remove the outermost set of brackets.

[jeffreybrown]

@Cutter,

Thanks I missed that part

[Cutter]

The way post #3 is worded it's hard to tell. It could be taken as that's what's in F3 or OP was just specifying what formula was being used and enclosed the formula in brackets just to show it.

[Hadekkk]

Hi all, Thanks for your replies.

I entered in F3 as follows:

=IFERROR(AVERAGEIF($B$3:$B$32,E3,$C$3:$C$32),"")

Then i enter in G3:

=IF(F3<>"",MIN(IF($B$3:$B$32=E3,$C$3:$C$32)),"")

Then entered in H3:

=IF(F3<>"",MAX(IF($B$3:$B$32=E3,$C$3:$C$32)),"")

All cells then show the formula's as text.


I tried the replacement of the "=" but got the "This formula is not correct" prompt immediately.

Is is possible to add the formula's in the original excel and then upload it again. Then i can see what i am doing wrong.

Boy, what a noob ej!


Thanks a lot!

[daddylonglegs]

Hello Hans,

If you are in Amsterdam then your regional settings probably require you to use semi-colons as separators in those formulas, rather than commas, e.g. for the first

=IFERROR(AVERAGEIF($B$3:$B$32;E3;$C$3:$C$32);"")

and the same for the other two - the last two are "array formulas" which means, on a Mac, you need to confirm with COMMAND+RETURN so that curly braces like { and } appear around the formulas