# Excel Formula and a bit of math

#### jeffdlinn

##### New Member
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.
ABCDE
1 Sample 1 Sample 2
2Gross 19,800,000 -
3
4Expense 1 - 48,842 3.76%
5Expense 2 1,200,000 760,000
6Expense 3 160,000 120,000
7Expense 4 45,000 -
8Total Expense 1,405,000 7.10% - 71.03%
9
10Net 18,395,000 92.90% - 28.97%
11
12
13
14Gross 19,800,000 19,779,570
15Expense 1,405,000 7.10% 1,384,570 7.00%
16Net 18,395,000 18,395,000

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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

</tbody>

<tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

Replies
3
Views
103
Replies
0
Views
274
Replies
8
Views
339
Replies
1
Views
252
Replies
3
Views
204

1,203,224
Messages
6,054,241
Members
444,711
Latest member
Stupid Idiot

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back