Jul 05 2019 04:31 AM
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
Jul 05 2019 05:56 AM
Jul 05 2019 07:06 AM
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)))
Jul 05 2019 08:39 AM
Jul 05 2019 02:00 PM
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)
Jul 09 2019 08:15 AM
Hello Thank you all for your help, the apostrophe was the issue.
Thanks again!!
Jul 09 2019 08:24 AM
@PSPAUL , you are welcome. In general, be careful with copy/pasting from Web. Most common issues are wrong apostrophes and not printable characters added.