Calculate Average with Criteria

[kumara_faith]

Hi,

I have the following table:

Book2
	C	D	E	F	G	H	I	J	K	L	M	N	O
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Current Total	8912.64	2500
4	Forecasted Total	8912.64	7387.5
Sheet1

I want to be able to calculate the forecasted total for every month based on the total for the prior month and based on the remaining days for the current month. Example: The total for January is 8912.64. Assuming todays date is Feb 12. The remaining days for Feb is 17 days. Jan had 31 days , therefore daily average was 287.50. Therefore, 287.45 X 17 days remaining for Feb + Current Total for Feb=7387.5.

Is there a way to do this with a formula ? Appreciate all the help.

 

[Cubist]

Maybe this.

Book1
	C	D	E	F	G	H	I	J	K	L	M	N
1	Today	2/12/24
2		1/1/24	2/1/24	3/1/24	4/1/24	5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24
3	Current Total	8912.64	2500.00
4	Forecasted Total	8912.64	7387.58
Sheet3

Cell Formulas
Range	Formula
D2:N2	D2	=EDATE(DATE(2024,1,1),SEQUENCE(1,11,0))
E4	E4	=MAX(0,DATEDIF($D$1,EOMONTH(E2,0),"d"))*D3/DAY(EOMONTH(D2,0))+E3
Dynamic array formulas.

 

[kumara_faith]

Thanks Cubist. Appreciate your patience and solution.