Excel with AND, IF, MAX

Question

I can't quit figure out what I am doing wrong here, I know how to do the formula in multiple steps but can not figure out how to do it in one step all together. So what I am trying to do here is in column A there is numbers 1-27 and in column B there is a weight assigned to them.

The problem I have is I have to find the max weigh in the values given. As you can see I have put from 1 to 10 and the max load should be 50. If i do it the easy way I can get that. On the right side you see a long formula and that is me trying to combine all the formulas into one. But the answers keeps coming out to 235 which is the biggest value I have. Can someone please help me figure out what I am doing wrong?

Answer 1

Well, I learned today that the And() function doesn't work inside of an array formula! In any case, try this:

=MAX( IF(A3:A29>=$G$1, IF(A3:A29<=$G$2,$B$3:$B$29) ) )

(and don't forget to ctrl+shift+enter)

Answer 2

You should use an array formula. I tried your data and i found out that the following one works:

 =MAX(IF((A3:A29>=$G$1)*(A3:A29<=$G$2);B3:B29;0))

The "multiplication" is a workaround to the unavailable "AND" operator in array formulas.

Some "workarounds" to the unavailable logical operators are described in this link.

Just make sure that after you type this formula, you don't just press "Enter" but "Ctrl+Shift+Enter".