Forum Discussion
IFS formula returns #NAME? error
Hi everyone. I'm new to this forum
I am Canadian, and I am working on an invoice log. I created a dropdown list in my spreadsheet (column D) with all Canadian provinces, and now I would like column G to show the sales tax rate depending on the province. For example, if column D says ON I would like column G to automatically show 13%.
Yesterday I spent all day looking for tutorials online, but so far, nothing has worked. The formula I'm using right now is:
=IFS(D2=”AB”,(0.0005*100),D2=”BC”,(0.0005*100),D2=”MB”,(0.0005*100),D2=”NB”,(0.0015*100),D2=”NL”,(0.0015*100),D2=”NT”,(0.0005*100),D2=”NS”,(0.0015*100),D2=”NU”,(0.0005*100),D2=”ON”,(0.0013*100),D2=”PEI”,(0.0015*100),D2=”QC”,(0.0005*100),D2=”SK”,(0.0005*100),D2=”YT”,(0.0005*100))
But I get a #NAME? error in the G column, and I don't know what I'm doing wrong. I am using Excel 365. Thanks in advance for all your help.
You have used so-called curly quotes ” around AB etc.
Excel expects straight quotes "
=IFS(D2="AB",0.0005*100,D2="BC",0.0005*100,D2="MB",0.0005*100,D2="NB",0.0015*100,D2="NL",0.0015*100,D2="NT",0.0005*100,D2="NS",0.0015*100,D2="NU",0.0005*100,D2="ON",0.0013*100,D2="PEI",0.0015*100,D2="QC",0.0005*100,D2="SK",0.0005*100,D2="YT",0.0005*100)
A shorter version:
=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100
20 Replies
- Hi
I think an easier approach would be to have a separate sales tax sheet with two columns one for state and the other for tax rate and just use xlookup.
Cheers#Paul Since this question seems to have come round again, one could also use SWITCH.
= SWITCH(province,"NB",15%,"NL",15%,"NS",15%,"PEI",15%,"ON",13%,5%) Including other derived quantities = LET( taxRate, SWITCH(province,"NB",15%,"NL",15%,"NS",15%,"PEI",15%,"ON",13%,5%), tax, taxRate*netAmt, grossAmt, netAmt+tax, HSTACK(taxRate, tax, grossAmt) )Note: The final 5% is the default value taken if no other match is found.
office 2021 How to fix ifs function NAME? error
=IFS(X4=9,K4*2,X4=8,K4*2,X4=7,K4*3,X4=6,K4*4,X4=5,K4*30,X4=4,K4*70,X4=3,K4*5000,X4=2,K4*20000,X4=1,K4*200000)Do you use the R1C1 reference system?
HansVogelaar no i don't now R1C1 reference
You have used so-called curly quotes ” around AB etc.
Excel expects straight quotes "
=IFS(D2="AB",0.0005*100,D2="BC",0.0005*100,D2="MB",0.0005*100,D2="NB",0.0015*100,D2="NL",0.0015*100,D2="NT",0.0005*100,D2="NS",0.0015*100,D2="NU",0.0005*100,D2="ON",0.0013*100,D2="PEI",0.0015*100,D2="QC",0.0005*100,D2="SK",0.0005*100,D2="YT",0.0005*100)
A shorter version:
=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100
- I'm sorry to be a pest, but I have one more quick question. Now I would like my sheet to calculate the actual tax amount, depending on the province, in column H using the net total in column E. How can I do that?
=LET(province,UNIQUE(H2:H48),percentage, IFS(province="AB",0.0005*100,province="BC",0.0005*100,province="MB",0.0005*100,province="NB",0.0015*100,province="NL",0.0015*100,province="NT",0.0005*100,province="NS",0.0015*100,province="NU",0.0005*100,province="ON",0.0013*100,province="PEI",0.0015*100,province="QC",0.0005*100,province="SK",0.0005*100,province="YT",0.0005*100),netvalue,SUMIF(H2:H48,province,E2:E48),taxamount,netvalue*percentage,result,SORT(CHOOSE({1,2,3,4},province,netvalue,percentage,taxamount),1),result)Maybe you want apply above formula to return province and net amount along with the tax percentage and tax amount.
- Amazing! Thank you so much for the abbreviated version! It worked like a charm!
=IFS(D2="AB",(0.0005*100),D2="BC",(0.0005*100),D2="MB",(0.0005*100),D2="NB",(0.0015*100),D2="NL",(0.0015*100),D2="NT",(0.0005*100),D2="NS",(0.0015*100),D2="NU",(0.0005*100),D2="ON",(0.0013*100),D2="PEI",(0.0015*100),D2="QC",(0.0005*100),D2="SK",(0.0005*100),D2="YT",(0.0005*100))Does this work in your sheet? I replaced ” with " and now it works in my sheet.
- Yes, it did! Thank you so much!
