How to calculate pre-tax - I know tax rate and tax amount

Hi there please see sample file. Probably a simple answer....looking for the pre-tax amount (yellow cells) when I know the tax rate and total tax amount.

Homework but not school work . Thanks on mobile now will advise if that works hen I get back to my notebook.

Originally Posted by yaman

Hi there please see sample file. Probably a simple answer....looking for the pre-tax amount (yellow cells) when I know the tax rate and total tax amount.

As a practical matter, we want to round the calculation to the cent. See the formulas for "pre-tax amount" in column D below.

However, beware that the tax is usually rounded to the cent as well. Consequently, there is a range of pre-tax amounts that can result in the same rounded tax amount. See the formulas for "pre-tax amount" in columns C and E below.

	A	B	C	D	E
2	Problem 1
3	Tax Rate	5%
4	Tax amount	10.54	at least	avg	at most
5	Pre-Tax amount		210.70	210.80	210.89
6	Total	10.54	10.54	10.54	10.54
7
8	Problem 2
9	Tax Rate	13%
10	Tax amount	414.86	at least	avg	at most
11	Pre-Tax amount		3191.20	3191.23	3191.26
12	Total	414.86	414.86	414.86	414.86

Formulas:
C5: =ROUNDUP(($B4-0.005)/$B3,2)
D5: =ROUND($B4/$B3,2)
E5: =ROUNDDOWN(($B4+0.004999)/$B3,2)
C6: =ROUND(C5*$B3,2)

Copy C6 and paste into D6:E6.
Copy C5:E6 and paste into C11:E12.

Notes:

1. The choice of 0.004999 is arbitrary. Ordinarily, I would write 0.00499999999999999. But it does not work in E5. The point is: we want to add "a little less than" 0.005.
2. We might be tempted to write =ROUNDDOWN(($B4+0.005)/$B3-0.01,2) instead. But that does not work in E11.