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