Excel IF statement Not returning the appropriate V

问题:

I'm trying to grade students by giving them A or B depending on their score. If someone is having absent instead of a score, I return a value of the cell.

However, it does not return the value of the cell. The reference records are in a separate sheet called raw. I think it may be because I'm trying to return a string data.

I am using Excel 2007. Here's the formula:

=IF(raw!E6>=75,"AA",IF(raw!E6>=70,"AB",IF(raw!E6>=60,"B",IF(raw!E6>=50,"C",IF(raw!E6>=40,"D",IF(raw!E6<40,"RT",raw!E6))))))

回答1:

Don't use Nested IFs if you can avoid it. Instead, use a banded VLOOKUP: it's many times more efficient, and a heck of a lot simpler to troubleshoot. Something like this:

=IF(ISNUMBER([@Score]),VLOOKUP([@Score],Table1,2,TRUE),"Absent")

Notes:

• The above uses Tables and the associated Table Notation. I always use Tables when I can, because they reduce spreadsheet administration and the Structured Table References have intrinsic meaning.
• The VLOOKUP must have TRUE as the forth argument, and the lookup table must be sorted in ascending order.
• The lowest score must be zero, so that anything below 40 gets a "Retake" grade.

回答2:

Depending on whether or not the students' scores are in whole percentage figures (i.e. 75, 63, etc), you could use the INT-function to force interpretation of the input field as an integer (not that it always round down a score, which I suppose seem to be ok with the nested if-structure you're using here. Your function would then be:

=IF(INT(raw!E6>=75),"AA",IF(INT(raw!E6)>=70,"AB",IF(INT(raw!E6)>=60,"B",IF(INT(raw!E6)>=50,"C",IF(INT(raw!E6)>=40,"D",IF(INT(raw!E6)<40,"RT",raw!E6))))))

回答3:

you just need to change you last IF condition (raw!E6<40,"RT) because excel will give RT to all the score which is below 40 so add a and condition like if raw!E6>0 , this should resolve your work

if(and(raw!E6<40mraw!E6>0),"RT,raw!E6)

Hope this helps