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
Thank you! The problem is that we calculated the tax over the full invoice amount instead of over the discounted amount. The formula in H2 should be =G2*(C2-E2)
I also simplified the formulas in columns E and F slightly.
See the attached version.
Amazing! I really can't thank you enough for all your help! Have a wonderful day and stay safe!