Solved IF Statement for 6 tests

October 12, 2015 at 20:00:52
Specs: Windows 7
Can someone help me check this IF statement for this logic (is this the most efficient way to do it):

If C9 = “Dade” and I3<3326 then “HOMERUN”
If C9 = “Broward” and I3<4220 then “HOMERUN”
If C9 = “Palm Beach” and I3<4326 then “HOMERUN”
If C9 = “Dade” and I3<4158 then “Community or HOMERUN”
If C9 = “Broward” and I3<5275 then “My Community or HOMERUN”
If C9 = “Palm Beach” and I3<5408 then “My Community or HOMERUN”

Statement:
=IF(I3="","",IF(AND(C9="Dade",I3<3326),"HOMERUN",IF(AND(C9="Broward",I3<4220),"HOMERUN",IF(AND(C9="Palm Beach",I3<4326),"HOMERUN",IF(AND(C9="Dade",I3<4158),"Community OR HOMERUN",IF(AND(C9="Broward",I3<5275),"Community OR HOMERUN",IF(AND(C9="Palm Beach",I3<5408),"Community OR HOMERUN","")))))))

message edited by mecerrato


See More: IF Statement for 6 tests

Report •

#1
October 13, 2015 at 03:59:38
For one thing, the formula is never going to return "My Community or Homerun" because it is not included in the formula as a possible return value.

There may be suggestions to offer, but there no sense in doing that until we see a correct version of either your criteria or formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 13, 2015 at 05:42:20
Thanks DerbyDad03 I did not mean to have the word "My" as part of the returned values, here is the correct criteria and statement (I also made another correction to the criteria):

If C9 = “Dade” and I3<3326 then “HOMERUN or Community”
If C9 = “Broward” and I3<4220 then “HOMERUN or Community”
If C9 = “Palm Beach” and I3<4326 then “HOMERUN or Community”
If C9 = “Dade” and I3<4158 then “Community”
If C9 = “Broward” and I3<5275 then “Community”
If C9 = “Palm Beach” and I3<5408 then “Community”

Statement (The formula works but I want to know if there is a better way of doing it.):

=IF(I3="","",IF(AND(C9="Dade",I3<3326),"HOMERUN or Community",IF(AND(C9="Broward",I3<4220),"HOMERUN or Community",IF(AND(C9="Palm Beach",I3<4326),"HOMERUN or Community",IF(AND(C9="Dade",I3<4158),"Community",IF(AND(C9="Broward",I3<5275),"Community",IF(AND(C9="Palm Beach",I3<5408),"Community","")))))))


Report •

#3
October 13, 2015 at 08:40:40
✔ Best Answer
I'm pretty sure this formula does the same thing as your formula. All I did was combine the 3 AND's that return same result and put them inside an OR. If any of the 3 AND's are true, the corresponding OR will be true.

=IF(I3="","",
IF(OR(AND(C9="Dade",I3<3326),
AND(C9="Broward",I3<4220),
AND(C9="Palm Beach",I3<4326)),"HOMERUN or Community",
IF(OR(AND(C9="Dade",I3<4158),
AND(C9="Broward",I3<5275),
AND(C9="Palm Beach",I3<5408)),"Community","")))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question