Find the max column value given certain restrictions or filters on the data set

[welchs101]

Please find the enclosed file as this would help explain what i am trying to do.

I want to find the maximum value for a given LotID when the status is not equal to "closed"..........any thoughts?

[DGagnon]

Give this array formula a shot

=MAX(IF(--(A$2:A$114=TEXT(F2,0))*--(B$2:B$114<>"Closed"),C$2:C$114,0))

confirmed with Ctrl+Shift+Enter

[Fotis1991]

In G2

=MAX(IF($A$2:$A$114=F2;$B$2:$B$114="Closed";$C$2:$C$114))

Array formula(C+Sh+E))

[welchs101]

I tried both.......

Fotis1991: i could not get yours to work....every time i pressed cntr+shift+enter.....would highlight the F2 in the cell.

DGagnon : Looks like yours worked.......just wish i understood the formula.

[DGagnon]

sure i can break it down

because it is an arry formula, it will exicute the IF statements individualy, so it will look at each row, it will do this

If A2=F2 and (signified by the *) B2<>Closed then return C2 else return 0
If A3=F3 and (signified by the *) B3<>Closed then return C3 else return 0
If A4=F4 and (signified by the *) B4<>Closed then return C4 else return 0
.
.
.
If A114=F114 and (signified by the *) B114<>Closed then return C114 else return 0

it returns all of those values to the MAX formula, which finds the highest returned value.


hope this helps.

[Fotis1991]

Hi

First i had to told you not in g2, but in h2.

Secondly, DGagnon, is more cleaver than me and noticed the point! Point is the Text function.

Because if you put, =F2=A2, you will get a False! Looks that are not the same values.

My poor English does not allow me to explain better....

Well done, DGagnon

[DGagnon]

Thank you Fotis, Much appreciated.

[welchs101]

thanks for the info.

[welchs101]

quick question.......

what are the "--" in the formula's what do they do?

[DGagnon]

it basicaly converts the TRUE or FALSE that is retunred by the formula into a 1 or 0

[daddylonglegs]

.....but in this case the * which multiplies the two conditions does that for you so you can safely removed the two instances of -- and the formula should still work. My preference would be this version

=MAX(IF(A$2:A$200=F2&"",IF(B$2:B$200<>"Closed",C$2:C$200)))