Calculate Monthly Goal to reach target [closed]

Solution 1:

For the purpose of this answer, I have laid out the sheet with the goal in row 1, the Months are in row 2, actual in row 3 and target in row 4. It looks like this;

First you want to enter a formula so the goal will count down as you enter actual amounts. To do this, enter the formula in B2 that has the goal-total in row 3. This will subtract amounts received from the goal.

=120000-SUM(B3:M3)

Now enter a formula in row 4 to calculate your monthly target. Using an IF function, we can tell the formula to ignore the "actual" cell if it has a value in it. If it is blank, the calculation will run to divide the remaining goal amount in B1 by the number of blank months in B3:M3 to give you the monthly target. This formula can be copied to each cell in B4:M4.

=IF(ISBLANK(B3), SUM($B$1/COUNTBLANK($B$3:$M$3)), "")

It will result in the sheet working like this;

Closing note; if the sheet doesn't calculate automatically, you will need to press F9 or set the sheet to automatic.