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
How about
=G2-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)*E2
or something like that?
HansVogelaar It's not working...it's strange. I have a separate sheet where I create my invoices, and the amounts don't match up.
I checked the formula that I use on those to calculate the totals automatically and they seem right. In my invoice workbook I have the subtotal, minus a discount (in some cases), plus tax (see example in image 1), but the result I get using the formula you so kindly helped me with is what is shown in the second image. As you can see, the tax in the sheet that I am working on right now is marked in red because it does not match the amount of the actual invoice, so I thought that it could be because in the invoice, I subtracted the discount before adding the tax, but it does not work with the formula you just sent me. Also, column P (with no title) is the amount of the invoice using the formula vs. the amount in column K, which is what I actually sent.
Could you attach a small sample workbook, or if that is not possible, could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.
Sure thing!
Here's the linkhttps://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.