Calculate Pretax and Tax Amounts from a Tax-Inclusive Total in WPS Office Spreadsheets
Question details
The user needs to split a tax-inclusive total into pretax and tax amounts using a tax rate stored in a separate cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has tax-inclusive totals in column D and needs the pretax amount in column B and the tax amount in column C.
- Observed behavior
- The required result is a pretax amount in column B and a tax amount in column C, derived from the tax-inclusive total in column D and the separate tax rate.
Enter the tax rate in a single cell such as K1, and make sure it is formatted consistently as a percentage or decimal so the formulas calculate correctly.
Use percentage formulas to split a tax-inclusive total into pretax and tax amounts
Use this method when you have a tax-inclusive total in column D and a tax rate in a single cell such as K1.
These formulas treat the tax-inclusive total as 100% plus the tax rate. Multiplying by 100%/(100%+tax rate) gives the pretax amount, and multiplying by tax rate/(100%+tax rate) gives the tax amount.
Type the tax rate in cell K1 as a percentage, for example 10%, or as the decimal 0.10.
Select cell B2 and enter =100%/(100%+$K$1)*D2, then press Enter.
Select cell C2 and enter =$K$1/(100%+$K$1)*D2, then press Enter.
Select B2:C2, then drag the fill handle down through the remaining rows to apply the formulas to all totals.
Calculate Pretax and Tax Amounts in WPS Office Spreadsheets
Use WPS Office Spreadsheets to split tax-inclusive totals into pretax and tax amounts with simple, Excel-compatible formulas. Enter your tax rate once and fill the formulas down for every row.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the tax-inclusive totals.
- 2. Locate or create the tax rate cell: Click cell K1 and enter the tax rate as a percentage or decimal.
- 3. Apply the pretax formula: In cell B2, type =100%/(100%+$K$1)*D2 and press Enter.
- 4. Apply the tax formula: In cell C2, type =$K$1/(100%+$K$1)*D2 and press Enter.
- 5. Fill down and format: Select B2:C2, drag the fill handle to fill the formulas down, then format columns B and C as currency.
Frequently Asked Questions
Can I use the same formulas if the tax rate is entered as a decimal instead of a percentage?
Yes. Enter the tax rate in K1 as a decimal such as 0.10 for 10%, and use the same formulas. The results will be the same.
Why should I use $K$1 in the formula?
The $ symbols make the reference to cell K1 absolute, so when you fill the formulas down, every row still uses the same tax rate cell.
How do I show the calculated amounts as currency?
Select columns B and C, right-click, choose Format Cells, select Currency or Number, and set the decimal places you want.
What if the tax-inclusive total is in a different column?
Replace D2 in the formulas with the cell that contains your tax-inclusive total, and keep the tax rate reference pointing to your tax rate cell.
Can I calculate tax for multiple tax rates in the same sheet?
Yes. Store each tax rate in a separate cell and change the $K$1 reference in the formula to the appropriate tax rate cell for each row or group.
