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
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.