IF statement with multiple "layers"

dexnmav

New Member
Joined
Jan 29, 2014
Messages
7
Hi, this is my first post, so hopefully I'm asking everything correctly.

I'd like the result column to be either P, R, or SCO based on the wrong and errors columns.

I'd like to know:
IF wrong =< 15 AND errors=0, then result is P
AND
IF wrong =<10 AND errors=1, then result is SCO
AND
IF wrong >15 OR errors >1, then result is R

WrongErrorsResult (P/SCO/R)
60
121
41
52

<tbody>
</tbody>

I've tried several different formulas just to get the first part of the equation to work so I can have a base to complete the rest, but something isn't working right for me.

Thanks in advance for your help! I greatly appreciate it!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi

Ok lets say Column A is "wrong", column B is "errors".

Then the formula on C2 (first field of "result") should look like this:

=IF(AND(A2>=15, B2=0), "P",IF(AND(A2<=10, B2=1), "SCO", IF(OR(A2>15, B2>1), "R", "OTHER")))

you can drag this down.
If the columns are not the same as in your file, just fix it in the formula.

Hope this helps.

Chris
 
Upvote 0
My mistake, got one sign wrong =) its like this:
=IF(AND(A2<=15, B2=0), "P",IF(AND(A2<=10, B2=1), "SCO", IF(OR(A2>15, B2>1), "R", "OTHER")))
 
Upvote 0
It's almost perfect. I still have some that have (for example) 13 wrong and 1 error that are showing SCO as the result and it should be R. Ideas? :confused:
 
Upvote 0
It's almost perfect. I still have some that have (for example) 13 wrong and 1 error that are showing SCO as the result and it should be R. Ideas? :confused:

Based on your stated requirements, I don't see why it should be "R". R will occur only when wrong >15 OR errors >1
 
Upvote 0
Based on your stated requirements, I don't see why it should be "R". R will occur only when wrong >15 OR errors >1

You just made something click for me: =IF(AND(A2<=15, B2=0), "P",IF(AND(A2<=10, B2=1), "SCO", "R"))) Because the R really only needs to be everyone else that didn't receive a P or SCO.

Here's the criteria pulled from the original document:
—Students who receive wrong 15 or less and 0 errors P
—Students who receive wrong 10 or less and one error are required to SCO
—All other students not include in the above two categories must R

Does the formula I did above make sense then?
 
Upvote 0
Nevermind, I thought I had it.

The only students that can receive a P are ones that have 15 or less wrong and 0 errors.
The only students that can receive a SCO are ones that have 10 or less wrong and 1 error.
So if you have 12 wrong and 1 error, you must receive a R. If you have only 6 wrong and 2 errors, you must receive a R.
 
Upvote 0
Nevermind, I thought I had it.

The only students that can receive a P are ones that have 15 or less wrong and 0 errors.
The only students that can receive a SCO are ones that have 10 or less wrong and 1 error.
So if you have 12 wrong and 1 error, you must receive a R. If you have only 6 wrong and 2 errors, you must receive a R.

ok then the formula you wrote in the previous post should work.

Please mark the thread as solved if your think your issue was resolved
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top