How can I avoid large, nested IF AND OR queries

Hello,

here's [what should be] a simple formula I ofen struggle with; nested IF statements.

In the attached spreadsheet I need to carry out 15 numerical checks based on what the current value of another field is. Is this possible in a shorter formula to avoid nesting 15 IF AND OR formulas.

As explained in the spreadsheet:

B4 to B18 is static
C4 to C18 is variable
E4 to E18 is variable within a range

Each cell in the range G4 to G18 needs to carry out 15 TRUE/FALSE tests (examples below), but within a single formula

If any one of the tests are TRUE then a return of TRUE is required

Hope someone can help.

Regards,

sherlock99.

This will return you 0/1

=(B4=ROWS($A$1:A1))*(C4<0)

This will return you FALSE/TRUE

=(B4=ROWS($A$1:A1))*(C4<0)=1

But I still don't know what E column shoudl do?

I don't understand why G16 should be TRUE given G16 is 1 and C4 is 10 (ie not < 0)

Based on my interpretation:

Please Login or Register  to view this content.

If G16 were 13 then the result would be TRUE

(you could use an INDEX approach also but SUMIF is perhaps more robust)