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")

[TABLE="width: 500"]
<tbody>[TR]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[TD]AK[/TD]
[/TR]
[TR]
[TD]English[/TD]
[TD]English Lit[/TD]
[TD]Maths[/TD]
[TD]Science[/TD]
[TD]Biology[/TD]
[TD]Physics[/TD]
[TD]Chemistry[/TD]
[TD]Geography[/TD]
[TD]History[/TD]
[TD]French[/TD]
[TD]Spanish[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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 ""?