Forum Discussion
Help with IFs formula needed
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
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.
- You CHOOSE this formula instead:
=CHOOSE(MATCH(F11,
{“Distinction”,”Merit”,”Pass”}),
9,8,7)*E11 - 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 thanksHi 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)))
