Forum Discussion
IFS formula returns #NAME? error
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
Sure thing!
Here's the link
https://1drv.ms/x/s!Aiqn_JB4mXBdkqJeu6kJi_tQDRz7Hw?e=iuPauZ
The problem seems to be with the amounts that have a discount. The first sheet has the sample of the document I am currently working on, and the second sheet has the calculations that I made for the invoices I sent out. On the first, I highlighted the entries with a problem, but left the ones with no discount for reference.
I can't thank you enough for your help!
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!