Forum Discussion
Solved
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 ...
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.