(First of: if you can come up with a better title you'd be welcome)
I have the following scenario: There is a budget set for an entire year. Unless known, this should spread equally over all months. But you should be able to manually enter values for the months you already know how much you spent (or didn't). The problem is that when I am trying to do that it always comes up as a circular reference (without being able to fix it because excel can't find an exact reference).
The formula I tried to use to calculate the remainder divided by remaining months:
=($B3-SUMPRODUCT(--NOT(ISFORMULA($C3:$N3));$C3:$N3))/(12-SUMPRODUCT(--NOT(ISFORMULA($C3:$N3))))
B3 is the total budget for the year (leftmost), C3 to N3 the months (January to December; right to budget). The white cells on the right are not important for this question
61 Answer
I've now come up with several solutions myself (not without the help of you, thanks a lot), which all have some drawbacks and advantages.
#1
This solution doesn't require additional cells but has the drawback that you can only fill out the cells sequentially (from left to right), you'll get wrong solutions if not.
The formula is as follows =IF(ISFORMULA(D3);D3;($B3-IFERROR(SUM(OFFSET($C3;0;0;1;(MONTH(E$2)-1)));0))/(13-MONTH(E$2))) for cell E3, the references should be visible in the image.
#2
This solution requires a second helper row.ImageIn the top row the formula is (example cell: E7) =MAX(0;($B7-SUMIFS($C7:$N7;$C8:$N8;0))/(SUM($C8:$N8))),
while the bottom row just contains this: --ISFORMULA(C7), C7 being the reference to the cell on top (C6). This solution works perfectly fine but is annoying to work with because of the second row required.
#3
This solution mirrors #2 but doesn't require a second row. Instead the "helper row" is put on another sheet (like @gns100 suggested).
This sheet just tells you whether the same cell on the first sheet is a formula or via --ISFORMULA(Sheet1!C7) (for cell C7).
The corresponding formula in the first sheet would then be: =MAX(0;($B13-SUMIFS($C13:$N13;Sheet2!$C13:$N13;0))/(SUM(Sheet2!$C13:$N13)))
The drawback is of course needing another sheet.
