Select Max value from range, where row contains (X) in another column

[hfidgen]

Hiya,

As the title suggests I'm struggling to find a formula that lets me select the max (and min) values out of a column, but only when another column matches the string "Complex"

Column E = "Complex" or "Simple"
Column V = The values, from which max and min need to be selected

I've managed to calulate the mean, but the other two elude me:

=AVERAGEIF(Data!E3:E100,"Simple",Data!V3:V100)

Can anyone help?

 

[Weaver]

[strike]Which version of Excel are you using?[\strike]
Scratch that, it, I read your post again and realised the example was of the one you have working. So it must be 2007 or later.

 

[hfidgen]

Good point! - It's 2010

 

[Weaver]

try:

=MIN(IF(Data!E3:E100="Simple",Data!V3:V100))

You need to use ctrl+shift+enter as this is an array formula

It should appear with curly brackets {} but you can't type or edit these.

HTH

 

[hfidgen]

The Max version works well, thank you.

The MIN version though returns 0 unless ALL the cells have a value (some of my cells are blank / dataless).

Is there a way to force it to discount these cells?

 

[Weaver]

Sorry for late response

try:

=MIN(IF((data!E3:E100="Simple")*(data!V3:V100<>""),data!V3:V100))

again, this is an array formula