# How to calculate a fee that is part of the total without a circular reference

Solve for the total instead of letting the cells refer to each other: =B2/(1-$F$1) is the amount to charge so that the fee comes out of it.

**Excel:** `=B2/(1-$F$1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Invoice | Amount due | Charge customer |  | Card fee | 0.029 |
| INV-1 | 1000 |  |  |  |  |
| INV-2 | 250 |  |  |  |  |
| INV-3 | 4800 |  |  |  |  |
| INV-4 | 75.5 |  |  |  |  |
| INV-5 | 12000 |  |  |  |  |

Formula in C2, filled to C6: `=B2/(1-$F$1)`

Result: $1,029.87; $257.47; $4,943.36; $77.75; $12,358.39

## How it works

The fee is 2.9% of the total charged, and the total includes the fee. Writing fee = total × 2.9% and total = amount + fee makes the two cells depend on each other: a circular reference. Solving once on paper removes the loop: total = amount ÷ (1 − 2.9%). For INV-1 that is 1,029.87, and 2.9% of it is the 29.87 fee, leaving exactly 1,000.

## Other ways

- `=B2+B2*$F$1/(1-$F$1)`: The same total written as amount plus fee, if you want the fee, `B2*$F$1/(1-$F$1)`, in its own column.
- `=B2*(1+$F$1)`: Adds 2.9% of the amount due, which falls short because the fee is taken from the larger total. (INV-1 charges 1,029.00; after the fee you receive 999.16, not 1,000.)

## Common mistakes

- Iterative calculation (File, Options, Formulas) can make the circular version settle on an answer, but it also hides real circular mistakes elsewhere in the workbook.
- Round to cents at the end: `=ROUND(B2/(1-$F$1),2)`.

## Questions

**How do I add a fixed fee per transaction too?**

Add it before dividing: `=(B2+0.3)/(1-$F$1)`.

**Is this the same as grossing up for tax?**

Yes. The gross amount before a 20% withholding is `=B2/(1-20%)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/fee-included-in-total-without-circular-reference