Trying to figure out if I need to do something different with this formula. I can get it to work on a individual level, but not with all the parameters I need it to. Help Please!!
H3 L3 M3 P3
Joe 235 680 220 75
Basically I need a formula like this with multiple outcomes using these numbers. I have this working but can't add in the other facets of it to get what I need.
=IF(H3>=160,"BLACK",IF(H3>=140,"GRAY",IF(H3>=120,"GOLD",IF(H3<120,"DQ"))))
I need it to be something like this.....
=IF(AND(H3>=160,L3>=450,M3>=160,P3>=47,”BLACK”,IF(H3>=140,L3>=400,M3>=140,P3>=42,”GRAY”,IF(H3>=120,L3>=350,M3>=120,P3>=39,”GOLD”,IF(H3<120,L3<350,M3<120,P3<39,”DQ”)))) with some how adding in that if there is a GRAY or a GOLD or a DQ that it defaults to the lowest Color Names assigned within the allowable ranges.
Thank you for any help
I've tried your formula and it won't work as-is. Here's a working version, although I'm not sure if it returns the result you want:
=IF(AND(H3>=160,L3>=450,M3>=160,P3>=47),"BLACK",IF(AND(H3>=140,L3>=400,M3>=140,P3>=42),"GRAY",IF(AND(H3>=120,L3>=350,M3>=120,P3>=39),"GOLD",IF(AND(H3<120,L3<350,M3<120,P3<39),"DQ","No result"))))
If you could provide some more information, I should be able to suggest a solution.
Regards
David
David,
Thank you for that formula. I have been racking my brain all dang day and had all of that in a different string, minus the "No Result". I knew it was something simple but I couldn't figure it out between the two different versions of the formula I was working. You asked for more info, so here it is.....I am a test state for a new initial assessment test for the Army. Basically, we just got released base line data and I wanted to create a formula to pull and score the test off manual input. This isn't just a test where for instance 70 push-ups equals so many points. This have four events with multiple attempts that need recorded for 2 events and the other two events have a outcome. With that said, all this equals to 4 different levels of criteria and that's what all of the >= refers to. I am an excel person and I understand and can evaluate things better through it's powers! Now I need to create one more piece to the puzzle to help self populate things so it can be interpreted faster. My question to you is (sorry if that was way too much info!) is why is it necessary to have the "no results" in the formula?
David,
Your formula answer works just as I need, but I do need to add one thing to it and I'm not getting it to formulate. I need to add at the end IF(AND(H3=0,L3=0,P3=0) or not data that it leaves the spot blank. I have tried numerous different additions but can't get it to work. I am sure that it is something easy like before.
Thank you,
I figured it out with the following:
=IF(AND(H4>=160,L4>=450,M4>=160,P4>=47),"BLACK",IF(AND(H4>=140,L4>=400,M4>=140,P4>=42),"GRAY",IF(AND(H4>=120,L4>=350,M4>=120,P4>=39),"GOLD",IF(AND(H4>=119,L4>=349,M4>=119,P4>=38),"Not Qualified",IF(AND(H4<1,L4<1,M4<1,P4<1),"","No Result")))))
Add new comment