If and or functions help!!!

[martyloo]

EXCEL 2010

I am trying to do the following IF in sheet "Main Console"

cell "E19" displays an 11 and "F19" Displays a 9
OR
cell "E19" displays a 9 and "F19" displays an "11"
AND
cell "G19" displays a 3

show the following - 20% otherwise display blank

Here are the formula's I have tried so far but have not worked.......

=IF('Main Console'!G19=3,AND(OR('Main Console'!E19=11,'Main Console'!F19=9,'Main Console'!E19=9,'Main Console'!F19=11),0.2,""))

=IF('Main Console'!G19=3,AND('Main Console'!E19=11,'Main Console'!F19=9,OR('Main Console'!F19=11,'Main Console'!E19=9),0.2,""))

=IF(OR('Main Console'!E19=11,'Main Console'!F19=9,'Main Console'!F19=11,'Main Console'!E19=9)*AND('Main Console'!G19=3),0.2,"")

The bottom one worked however regardelss of what is in cells E19 and F19 if G19 had a 3 in it, it would display all the % I had for other if functions involving that G19 cell with a 3

Please help as I am now clueless........

 

[JackDanIce]

Try:

=IF('Main Console'!G19<>3,"",IF(AND(OR('Main Console'!E19=11,'Main Console'!E19=9),OR('Main Console'!F19=9,'Main Console'!F19=11)),20%,""))

 

[amir_urmila]

Hi See if this is working...

=IF(AND(AND(OR(E19=11,E19=9),OR(F19=11,F19=9)),G19=3),20%,"")

 

[martyloo]

Your knowledge is amazing I genuinely thought this would be a problem not easy to solve, and it took seconds.

Thank you once again JackDanIce

 

[JackDanIce]

And amir's suggestion looks even more compact, bit annoyed I didn't spot that!

 

[JackDanIce]

Out of interest/curiosity, are the values of 9 and 11 the only ones E19 and F19 can take? If so then the formula could be reduced down to:

=IF(SUM('Main Console'!E19:G19)=23,20%,"")

E.g.
E19 = 9, F19 = 11, G19 = 3
SUM(E19:G19) = 23 - TRUE

E19 = 9, F19 = 9, G19 = 3
SUM(E19:G19) <> 23 - FALSE

E19 = 11, F19 = 11, G19 = 3
SUM(E19:G19) <> 23 - FALSE

E19 = 9, F19 = 11, G19 = 4
SUM(E19:G19) <> 23 - FALSE

 

[martyloo]

no no they can go from 2 up to 11

 

[JackDanIce]

This may work too then:

=IF(AND(AND('Main Console'!E19+F19=20,ABS('Main Console'!E19-F19)=2),G19=3),20%,"")

 

[jasonb75]

Unless I misread something, this matches your original criteria,

Excel Workbook
	E	F	G	H
1	9	9	3
2	11	9	3	0.2
3	9	11	3	0.2
4	11	11	3
Sheet

 

[jasonb75]

Slight revision to the last formula to check that "3" is in the correct column, previously E19 = 3, F19 = 11, G19 = 9 (or similar variation) would incorrectly return 20%

=IF(G1=3*AND(SMALL(E1:F1,{1,2})={9,11}),0.2,"")