How can I avoid large, nested IF AND OR queries

[sherlock99]

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.

[zbor]

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?

[DonkeyOte]

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

Based on my interpretation:

G4:
=SUMIF($B$4:$B$18,$E4,$C$4:$C$18)<0
copied down

If G16 were 13 then the result would be TRUE

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

[sherlock99]

Thanks for the replies.

I didnt explain properly zbor, sorry , but DonkeyOte has somehow read my mind and correctly interpreted my requirements.

The idea was that if the chosen team number in column E and the looked up value in column C (by virtue of its association with the colun B number) was <0 then give a TRUE value.

For example, I change E4 to 6. I want G4 to look and see if C9 is <0.

Many thanks for your replies.

Regards,

Sherlock99.