Return a Fail, Pass, Merit or Distinction

**CapeDutch** · 09-13-2010, 12:24 PM

Hi
I have a collation spreadsheet that collects grade from other sheets. I am trying to work out the grade for a specific unit of work to give me either "F", "P", "M" or "D" for fail, pass, merit and distinction respectively.
I've assign a number to each grade level:
"F"=0
"P"=1
"M"=2
"D"=3

Column S adds the assigned numbers to give me a total. I than used an IF statement to work out the grades eg.
=IF(S4<6,"F",IF(S4=6,"P",IF(S4<12,"P",IF(S4=12,"M",IF(S4<18,"M",IF(S4=18,"D","F"))))))

This works fine if the students have a "P","M" or "D" grade in each of the previous columns. However, in row 22 it does not work because enough points have been scored for the formula to return "P". This is an incorrect grade because not all the work has been completed. I want it to return "F" in column T if any of the columns have an "F" in it.

How can I make this work please?

**DonkeyOte** · 09-13-2010, 12:29 PM

Files are always more useful than images (people won't spend time recreating an image)

Please Login or Register  to view this content.

**CapeDutch** · 09-14-2010, 04:32 AM

Thank you DonkeyOte for taking the time to come up with a solution to my problem. You are right about the image. I now realise my mistake in attaching an image instead of a spreadsheet. I've replaced the image with a spreadsheet above. I've used your solutions but it doesn't give me the result i'm looking for. This is my fault because I forgot to add the follow info. The reason I assigned numbers to each grade is because a certain total (S column) will determine the overall grade:
<6="F"
6 to 11 ="P"
12 to 17="M"
18="D"
I saved the spreadsheet with your solution for the top class and my solution for the bottom class.
I left the columns in that you told me to erase, just in case you might need it for a different solution.

T4 should return "M"

**DonkeyOte** · 09-14-2010, 05:00 AM

The formula provided work without issue, however, you must ensure you are using the correct ranges

In your file both S4 & T4 are referring to row 5 whereas they should of course be referring to row 4 - hence the 5/"F" result rather than 12/"M"

**CapeDutch** · 09-14-2010, 06:17 AM

Sorry for my stupidity in applying the formula to the wrong row.
It works perfectly. You are brilliant!!!! Thank you so much DonkeyOte!

**MaverickSemperFi** · 01-27-2019, 04:07 PM

Hi,

This thread has been great and helped loads.

I have a problem that I’m hoping soemone could help me with. Attached is a copy of my spreadsheet that is for s similar purpose howevre, i have come across 2 problems

- My first issue is that due to the amount of Ps,Ms and Ds being changeable dependent on the criteria of the units, i need the number boundaries in column N11-26 to be dependant on results in cells P1-4.

- My second issue is that (as seen in spreadsheet) if one P is missing it should return Fail, if all Ps are present by one M is missing then it should return P, if all of the criteria is there but one P is missing then it should return F.

I’m wanting this spreadsheet to be able to fit with any unit, and as units have different amount of pass, merit and distinction criteria’s if you could solve this, it would be extremely helpful.

Kind Regards,
Maverick

**AliGW** · 01-27-2019, 05:27 PM

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.