My Math/Excel Ignorance is Showing
May 8, 2015 2:34 PM Subscribe
In three cells in a single column in Microsoft Excel, I'd like to be able to input a number in the bottom cell, have the middle cell calculate what a 6% sales tax on the figure below it is, and provide the amount before that 6% tax in the top cell.
Any help will be appreciated. The last math class I took was 44 years ago and I didn't pay much attention in that one.
Just to be clear, the bottom cell is a price including 6% tax? Is that correct? And you want to break it out to original price before tax (in the top cell) and the tax (in the middle cell)?
If so, assuming you're in cells A1, A2, and A3:
Put the value you have in A3
A1 (the top cell) should be: =A3/1.06
Why? Basically, to calculate tax the total with tax, you multiply your original value by 1.06. So in order to work backwards to get the original value from the total with tax, you need to divide your new value by 1.06
A2 (the middle cell) should be: =A3-A1
Why? Just subtract the value without tax from the value with tax. You could alternately do this as A1*0.06. The answers should be identical.
posted by brainmouse at 2:44 PM on May 8, 2015 [1 favorite]
If so, assuming you're in cells A1, A2, and A3:
Put the value you have in A3
A1 (the top cell) should be: =A3/1.06
Why? Basically, to calculate tax the total with tax, you multiply your original value by 1.06. So in order to work backwards to get the original value from the total with tax, you need to divide your new value by 1.06
A2 (the middle cell) should be: =A3-A1
Why? Just subtract the value without tax from the value with tax. You could alternately do this as A1*0.06. The answers should be identical.
posted by brainmouse at 2:44 PM on May 8, 2015 [1 favorite]
A1: =A3*1.06
A2: =A3*.06
A3: (your number)
Does the same thing mbrubeck's does, but this is the way my mind works.
posted by spikeleemajortomdickandharryconnickjrmints at 2:46 PM on May 8, 2015 [1 favorite]
A2: =A3*.06
A3: (your number)
Does the same thing mbrubeck's does, but this is the way my mind works.
posted by spikeleemajortomdickandharryconnickjrmints at 2:46 PM on May 8, 2015 [1 favorite]
Let's call your cells A1, A2, and A3.
Enter your sale price in A3.
Enter this in A1: =A3/1.06
Enter this in A2: =A3-A1
A1 gives you the pre-tax price, then A2 subtacts that from your "total" in A3. If your sales tax changes, just make the change in A1. e.g. 8%? =A3/1.08. 11%? =A3/1.11.
Took me a second to grasp what you're going for. Are you working backwards from a quoted price to figure out how much of that would have been tax?
posted by Kreiger at 2:48 PM on May 8, 2015 [1 favorite]
Enter your sale price in A3.
Enter this in A1: =A3/1.06
Enter this in A2: =A3-A1
A1 gives you the pre-tax price, then A2 subtacts that from your "total" in A3. If your sales tax changes, just make the change in A1. e.g. 8%? =A3/1.08. 11%? =A3/1.11.
Took me a second to grasp what you're going for. Are you working backwards from a quoted price to figure out how much of that would have been tax?
posted by Kreiger at 2:48 PM on May 8, 2015 [1 favorite]
Hey, please come back and clarify your question -- mbrubeck and I answered a different question than spikelee... & Kreiger, hence the different answers. An example would be great? Or just a different explanation.
posted by brainmouse at 2:51 PM on May 8, 2015
posted by brainmouse at 2:51 PM on May 8, 2015
Sorry brainmouse, I edited mine to reflect the question when I read it again. I know people who have to do stuff like this for quotes, like:
"Let's call it $450 for the materials."
"Shit, now I need to work the tax out..."
posted by Kreiger at 2:56 PM on May 8, 2015
"Let's call it $450 for the materials."
"Shit, now I need to work the tax out..."
posted by Kreiger at 2:56 PM on May 8, 2015
A2: =A3*.06
No spikeleemajortomdickandharryconnickjrmints , that is wrong. That's six percent of the price with tax, not 6 percent of the pre-tax price, which is what the tax is.
mbrubeck's solution is correct.
I'd rather put the tax rate in a field too and do it like this:
A1: Price before tax B1: =B3 * (1/(1+B4))
A2: Tax: B2: =B3-B1
A3: Price with Tax B3: (insert price here)
A4: TaxRate: B4: 0.06 (set to display as percentage and it will draw as 6%).
posted by w0mbat at 5:24 PM on May 8, 2015
No spikeleemajortomdickandharryconnickjrmints , that is wrong. That's six percent of the price with tax, not 6 percent of the pre-tax price, which is what the tax is.
mbrubeck's solution is correct.
I'd rather put the tax rate in a field too and do it like this:
A1: Price before tax B1: =B3 * (1/(1+B4))
A2: Tax: B2: =B3-B1
A3: Price with Tax B3: (insert price here)
A4: TaxRate: B4: 0.06 (set to display as percentage and it will draw as 6%).
posted by w0mbat at 5:24 PM on May 8, 2015
This thread is closed to new comments.
A1: =A3/1.06
A2: =A3-A1
A3: (insert total here)
posted by mbrubeck at 2:44 PM on May 8, 2015