Simple example of a circular reference formula: Is there a formula that can help me out?

**Katie620** · 07-24-2019, 01:49 PM

Hi,

Please see the attached file

Line's 2-4 are static, Line 1 is a formula that looks at them. Then there is another row that is what line 1 should always equal.

To make Line 1 equal this, I need Line 3 to be dynamic and 'fill in the gap'. But of course I can't make Line 3 look at Line 1 because Line 1 is already looking at Line 3.

How do I solve this problem besides manually going in every month and adjusting the static Line 3?

Thanks!

**MrShorty** · 07-24-2019, 02:04 PM

Algebra first (unless you have some moral or religious objection to using algebra -- in which case I recommend pawning this task onto someone who is not morally opposed to algebra).

C5=C8+B6-C6+B7-C7
goal: find the value for C7 that makes C5=C12 (assuming all other values are static).
Add C7 and subtract C5 from both sides so we can find the formula for C7.
C7=C8+B6-C6+B7-C5.
We want C5 to be equal to C12, so substitute C12 in for C5
C7=C8+B6-C6+B7-C12.

Enter that formula into C7 and copy across. Unless I am misunderstanding something, that should solve the problem.