Home

Help with IFs formula needed

%3CLINGO-SUB%20id%3D%22lingo-sub-739182%22%20slang%3D%22en-US%22%3EHelp%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739182%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20lots%20of%20different%20formulas%20from%20websites%20and%20adapted%20them%20but%20none%20work.%20%26nbsp%3BAll%20i%20want%20to%20do%20is%20using%20a%20drop%20down%20select%20a%20grade%20for%20a%20specific%20unit%2Fmodule%20eg%20Pass%2C%20Merit%20or%20Distinction.%20%26nbsp%3BOnce%20this%20is%20selected%20then%20i%20want%20to%20calculate%20the%20points%20the%20grade%20is%20worth%20eg%20some%20units%2Fmodule%20are%20worth%2010%20others%2015%2C%20and%20each%20grade%20is%20is%20also%20worth%20point%20pass%3D7%2C%20merit%3D8%2C%20distinction%3D9.%20%26nbsp%3BThe%20drop%20down%20option%20is%20in%20F11%2C%20the%20points%20a%20unit%20is%20worth%20is%20in%20E11%2C%20I%20am%20trying%20to%20get%20my%20answer%20in%20G11.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20formula%20i%20used%20is%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(F11%3D%E2%80%9CPass%E2%80%9D%2C%20E11*7%2C%20F11%3D%E2%80%9CMerit%E2%80%9D%2C%20E11*8%2C%20F11%3D%E2%80%9CDistinction%2C%20E11*9)%3C%2FP%3E%3CP%3EError%20%23NAME%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20used%20multiple%20IF.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%3F%20%26nbsp%3BIt%20seems%20pretty%20straightforward.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-739182%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739345%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739345%22%20slang%3D%22en-US%22%3EI%20have%20done%20some%20more%20digging%20and%20when%20i%20replace%20the%20words%20with%20numbers%20and%20not%20using%20a%20drop%20down%20it%20works%2C%20does%20anyone%20have%20any%20suggestions%20on%20how%20I%20can%20get%20my%20version%20to%20work%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739444%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371570%22%20target%3D%22_blank%22%3E%40PSPAUL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Paul%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20try%20this%20formula%2C%20I%20think%20it%20works%3C%2FP%3E%3CP%3E%3DIF(F11%3D%22Merit%22%2C8%2BE11%2CIF(F11%3D%22Pass%22%2C7%2BE11%2CIF(F11%3D%22Distinction%22%2C9%2BE11)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739959%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739959%22%20slang%3D%22en-US%22%3EYou%20CHOOSE%20this%20formula%20instead%3A%3CBR%20%2F%3E%3DCHOOSE(MATCH(F11%2C%3CBR%20%2F%3E%7B%E2%80%9CDistinction%E2%80%9D%2C%E2%80%9DMerit%E2%80%9D%2C%E2%80%9DPass%E2%80%9D%7D)%2C%3CBR%20%2F%3E9%2C8%2C7)*E11%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740405%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371570%22%20target%3D%22_blank%22%3E%40PSPAUL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%2C%20back%20to%20original%20formula%20-%20if%20you%20copy%2Fpaste%20some%20sample%20from%20the%20web%20and%20after%20that%20adjusted%20it%20to%20your%20need%2C%20with%20pasting%20most%20probably%20apostrophe%26nbsp%3B%3CSPAN%3E%E2%80%9C%20CHAR(147)%20instead%20of%20%22%20CHAR(34).%20Excel%20doesn't%20recognize%20first%20one%20as%20apostrophe%20and%20returns%20%23NAME!%20error.%20If%20correct%20that%20like%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DE11*IFS(F11%3D%22Pass%22%2C%207%2C%20F11%3D%22Merit%22%2C%208%2C%20F11%3D%22Distinction%22%2C%209%2C%20TRUE%2C0)%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%3EAs%20usual%20in%20Excel%20you%20may%20use%20different%20approaches%20to%20receive%20the%20same%20result.%20Variants%20are%20in%20previous%20posts%2C%20one%20more%20for%20the%20collection%20could%20be%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DE11*IFERROR(LOOKUP(F11%2C%7B%22Distinction%22%2C%22Merit%22%2C%22Pass%22%7D%2C%7B9%2C8%2C7%7D)%2C0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-745186%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-745186%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Thank%20you%20all%20for%20your%20help%2C%20the%26nbsp%3B%3CSPAN%3Eapostrophe%20was%20the%20issue.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20again!!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-745209%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFs%20formula%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-745209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371570%22%20target%3D%22_blank%22%3E%40PSPAUL%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20In%20general%2C%20be%20careful%20with%20copy%2Fpasting%20from%20Web.%20Most%20common%20issues%20are%20wrong%20apostrophes%20and%20not%20printable%20characters%20added.%3C%2FP%3E%3C%2FLINGO-BODY%3E
PSPAUL
New 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies