Excel Formula 1

[Mark2Aus]

Hi everyone.
I can't get this right. I want a single formula to determine the highest value in a Range which fullfils two separate conditions.

B C D E F G H
32 25 17 10 33 36 42

{=MAX(IF(AND(B3:F3>=G3-5,B3:F3>=H3-10),B3:F3,0))}

It should return 33 but it returns always 0. Why?
Thanks
Mark

 

[Blorf]

I am guessing that the returned 0 is a logical false.

Any way, can you explain the conditions? Looks like you are taking a range, b - f, and comparing it to such and what not, but I am confused. Maybe with more info I can see an alternative.

ChaZ

 

[byundt]

Try this array formula instead:
=MAX(IF(((B3:F3>=G3-5)*(B3:F3>=H3-10)),B3:F3,0))
Remember to Control + Shift + Enter

The AND function in an array formula applies to all of the array possibilities at once, and so is easily satisfied. As a result, it doesn't exactly do what you want. By multiplying two Boolean expressions in the suggested formula, you get the equivalent of the AND, only it works in an array formula.