Field Calculation

[DS]

I need a formula to back the Tax out of an amount.
For instance if the field is $1.00 I need to find out the amount that is
Tax and the amount that is the Item. Does anyone know what the
formula is?
Thanks
DS

 

[Jeff Boyce]

Is this an Access question?

What is the tax rate?

If you are saying that
Your Value = Amount + (.## x Amount), or
Your Value = ( Amount (1 + .##))
the math says that
Amount = (Your Value) / (Amount (1 + .##))

Or have I misunderstood?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[DS]

Is this an Access question?

What is the tax rate?

If you are saying that
Your Value = Amount + (.## x Amount), or
Your Value = ( Amount (1 + .##))
the math says that
Amount = (Your Value) / (Amount (1 + .##))

Or have I misunderstood?

Thanks Jeff,
Well it's an Access question in as far as I am using Access
The Tax rate varies from state to state, so it can be anywhere from 3%
to 8.25%

I'm a little confused as to what .## is.

So you are saying that if my amount is 1.06 and the tax rate is .06 I
divide 1.06 by 1.06 which should give me a dollar?

Once again thank you jeff.
DS

 

[Jeff Boyce]

I think this is more a "math" question ...

You've confirmed that the tax rate varies.

"Your Value" is the $1.06 in your second example.

"Amount" is the value you are trying to calculate.

".##" is the decimal equivalent of the tax rate you are applying (in
your second example, 6%)

Yes, (1.06) / (1 + .06) = 1.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[DS]

I think this is more a "math" question ...

You've confirmed that the tax rate varies.

"Your Value" is the $1.06 in your second example.

"Amount" is the value you are trying to calculate.

".##" is the decimal equivalent of the tax rate you are applying (in
your second example, 6%)

Yes, (1.06) / (1 + .06) = 1.

Jeff, I think Amount / 1 + TaxRate = PreTax Amount
is it. Thnak you for your help.
DS

 

[Guest]

Hi Jeff,

PMFJI

It looks like the cut and paste gremlin nailed you again...

Amount = (Your Value) / (Amount (1 + .##))

Shouldn't it be

Amount = (Your Value) / (1 + .##)


where

(Your Value) = Total
.## = tax rate
Amount = item cost


DS,

this is a good example of why you should (almost) never store a value that
can be calculated; you should be saving the item cost and the tax rate. The
amount of the tax and the total cost can be calculated at any time.

 

[Jeff Boyce]

Thanks for the catch, Steve. I hope DS wasn't using my previous formula for
anything critical, like engineering a bridge... <g>

Jeff