Estimate the sales tax

I have a problem. If I get a receipt from a purchase all it lists is the item price but I don't see the sales tax rate which adds the amount to get the grand total. Is there an easy way to estimate the sales tax rate and create a function to automatically estimate it's percentage if you have the grand price total?

The first row lists the column headers

A2 is the item price
B2 is the unknown sales tax rate
C2 is the grand total price of the item plus tax rate.

If I have the item price and the grand total price...can a function be made to estimate the sales tax? Or put another way...if I have the initial sales price but don't have the sales tax rate but can't find the grand total price unless I have the sales tax...is there a way to generate a function to automatically estimate what the total cost would be if a sales tax were included?

Thanks in advance.

Perhaps:

=C2/A2-1

where A2 and C2 contain you price and grand total

See the formula posted here by Pete_UK

Given the sample sheet, give an example of what you mean. I am not following.

Sorry I am still not following... can you be specific, name cells, steps with specific examples...etc to make the point clear.

So the formula in say C17 would be =E17/(1+D17) if 5.42 is entered in E17, the based on the sales tax in D17 being 8.38%, then C17 will give 5.00.

Originally Posted by NBVC

So the formula in say C17 would be =E17/(1+D17) if 5.42 is entered in E17, the based on the sales tax in D17 being 8.38%, then C17 will give 5.00.

No, the amount would need to be input into C17. Since the sales tax rate is given at 8.37% in D17 the formula is listed in E17. The problem is since I can't always be sure what the sales tax rate should be since I'm not always given the sales tax rate...what is given is usually the total sales amount which includes the sales tax rate. Since I don't know what rate each retail chain is using...the initial cost of the item doesn't tell me what the sales tax rate is.

Again with the example of 5.00 dollar item. The total cost could be 5.42 but I"m not shown the sales tax rate. So if the receipt shows the total cost...which is 5.42 along the initial cost of the item...which is 5.00 I don't know what the sales tax rate is. So in order to figure out the final cost of the item without me knowing the sales tax rate forces me to statically hard code the sales tax rate at the rate I input in cell D17 at 8.38% and I would be forced to plug in arbitrary numbers in C17 to account for the total cost which includes the sales tax rate where the formula is listed in E17.

We seem to be going around in circles with this.. I am hoping that someone following this thread understands and can interject.... I am not sure where to go from here....

I think the problem is DorothyFan1 is more concerned with determining the tax rate which is in col D but has a formula in col E that refers to col D.

DorothyFan1
If you are mostly concerned with checking the tax rate then you have to put your formula in col D and manually type your col C AND col E numbers.
So you would type in your Amount in for instance C17
Then type in your total in E17
This formula in D17 will give you your Tax Rate: =IF(E17="","",(E17-C17)/C17)

You can't have formulas in D17 AND E17 referring to each other because that would create a circular reference.

You could have this formula in E17: =IF(C17="","",ROUND(C17*1.08375,2))
And if the total doesn't match your sales slip then overwrite the formula with actual value.

Are you in New York where there is a variable tax rate which explains the inconsistency?

There are three numbers -- item cost, total cost, and tax rate. If you know any two, you can calculate the third, and you always know two of them, right? So you could do this:

Please Login or Register  to view this content.

Columns A:E are inputs. Columns F:H compute the missing item.

F2 and down: =IF(C2, C2, D2/(1+E2))

G2 and down: =IF(D2, D2, C2*(1+E2))

H2 and down: =IF(E4, E4, D4/C4-1)

See if this is something that you can use....

Enter the Cost of the Item,
Enter the Total Price paid,
Last column figures the Tax percentage paid for you....

Originally Posted by jwright650

See if this is something that you can use....

Enter the Cost of the Item,
Enter the Total Price paid,
Last column figures the Tax percentage paid for you....

I've tried it and it works, but it's only giving the percentage based on the difference between the initial cost and the grand total. The formula doesn't take into account the actual sales tax itself.

For example, the sales tax rate is normally 8.37% per unit. But sometimes where I go to purchase the items...the sales tax is higher or lower. If I don't know the sales tax rate the retail chain is using I end up having to guesstimate the initial cost to account for the given total if I'm not told the sales tax rate.

I need a function that recognizes if I don't have either the initial cost or the grand total, it estimates the sales tax based on the previous entry.

BTW...I'm using Excel 2010 and something interesting happened...when I opened up the Excel attachment into Excel..Protected Mode didn't activate and I have the Protected Mode feature enabled. I'm wondering if this is a bug. Because when opening files from the internet Protected Mode should automatically kick in and prevent me from editing the files. Instead I've been able to edit the files immediately. Using 32bit Excel 2010 on Win 7 64 bit system.

Originally Posted by Cutter

No more guessing the sales tax!

http://www.catskill.net/purple/nystaxes.htm

Great Chart Cutter! [edit** "and Marcol"**], thanks...I saved that one for future reference

DorothyFan1, can you say which solution that you used. This will help other users