Excel Formula and a bit of math

[jeffdlinn]

Need help - Been at this for an hour now and I'm getting a circular reference error but not certain how to solve for the problem.

I'm basically trying to accomplish in rows 2-10 the same calculation in rows 14-16. So, in Column B I have a net amount and based on my expenses in Column D I have a formula (cell D14) which calculates what my Gross number needs to be to maintain the same net number.

In rows 2-10 I've introduced a new variable (row 4) which is a variable expense calculated as a percent of gross revenue. This is creating a circular reference error in cell D2 since cell D4 changes based on the gross number.

Any ideas how I fix the formula in cell D2.

FYI the net number in cell D10 needs to match the net number in B10.

	A	B	C	D	E
1		Sample 1		Sample 2
2	Gross	19,800,000		-
3
4	Expense 1	-		48,842	3.76%
5	Expense 2	1,200,000		760,000
6	Expense 3	160,000		120,000
7	Expense 4	45,000		-
8	Total Expense	1,405,000	7.10%	-	71.03%
9
10	Net	18,395,000	92.90%	-	28.97%
11
12
13
14	Gross	19,800,000		19,779,570
15	Expense	1,405,000	7.10%	1,384,570	7.00%
16	Net	18,395,000		18,395,000

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =B10/(1-E8) D4 =E4*D2 B8 =SUM(B4:B7) C8 =B8/B2 D8 =SUM(D4:D7) E8 =D8/D2 B10 =B2-B8 C10 =B10/B2 D10 =D2-D8 E10 =D10/D2 C15 =B15/B14 B16 =B14-B15 D14 =B16/(1-E15) D15 =D14*E15 D16 =D14-D15 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[baitmaster]

Why are you trying to use %s in this calculation? You Net amount is your Gross less your expenses. You're trying to fix your Net so make it equal the other Net, i.e. D16 = B16. Then reverse all your formulas so you are calculating your gross from your net, leaving a leftover value which is your adjustment figure. You can calculate your % from this