Nested If functions and VLOOKup - PLease help

Hey there guys so I had a few inquiries in which I would appreciate some clarification. So if we have a question that says =IF(score<80,"b",If(score<70,"c",If(score<60,"d",If(score<50,"f","a")))), what is the grade you would receive for a mark of 45, logically it would be assumed that it would receive an F, but the correct answer is B. Anyone know why that is?

There is also another question with LOOKUP function, I have posted this in the attachment. Therefore, my question is why is that 5 is N/A and it is also not an f as it is less then 10, however Understand why it is N\A but wanted more clarification in regards to why some question turns out like these, and what are the rules of reading such statement and how you would understand the boundaries because some can not be read logically.

Thank you in advnce.

There is no attachment, so I can't answer your second question.

Regarding the first question, though, your first IF is checking to see if score is less than 80 and return a "b" if it is. If the score is actually 45 then this IS less than 80, so a b will be returned - Excel will never check the other conditions.

To get the correct answer, you should reverse the order in which you do the comparisons, like this:

=IF(score<50,"f",If(score<60,"d",If(score<70,"c",If(score<80,"b","a"))))

Hope this helps.

Pete

If you want to attach a sample Excel workbook, here is the procedure.

Click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work on this Forum.

Hope this helps.

Pete

Hey pete,

thank you, However if this is the case then why doesn't the same apply to the other condition, lets say if it asks what is a score of 60, it would naturally be a "C" but following that mentality it mean that it is also B?

No. The formula that I gave you checks first of all if the value of score is less than 50 - if it is then the formula would return "f" and would not bother to check any of the other conditions, i.e. it exits at that point. If it is not less than 50 then the next criterion is examined, i.e. is score less than 60? If it is then "d" will be returned (with no further checking), and if it isn't then the next criterion will be applied, i.e. is it less than 70? If yes, then return "c" (and stop), otherwise check the next criterion - is it less than 80? If it is then return "b" otherwise return "a".

You can see that to get a "c" returned, for example, the score has to be 60 or over AND less than 70.

Hope this helps.

Pete

here is my second question in regards to the .lookup function

Compare this with the formula that Ali gave you in Post #5 - can you see why her formula would not produce #N/A for a score of 5?

Pete

Hey pete, so for the If question, if it were to ask what is a score of 65, that means it will also receive a B is that correct?b because it will look towards the first statement to see if it satisfies the condition, in which 65 is lower then 80, therefore it would be given a B, Instead of a C. Furthermore, for the second question Why is that 5 would receive a N/A and not a F, I just want some clarification in regards in how to read the statements more readily as sometime it can be against common logic. such that if 5 is N/A and anything between 10-14 is F, and 15-19 is C and so forth, then 30 is an A, even though 30 has not been identified, so what is a mark of 40? is that also A (even though logically I get it, it would be an A). I just want to better understand how the boundaries are read by the computer or how the functions are read. However, I Fully understand the IF function. Also another question is why someone would choose V-loockup instead of Loockup, as I know V-look up is or vertical, and also what about when people choose Match and Index. Sorry for the long message.