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

Q: How do I add a fixed fee per transaction too?

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

Q: Is this the same as grossing up for tax?

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

Formula (Excel and Google Sheets)

=B2/(1-$F$1)

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.

Result on the example: one result per row in C2, filled down to C6
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

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.

=B2/(1-$F$1)

Result1029.866117

B2$1,000.00
The value in B2.
$F$12.9%
The value in F1.
1-$F$10.971
The - operator subtracts.
B2/(1-$F$11029.866117
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =B2/(1-$F$1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=B2+B2*$F$1/(1-$F$1)	$1,029.87, $257.47, $4,943.36…	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)	$1,029.00, $257.25, $4,939.20…Different result	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).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate a fee that is part of the total without a circular reference

In C2, calculate how much to charge so that, after the card fee in F1 is taken from the total charged, you receive the amount due. Fill down to C6.

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%).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.