If Nested Formula

[MatthewIJClark]

I'm trying to do an IF nested formula based on pass marks but the formula I'm using only returns "Fail" no matter the pass mark, what am I doing wrong:

=IF(B18>=50,"Pass",IF(B18>=60,"Merit",IF(B18>=70,"Distinction","Fail")))

Any help will be greatly appreciated.

Thanks In Advance

[Sam Capricci]

You need to reorder your formula to this...
=IF(B18>=70,"Distinction",IF(B18>=60,"Merit",IF(B18>=50,"Pass","Fail")))
the problem with the first order is that any value above 50 will solve the first part of the equation so a 70 is also greater than or equal to 50 so it would return pass and not distinction.
Now if this doesn't work you may have values that appear as numbers but are in fact text.
test that using =ISNUMBER(B18) to see, if it returns true then it is a number, false is text.

if it is text, use this instead... =IF(B18*1>=70,"Distinction",IF(B18*1>=60,"Merit",IF(B18*1>=50,"Pass","Fail")))

[BMV]

Please Login or Register  to view this content.

[MatthewIJClark]

Thank you for this, tried what you suggested but still returned fail only. Used the =ISNUMBER(B18) formula and returns a true so it's definitely and number, B18 is set up as a percentage to 2 decimal places not sure if that's causing an issue. Was hoping this would work and cannot understand why, the only other solution is possibly using a vlookup to a table of results with the pass mark and the classification but was hoping not to do that.

[MatthewIJClark]

Many thanks for this formula also but this didn't work either returned fail only.

[Sam Capricci]

can you post a sample using the instructions at the top of the post in the yellow banner?

[MatthewIJClark]

Hi Sambo Kid, I've attached a spreadsheet copying and pasting the cells from my original spreadsheet. Thanks for your help on this.

[MatthewIJClark]

Attachment.

[Sam Capricci]

here is the adjustment to make...
=IF(B1>=70%,"Distinction",IF(B1>=60%,"Merit",IF(B1>=50%,"Pass","Fail")))
the problem you had was that percents are less than 1 but your formula was all numbers greater than 1 (88% is equal to 0.88).
also you still need to reverse the order.

[BMV]

then
=LOOKUP(B1,{-9E9,50,60,70}%,{"Fail","Pass","Merit","Distinction"})

[MatthewIJClark]

Thank you so much, that's worked and is perfect.

[Sam Capricci]

You're welcome. Please don't forget to mark the post as solved using the thread tools dropdown at the top.
and you can thank any and all who stopped by to work on it by clicking on the * Add Reputation below one of their posts, it is how we advance on this forum.