Help with IFs formula needed

Copper Contributor

 

I have tried lots of different formulas from websites and adapted them but none work.  All i want to do is using a drop down select a grade for a specific unit/module eg Pass, Merit or Distinction.  Once this is selected then i want to calculate the points the grade is worth eg some units/module are worth 10 others 15, and each grade is is also worth point pass=7, merit=8, distinction=9.  The drop down option is in F11, the points a unit is worth is in E11, I am trying to get my answer in G11.

 

One formula i used is below;

 

=IFS(F11=“Pass”, E11*7, F11=“Merit”, E11*8, F11=“Distinction, E11*9)

Error #NAME?

 

I also used multiple IF.

 

Can someone help?  It seems pretty straightforward.

 

Thanks

 

Paul

6 Replies
I have done some more digging and when i replace the words with numbers and not using a drop down it works, does anyone have any suggestions on how I can get my version to work?

Many thanks

@PSPAUL 

Hi Paul

 

If you try this formula, I think it works

=IF(F11="Merit",8+E11,IF(F11="Pass",7+E11,IF(F11="Distinction",9+E11)))

You CHOOSE this formula instead:
=CHOOSE(MATCH(F11,
{“Distinction”,”Merit”,”Pass”}),
9,8,7)*E11

@PSPAUL 

Paul, back to original formula - if you copy/paste some sample from the web and after that adjusted it to your need, with pasting most probably apostrophe “ CHAR(147) instead of " CHAR(34). Excel doesn't recognize first one as apostrophe and returns #NAME! error. If correct that like

=E11*IFS(F11="Pass", 7, F11="Merit", 8, F11="Distinction", 9, TRUE,0)

As usual in Excel you may use different approaches to receive the same result. Variants are in previous posts, one more for the collection could be

=E11*IFERROR(LOOKUP(F11,{"Distinction","Merit","Pass"},{9,8,7}),0)

Hello Thank you all for your help, the apostrophe was the issue.

 

Thanks again!!

@PSPAUL , you are welcome. In general, be careful with copy/pasting from Web. Most common issues are wrong apostrophes and not printable characters added.