Nested If Formula Help

[ffi77]

Hi,

I am trying to enter the following formula however I know it has to many Nested Ifs but can't figure out a way round it:

=IF(OR(GS3="Y",P3=0),0,IF(AND(AC3<AB3,AJ3<AG3*1.5),0,IF(CV3>CU3,CV3,
IF(CU3>CT3,CU3,IF(CT3>CS3,CT3,IF(CS3>CR3,CS3,IF(CR3>CQ3,CR3,
IF(CQ3>CP3,CQ3,IF(CP3>CO3,CP3,IF(CO3>CN3,CO3,IF(CN3>CM3,CN3,
IF(CM3>CL3,CM3,IF(CL3>0,CL3,0))))))

I've tried to amend it by doing &IF but think I've went way off course with that. Can anyone point me in the right direction?

Many Thanks
Fraser

 

[dave3009]

Is it something like

=IF(OR(GS3="Y",P3=0),0, MAX(CL3:CU3))

 

[ffi77]

Is it something like

=IF(OR(GS3="Y",P3=0),0, MAX(CL3:CU3))

Cheers Dave, I stumbled across that by compelte accident just after I'd posted! Thanks for your help.

 

[Andrew Poulsom]

Actually it isn't MAX the way you have written your formula. For example if CL3:CV3 contained:

8,7,6,5,4,3,2,5,4,3,2

your formula would have returned 5, being the first value that is greater than the value to the left working backwards. The maximum value is 8. If no cell was greater than the cell to the left as in:

10,9,8,7,6,5,4,3,2,1,0

the value in CL3 would be returned provided that it was greater than zero, otherwise zero would be returned.

If you still want that you can use this as the last part of your formula:

=IF(ISNA(LOOKUP(2,1/(CL3:CU3<=CM3:CV3),CM3:CV3)),MAX(0,CL3),LOOKUP(2,1/(CL3:CU3<=CM3:CV3),CM3:CV3))