This question is related to my other thread:
Formula to divide data from one cell equally into multiple cells
This is what I am trying to achieve:
Would it be possible to have the values in the calendar automatically change if I were to manually change one of the equally divided numbers.
Ex.
- Total hours 400
- Start week 1630
- end week 1630
Divided into 36 each I change 1 of the 36s into 100 and the other 36s automatically change to 30 so that the total stays 400.
This is how my excel sheet looks like: http://imgur.com/9RUNTGV
The 3 columns to the left are
AQ4 =COUNTIF(L4:AP4;$I4/($K4-$J4+1)) ## This is the number of cells with the divided total
AR4 =SUM(L4:AP4) ## this is the total value that I use to compare with the total hours it should take before I changed values manually
AS4 =COUNTIF(L4:AP4;"<>"&$I4/($K4-$J4+1))-COUNTIF(L4:AP4;"") ## This is the number of cells that are not $I4/($K4-$J4+1) or blank.
This is what i've come up with this far but it is not working:
=IF(AND(AC$2>=$J4;AC$2<=$K4);$I4/($K4-$J4+1);AND($AS4>0;(2*$I4-$AR$4)/($AQ4-$AS4);($K4-$J4+1);$I4/($K4-$J4+1)))
From this answer in your previous question, we have this formula in L7, which can be copied to other cells:
=IF( AND(L$2>=$J7; L$2<=$K7); $I7/($K7-$J7+1); "")
Now the challenge is to make that formula aware of the other values on the same row, as they might have been changed manually. This leads to a problem of circular reference: cell L7 must take into account changes made to cell M7, but also M7 should take into account changes made to cell L7. If there are (initially) no manually encoded values in either of these two cells, we have a circular reference. So this cannot be done.
However, what can be done, is to make the formula in each cell only look to its left side and make the calculation based on that. This way there will not be circular references.
This leads to the following formula for L7:
=IF(L$2=$J7;
$I7/($K7-$J7+1);
IF(AND(L$2>$J7; L$2<=$K7);
($I7-SUM(OFFSET(L7;0;$J7-L$2;1;L$2-$J7)))/($K7-L$2+1);
""))
This means:
If this cell is the first one of the period (L$2=$J7) then do as before, and calculate the evenly distributed value ($I7/($K7-$J7+1)). If this cell is another cell within the period, then take the sum of the previous cells (SUM(OFFSET(L7;0;$J7-L$2;1;L$2-$J7))), subtract that from the total required sum ($I7-SUM(...)) and divide that by the number of cells that remain on the right, counting this cell as well (.../($K7-L$2+1)). In all other cases output the empty string.
Copy this formula around.
Once you overwrite a value with a fixed number, you obviously destroy the formula that was there. But see how it makes the cells on the right of it recalculate. The sum remains constant. Be aware that changes in cells outside of the period are not taken into account.
Finally, make sure to make manual changes from left to right. If you make a change to a cell at the left of another cell that was already changed, things will no longer add up nicely. In that case, you first have to restore the formula in the righter cells, then make the change in the left cell, and then make again the changes in left-to-right order.
try this:
=IF(AND(Y$2>=$J4,Y$2<=$K4),$I4/($K4-$J4+1),IF(AQ4<>AS4,(2*I4-AR4)/(AS4-AQ4),$I4/($K4-$J4+1)))
I don't know what you were trying to achieve but this function does not produce an error
your function had incorrect placing of arguments so it was like 2 separate if functions trying to be read in one cell, if you follow the guideline excel shows underneath the function while you're typing it this can be prevented in the future
""; IF(aq4<>as4; (2*I4-AR4)/(AS4-AQ4; $I4/($K4-$J4+1))))
The "" is the If Not, after that is a fourth argument...
