Excel formula for using if and different conditions
Hi ALL,
i need to add grades in a cell by comparing score and percentile.
A GRADE 790-820
B GRADE 730-780
C GRADE 660-720
D GRADE 600-650
F GRADE 370-590
If the percentile is greater than 40 AND the score is 660 , the student will get C grade but if score is 660 but percentile is less than 40 student need D grade. How can we use if loop?
No need for IF statements - we can use a lookup. However, how are you calculating the percentile? Are there other percentiles that affect your grade boundaries?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Percentiles are already calculated! no other percentile is affecting grade boundaries
OK, so where is the C boundary for someone with a percentile below 40, or are you saying that a low percentile can't get higher than a D?
So let's assume the latter of my assumptions, where a percentile of less than 40 can't get more than a D, try this (score in A1, percentile in B1):
=LOOKUP(IF(B1<40,MIN(A1,600),A1),{370,600,660,730,790},{"E","D","C","B","A"})
Last edited by AliGW; 07-20-2018 at 12:30 PM.
let me give you an example if a student scores 820 but his percentile score is less than 40 then he wont get grade A he will get grade B.
What if the score is 595 (between categories)?
What if the score is <370
What if the score is >820
Is it ONLY the 40 percentile which affects the grade?
is it <40 or <=40?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
OK, this then:
=LOOKUP(IF(B1<40,A1-130,A1),{370,600,660,730,790},{"E","D","C","B","A"})
or this, maybe:
=IF(B1<40,LOOKUP(A1,{370,600,660,730,790},{"U","E","D","C","B"}),LOOKUP(A1,{370,600,660,730,790},{"E","D","C","B","A"}))
Last edited by AliGW; 07-20-2018 at 12:44 PM.
Here's another alternative assuming that a percentile < 40 bumps them down a grade:
=SUBSTITUTE(CHAR(LOOKUP(C2,{370,600,660,730,790},{69,68,67,66,65})+IF(B2<40,1,0)),"E","F")
With percentile in B2 and score in C2.
Last edited by 63falcondude; 07-20-2018 at 12:50 PM.
Maybe??
=IF(B2<40,LOOKUP(C2,{370,600,660,730,790},{"E","E","D","C","B"}),LOOKUP(C2,{370,600,660,730,790},{"E","D","C","B","A"}))
Hi,
the other formula worked I.E (=IF(B1<40,LOOKUP(A1,{370,600,660,730,790},{"U","E","D","C","B"}),LOOKUP(A1,{370,600,660,730,790},{"E","D","C","B","A"}))),
but the school introduced a new condition now if the student has a percentile higher than 90 and then he will get a higher grade. for example a student score 780 and the percentile is more than 90 so he/she will get A grade instead of B. All the previous conditions are still existing
So introduce another IF statement at the start that deals with the high percentiles. You will then have two nested IFs.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks