Multiple criteria if statement
I have microsoft excel 2010. I need help writing a formula/function for column AA. Here's what I want it to do in English:
If P3 = “True”, than V3 needs to be >= (greater than or equal to) 40% of H3. If it is, I want success to appear in column AA. If it’s not, I want fail to appear in column AA.
If Q3 is “True”, than V3 needs to be >= (greater than or equal to) 60% of H3. If it is, I want success to appear in column AA. If it’s not, I want fail to appear in column AA.
If R3 is “True”, than V3 needs to be >= (greater than or equal to) H3. If it is, I want success to appear in column AA. If it’s not, I want fail to appear in column AA.
If S3 is “True”, than V3 needs to be >= (greater than or equal to) H3. If it is, I want success to appear in column AA. If it’s not, I want fail to appear in column AA.
If there is a way to get this all in one function, I'd appreciate that very much. Here's an example:
P3 says True (which means Q,R,and S cannot be true and must be false). V3 is just the number 28 and H3 is just the number 10. Obviously, 40% of 10 is 6 and 28>6. So, in column AA, I would like it to say "Success".
Thank you very much. I appreciate it!
Last edited by marlin5124; 07-10-2011 at 07:57 PM. Reason: Better Title
I'll put something together for you shortly.
In the meantime, take a moment to read through the Forum Rules so you can use and follow them effectively. These rules are meant to benefit you and all who use the forum.
As per Rule #1, please EDIT your post above and update the title to reflect the topic more accurately. It's usually MUCH easier to name your post after you've written all that content...
Perhaps:
MULTIPLE CRITERIA IF STATEMENT
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Try this formula in AA3
=IF(OR(AND(P3=TRUE,V3>=H3*40%),AND(Q3=TRUE,V3>=H3*60%),AND(OR(R3=TRUE,S3=TRUE),V3>=H3)),"Success", "Fail")
I'm assuming P3:S3 contain "Boolean" values TRUE/FALSE not text "TRUE"/"FALSE" - if it's the latter include quotes around TRUEs in formula......
Audere est facere
My apologies, the moderators appear to approve your thread title.
My solution would be this formula in AA3:
=IF(V3 >= (H3 * LOOKUP(MATCH(TRUE, P3:S3, 0), {1,2,3}, {0.4,0.6,1})), "Success", "Fail")
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
No you were quite right, JerryOriginally Posted by JBeaucaire
I only posted a reply after marlin changed his thread title as you asked.....
Audere est facere
Thank you, this works. But I'd actually like to add a few things if that's not too much of a problem for you:
If it says "noplay" in column N3, can you make it say "Neither". Also, if it says "TD" in Y3 can you make it say "Success", regardless of the prior conditions. Also, if it says "fum lost" in column U3 can you make it say "Fail" regardless of the prior conditions.
Lastly, there's sometimes just a blank column in V.... Just no number in the V column. With the formula you posted, it automatically says fail for those. But if there's no number and it's just blank, I'd just rather it say "Neither" or just nothing at all.
If you know how to do this, please post the original formula with the added info and corrections all in one function.
Thank you very much. I really appreciate it!
Last edited by marlin5124; 07-10-2011 at 08:22 PM.
See, I'm blind, didn't even notice he had!
Last edited by JBeaucaire; 07-10-2011 at 08:23 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
My formula with all those additional requirements added at the start...ugh...
=IF(U3="turn lost", "Fail", IF(Y3="TD", "Success", IF(OR(N3="noplay", V3=0), "Neither", IF(V3 >= (H3 * LOOKUP(MATCH(TRUE, P3:S3, 0), {1,2,3}, {0.4,0.6,1})), "Success", "Fail"))))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
It's only if the V column is blank or the N column says "noplay" that I want it to say "Neither". It's saying "Neither" for other things, including when the V column says 0. I want 0 to count as a regular number instead of nothing or the same as a blank V column.
Thanks.
EDIT... my bad...ok, slight edit:
=IF(U3="turn lost", "Fail", IF(Y3="TD", "Success", IF(OR(N3="noplay", ISBLANK(V3)), "Neither", IF(V3 >= (H3 * LOOKUP(MATCH(TRUE, P3:S3, 0), {1,2,3}, {0.4,0.6,1})), "Success", "Fail"))))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
1 more change, I promise! How about this?
Instead of caring about if it says "noplay" in the N column, can we only make any/all of the conditions count if it says "pass" or "rush" in the N column. What I mean is, if it doesn't say "pass" or "rush" in the n column, I don't want success or fail to appear. If it has to say something, just make it say "Neither". But I only want it to say "Success" or "Fail" if it says "pass" or "rush" in the corresponding N Column.
I also still want the other conditions, which were if it says "fumlost" in the U column I want it to say "Fail" and if it says "TD" in the Y column I want it to say "Success".
Thank you. I hate to be a pain, sorry. But thank you very much, I really do appreciate it.
Yeah, I think we've demonstrated the technique for you multiple times, far enough for you to be able to dive in yourself now.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks