Expert: Pawan Musale - 3/18/2009

Question
Dear Sir,
I’m sorry to email you but maybe you can help or know somebody who can for free.
I’m creating a simple spreadsheet for my wife’s business and stumbled into a problem which is probably very simple but I think my IQ is just is not up to it.
I need a formula that will calculate the value of a given sum of cells less the original sales tax (6.5%)
Basically let’s say =sum(B1:B3) is $319.5 what formula can be applied to get the original $300 price?
Another way to express myself:
I’m being charged $319.5 for a purchase including a 6.5% sales tax. What was the original cost of the item?
I hope you can help.

Sincerely,
Arturo Meneses  

Answer
Arturo,

Simply write this equation:

(319.5 * 100) / 106.5. This calculates back to 300.

You have sales tax of 6.5 %. Means If your purchase is $100, then you pay $106.5.

Write this like:

          Original Cost           Cost after tax
              100                      106.5

Now in your case, your cost after tax is $319.5, so write it below "Cost after tax" column in above table and write "x" in the "Original Cost" column like:

           Original Cost           Cost after tax
              100                      106.5
               x                       319.5


Now MOST IMPORTANT - Do CROSS MULTIPLICATION and match results like:

100 * 319.5 = x * 106.5

After taking x on one side, we get,

x = (100 * 319.5)/106.5

Simply copy the formula and give reference of your SUM cell in place of 319.5. I hope this will solve your problem.

All the best.

Regards,
Pawan