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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211
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
 

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211
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")))
 

dexnmav

New Member
Joined
Jan 29, 2014
Messages
7
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:
 

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211

ADVERTISEMENT

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:

That's strange.
Can i get your worksheet file to have a look?
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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
 

dexnmav

New Member
Joined
Jan 29, 2014
Messages
7
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?
 

dexnmav

New Member
Joined
Jan 29, 2014
Messages
7
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.
 

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

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
Top