[RESOLVED] Excel cell formula problem

[DebbieInFlorida]

I am creating this long formula and it works fine until I add the last couple of conditions. I don't see anything wrong, any ideas??? It isn't past the 30 condition limit, but what do I know..

=IF(OR(F8="LOW",G8="LOW"),"LOW",IF(AND(F8="MEDIUM",G8="VERY
HIGH"),"MEDIUM",IF(AND(F8="MEDIUM",G8="HIGH"),"MEDIUM",IF(AND(F8="MEDIUM",G8="MEDIUM"),"MEDIUM",
IF(AND(F8="HIGH", G8="VERY HIGH"),"HIGH",IF(AND(F8="HIGH",G8="HIGH"),"HIGH",IF(AND(F8="HIGH",
G8="MEDIUM"),"MEDIUM",IF(AND(F8="VERY HIGH", G8="VERY HIGH"),"VERY HIGH",IF(AND(F8="VERY HIGH",
G8="HIGH"),"HIGH",IF(AND(F8="VERY HIGH", G8="MEDIUM"),"MEDIUM", "ELSE")))))))

From the underlined AND on it doesn't like this, but if I remove from that point on, it is ok. I need all these conditions!!!!!!!

[Fizziii]

It seems to me like you're taking the lowest value... so would the following work for you? It worked on my end.

=IF(OR(F8="LOW",G8="LOW"),"LOW",IF(OR(F8="MEDIUM",G8="MEDIUM"),"MEDIUM",IF(OR(F8="HIGH",G8="HIGH"),"HIGH","VERY HIGH")))

Basically:
IF either are "Low", then "Low"
ElseIF either are "Medium", then "Medium"
ElseIF either are "High", then "High"
Else "Very High".


Edit: I see you have an else condition in there... So I guess that'll throw off my thinking.

After further review, it looks like it's working okay... you have
If Either is Low, then Low
Else if M/VH, M/H, MM then Medium
Else if H/VH, H/H, HM then High
Else if VH/VH, VH/H, VH/M

With the first part being an OR, every condition is covered. If you change it to AND instead of OR, then I can get an "Else" result with VH/L. I think my formula would give the same results as yours is giving on my machine. (Running 2010 though)

[DebbieInFlorida]

I may have set up the formula wrong, but check out the attached matrix, it may make sense what I did. I used the OR's to save some IFs' for the LOW values....

thanks for your speedy input

[Fizziii]

Assuming your input is in cells F8 & G8, my formula should work. It basically returns the lowest value among the 2. Just like your formula started out, but then using "or" with Medium and High in both cells as well. Then if neither one has low, it will check for medium, if it doesn't find that it will look for high, and if it doesn't find that it'll default to Very High.

F8 & G8 don't have multiple values in them, do they?

Please see my attached file for how I set this up.

[opus]

The formula posted by Fizziii should work for most cases, the case in which it gives a wrong(?) result is if both cells are empty. The result of the formula is "Very High", I assume that would not be correct. However this case might never happen.

[DebbieInFlorida]

FIZZii

You are a genius, that is exactly what I needed, I was making it way too complicated.
Thanks so much!!

[Fizziii]

The formula posted by Fizziii should work for most cases, the case in which it gives a wrong(?) result is if both cells are empty. The result of the formula is "Very High", I assume that would not be correct. However this case might never happen.

=IF(OR(F8="LOW",G8="LOW"),"LOW",IF(OR(F8="MEDIUM",G8="MEDIUM"),"MEDIUM",IF(OR(F8="HIGH",G8="HIGH"),"HIGH",IF(AND(F8="VERY HIGH", G8="VERY HIGH,"VERY HIGH",ELSE))))

This will fix the issue brought up by opus. Thank you opus. (I just added one last if... if both are "VERY HIGH", then it's "VERY HIGH", otherwise it's "ELSE")