If (condition1) AND (either condition 2 3 or 4)

**jbird123** · 07-14-2010, 05:18 AM

Hi, looking for some help.

At the moment I have the following formula

Please Login or Register  to view this content.

This is ok, if I5 and J5 are both bigger than 0 then display '1'. However I now need to add to that statement 'AND (IF EITHER L15, L16 or L17 > 0)'.

Its quite hard to explain in text, not sure if this makes any sense so see attached XLS.

I dont think it should be difficult, just getting the syntax right and I dont use excel much any more

**arthurbr** · 07-14-2010, 05:32 AM

Does this help ?

=IF((AND(I5>0,J5>0,or(l15>0,l16>0,l17>0)),1,"")

**DonkeyOte** · 07-14-2010, 05:39 AM

Or, another variant:

=IF(AND(MIN(I5:J5)>0,MAX(L15:L17)>0),1,"")

**jbird123** · 07-14-2010, 05:40 AM

Judging by the cells that it selected I think its exactly what I need, however excel is telling me theres something wrong with the formula? :S

EDIT: Both of those say that there is somethign wrong with the formula (does say what though!).. both are selecting the right cells though. Must be something to do with the brackets or "'s or something?

**DonkeyOte** · 07-14-2010, 05:48 AM

There's nothing wrong with edit: my formula based on your locale (UK)

If you're using non-UK settings such that your delimiter is something other than comma then you will need to modify accordingly... eg for Contintenal Europe:

=IF(AND(MIN(I5:J5)>0;MAX(L15:L17)>0);1;"")

EDIT: though it might help if I actually referenced the correct cells !

**jbird123** · 07-14-2010, 06:00 AM

Originally Posted by DonkeyOte

EDIT: though it might help if I actually referenced the correct cells !

Lol yeah I did change that, tis ok.

Thats strange then, maybe its something to do with excel saying at the top 'compatability mode'? Thats the only thing I could think of!

Sorry the screenshot is a gif (and why does it think its a jpg :S ) but it was too big to upload as a jpg or png

**DonkeyOte** · 07-14-2010, 06:42 AM

There is a superfluous parenthesis in the embedded IF approach, should read:

=IF(AND(I5>0,J5>0,OR(L15>0,L16>0,L17>0)),1,"")

The other formula (MIN/MAX) should not require any alteration.

**jbird123** · 07-14-2010, 07:12 AM

Originally Posted by DonkeyOte

There is a superfluous parenthesis in the embedded IF approach, should read:

=IF(AND(I5>0,J5>0,OR(L15>0,L16>0,L17>0)),1,"")

The other formula (MIN/MAX) should not require any alteration.

Woo yeah that works! Thank youu