Find the MAX value of changing sets

[Gunther Maplethorpe]

I've been fiddling with how to do this for a coupe of hours, to no avail. Time to ask the experts!

Given three columns (SET, SAMPLE, and SIZE), I'd like a formula in a fourth column that give the maximum value of SIZE for all rows that match both SET and SAMPLE. There's got to be a simple way to do this, but my brain just isn't functioning at 100% today.

My only real restriction is that it must be a formula, not VBA or anything.

Example datasheet attached.

[AlKey]

Enter formula in E2 and copy down

Please Login or Register  to view this content.

[Gunther Maplethorpe]

Well, that was quick! Works perfectly, too...and now I've learned something new about INDEX, which I'm sure will come in handy in the future. Thanks!

[Gunther Maplethorpe]

Is there a reason this doesn't seem to work with MIN? In the attached example, I would expect cell E2 to return 12.5...

[AlKey]

Is there a reason this doesn't seem to work with MIN? In the attached example, I would expect cell E2 to return 12.5...

MIN would require a different array formula

Enter in E2 and copy down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[Gunther Maplethorpe]

That doesn't seem to be working; it's returning only the top-most value for each subset of data. (And yes, I've entered it as an array.)


What's the underlying reason why the formula for MAX won't work the same for MIN?

[AlKey]

Must likely you didn't enter formula as an array. BTW, your comment, you put in F2 is wrong. The result of 12.5 can only be achieved if you drop the second criteria, ("Lower Marker").

Please see attached file with the same formula as in post#5

[Gunther Maplethorpe]

Ha...yep, I'm just dumb enough to not see the forest for the trees. Thanks, again.

I'm still curious as to the underlying reason why the formula used for MAX doesn't work for MIN, though.

[AlKey]

The MAX function calculates values that are >=0 where MIN function has to perform calculations for the values that are >=0 and <=0 hence the different formula is required.