Easy way to update goal by month

[Dee8]

Hi, I think this should be easy but I am having trouble sorry. Can anyone help with the easiest way to update a monthly goal based on an actual number. So for instance if the total goal for the year is 140% so that would be 11.67% per month for the entire year. If I have another column with the actual % so lets say in January it's 15% how to I get February goal to change from 11.67% to the new number based on the actual result and have that populate each month of the year so lets say January 15%, February 5%, March 10%, April 20%, annual goal is still 140% but the monthly goal needs to adjust based on the actual result. If someone can assist I would appreciate it! thank you!

 

[awoohaw]

How about this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
2		2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28
3	Projected	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	140%
4	Actual/New	15.00%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	140%
5	Actual/New	15.00%	5.00%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	140%
6	Actual/New	15.00%	5.00%	10.00%	13.75%	13.75%	13.75%	13.75%	13.75%	13.75%	13.75%	140%
7	Actual/New	15.00%	5.00%	10.00%	20.00%	12.86%	12.86%	12.86%	12.86%	12.86%	12.86%	140%
8
Sheet1

Cell Formulas
Range	Formula
F2,H2,J2,D2,B2	F2	=DATE(2023,1,31)
G2,I2,K2,E2,C2	G2	=EDATE(F2,1)
B3:K3	F3	=($L$3-SUM(($A$3:E3)))/(12-IF(COLUMN(F3)=1,0,COLUMN(F3)-1))
F4:K7,E4:E6,D4:D5,C4	F4	=($L3-SUM(($A4:E4)))/(12-IF(COLUMN(F4)=1,0,COLUMN(F4)-1))

 

[Dee8]

How about this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
2		2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28	2023-01-31	2023-02-28
3	Projected	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	12.73%	140%
4	Actual/New	15.00%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	12.50%	140%
5	Actual/New	15.00%	5.00%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	13.33%	140%
6	Actual/New	15.00%	5.00%	10.00%	13.75%	13.75%	13.75%	13.75%	13.75%	13.75%	13.75%	140%
7	Actual/New	15.00%	5.00%	10.00%	20.00%	12.86%	12.86%	12.86%	12.86%	12.86%	12.86%	140%
8
Sheet1

Cell Formulas
Range	Formula
F2,H2,J2,D2,B2	F2	=DATE(2023,1,31)
G2,I2,K2,E2,C2	G2	=EDATE(F2,1)
B3:K3	F3	=($L$3-SUM(($A$3:E3)))/(12-IF(COLUMN(F3)=1,0,COLUMN(F3)-1))
F4:K7,E4:E6,D4:D5,C4	F4	=($L3-SUM(($A4:E4)))/(12-IF(COLUMN(F4)=1,0,COLUMN(F4)-1))

that looks like it might work!! I will try and it and let you know. thank you so much!!

 

[awoohaw]

that looks like it might work!! I will try and it and let you know. thank you so much!!

I'm pretty sure it should. The only thing that is goofy with it is that you are asked to overwrite the formulas in the month where new actual data is.
If you wanted to protect the formula, you will need to do some more work.