If And Or formula

[Fazila]

I know I am missing something really basic here and would be grateful if someone could point out to me what.

If the values in certain subjects are greater than or equal to 4 then I want the formula to return "Y" else "N". The formula I am using is:

=IF(AND(OR(AA2>3,AB2>3),AC2>=4,OR(AD2>=4,AE2>=4,AF2>=4,AG2>=4),OR(AH2>=4,AI2>=4),OR(AJ2>3,AK2>3)),"Y","N")

AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
English	English Lit	Maths	Science	Biology	Physics	Chemistry	Geography	History	French	Spanish
6	8	8	8.5					7

<tbody>
</tbody>

This should return an N, however, I am getting Y. When I entered 0 into AJ and AK I got the value N. How can I adapt the formula to equate blank cells to a value of 0.

Thanks for your help.

Fazila

 

[JackDanIce]

What is difference in your example of >3 and >=4, why is it not consistent? 4 is > 3 and 4 is >=4

Below gives me "N" for your example and "Y" if I add a value of 4 or more to any empty cell in range AA2:AK2

=IF(--(--(OR(AA2>3,AB2>3))+--(AC2>3)+--(OR(AD2>3,AE2>3,AF2>3,AG2>3))+--(OR(AH2>3,AI2>3))+--(OR(AJ2>3,AK2>3))>3),"Y","N")

What about art subjects like music, drama or art?

If 1 represents "Y" and 0 represents "N" then you can shorten formula to:

=--(--(OR(AA2>3,AB2>3))+--(AC2>3)+--(OR(AD2>3,AE2>3,AF2>3,AG2>3))+--(OR(AH2>3,AI2>3))+--(OR(AJ2>3,AK2>3))>3)

 

[Peter_SSs]

Sounds like you might have formulas in AA2:AK2, or at least some of those cells, and those 'blank' cells might contain "". Is that correct?

If so, could you change those formulas so that they return 0 instead of ""?