Calc cumulative by month

[mylamly26]

I have a annual target of $300,000 to achieve by next June. How do pro-rate the target cumulative by month.

July	August	September	October	November	December	January	February	March	April	May	June
											$300,000

 

[6StringJazzer]

This will work for any number of months based on what total you put in the last month.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	July	August	September	October	November	December	January	February	March	April	May	June
2	$25,000	$50,000	$75,000	$100,000	$125,000	$150,000	$175,000	$200,000	$225,000	$250,000	$275,000	$300,000
Sheet14

Cell Formulas
Range	Formula
A2:K2	A2	=COUNTA($A1:A1)*$L$2/COUNTA($A1:$L1)

 

[Phuoc]

Or try this:

=LET(a,COLUMNS(A1:L1),L2/a*SEQUENCE(,a-1))

 

[mylamly26]

This will work for any number of months based on what total you put in the last month.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	July	August	September	October	November	December	January	February	March	April	May	June
2	$25,000	$50,000	$75,000	$100,000	$125,000	$150,000	$175,000	$200,000	$225,000	$250,000	$275,000	$300,000
Sheet14

Cell Formulas
Range	Formula
A2:K2	A2	=COUNTA($A1:A1)*$L$2/COUNTA($A1:$L1)

I tried your method to achieve these results but got a different outcome. How was the below achieved?

July	August	September	October	November	December	January	February	March	April	May	June
5,541	47,525	58,375	72,402	99,482	149,311	191,965	201,844	222,766	235,877	302,907	325,000

This will work for any number of months based on what total you put in the last month.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	July	August	September	October	November	December	January	February	March	April	May	June
2	$25,000	$50,000	$75,000	$100,000	$125,000	$150,000	$175,000	$200,000	$225,000	$250,000	$275,000	$300,000
Sheet14

Cell Formulas
Range	Formula
A2:K2	A2	=COUNTA($A1:A1)*$L$2/COUNTA($A1:$L1)

 

[mylamly26]

Sorry I forgot to provide the book of business.
I have the monthly book of business. How do I add it up cumulatively by month and then pro-rate the target cumulative by month to get the monthly target?

July

August

September

October

November

December

January

February

March

April

May

June

Total

$377,677​

$978,700​

$333,485​

$426,638​

$1,064,052​

$1,623,983​

$1,077,042​

$208,066​

$4,140,820​

$420,723​

$1,103,844​

$473,849​

$12,228,879​

Book of business

July

August

September

October

November

December

January

February

March

April

May

June

Target:

$14,613,500

 

[Sanjeev1976]

Try the below :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	July	August	September	October	November	December	January	February	March	April	May	June	Total
2	$ 377,677	$ 978,700	$ 333,485	$ 426,638	$ 1,064,052	$ 1,623,983	$ 1,077,042	$ 208,066	$ 4,140,820	$ 420,723	$ 1,103,844	$ 473,849	$ 12,228,879	Book of business
3
4	July	August	September	October	November	December	January	February	March	April	May	June	Target:	$ 14,613,500
5	$ 451,324	$ 1,620,869	$ 2,019,384	$ 2,529,216	$ 3,800,757	$ 5,741,415	$ 7,028,480	$ 7,277,118	$ 12,225,395	$ 12,728,158	$ 14,047,251	$ 14,613,500
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUM(A2:L2)
A5:L5	A5	=$N$4/$M$2*SUM($A$2:A2)

 

[mylamly26]

Try the below :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	July	August	September	October	November	December	January	February	March	April	May	June	Total
2	$ 377,677	$ 978,700	$ 333,485	$ 426,638	$ 1,064,052	$ 1,623,983	$ 1,077,042	$ 208,066	$ 4,140,820	$ 420,723	$ 1,103,844	$ 473,849	$ 12,228,879	Book of business
3
4	July	August	September	October	November	December	January	February	March	April	May	June	Target:	$ 14,613,500
5	$ 451,324	$ 1,620,869	$ 2,019,384	$ 2,529,216	$ 3,800,757	$ 5,741,415	$ 7,028,480	$ 7,277,118	$ 12,225,395	$ 12,728,158	$ 14,047,251	$ 14,613,500
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUM(A2:L2)
A5:L5	A5	=$N$4/$M$2*SUM($A$2:A2)

You are a genius! Thank you so much!!! I lost a lot of sleep thinking of this. Thanks again!