How to Divide If Data Exists?

[kubrendan2]

I have a spreadsheet where I track data each month, which produces one number for the month. I'd like to have an average of each month without having to manually change the number of months in which data exists. Is there a way to easily create a formula that will calculate the average after a month is over (basically, dividing by 1 after February 1, dividing by 2 after March 1, etc.

This is all that is there now, since it's still January. Each of the zeros are for the months that haven't happened yet.

219

0

0

0

0

0

0

0

0

0

0

0

Thanks!

 

[awoohaw]

I'm confused with your set up. Is this expected results or the data you want to have averaged?
Can you post some dummy data for 3 or 4 months?

Do you want something like this?:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													ytd avg
2	219	0	0	0	0	0	0	0	0	0	0		219
3
4
5	219	244	257	223	0	0	0	0	0	0	0		235.75
Sheet3

Cell Formulas
Range	Formula
M2,M5	M2	=AVERAGEIFS(A2:K2,A2:K2,">"&0)

 

[kubrendan2]

Thank you! You have the data idea correct; except I'd prefer it to not calculate into the average until the month is over (since one month with 219 and the next with 4 wouldn't look good). I was trying to put =today() in one cell and then try to set the denominator based on if that cell < 02/01/2024, then = 0, if < 03/01/2024, then = 1, and so on, but I was not getting the syntax correct, if that is even a possible way to do it.

 

[awoohaw]

Okay, I have hard coded a date in this sample, you can insert a Today Function in there...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	2023-01-01	2023-02-01	2023-03-01	2023-04-01	2023-05-01	2023-06-01	2023-07-01	2023-08-01	2023-09-01	2023-10-01	2023-11-01	2023-12-01
2	Month Name based on date	January	February	March	April	May	June	July	August	September	October	November	December	ytd avg
3		219	0	0	0	0	0	0	0	0	0	0		219
4
5
6		219	244	257	223	0	0	0	0	0	0	0		240
Sheet3

Cell Formulas
Range	Formula
C1:M2	C1	=EDATE(B1,1)
N3	N3	=SUM(($B$3:$M$3)* ($B$2:$M$2<EOMONTH(DATE(2023,2,5),-1)+1))
N6	N6	=SUM(($B6:$M6)*($B$2:$M$2<EOMONTH(DATE(2023,4,5),-1)+1))/ SUM(--($B$2:$M$2<EOMONTH(DATE(2023,4,5),-1)+1))

​

 

[AhoyNC]

Maybe another option.
Enter the last day of each month in the date range. You can format this as just month or month - year, etc (or any way you want).
Then use the AVERAGEIF function.
For the example I started with dates in 2023 just to have some data.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	10/31/2023	11/30/2023	12/31/2023	1/31/2024	2/29/2024	3/31/2024	4/30/2024	5/31/2024	6/30/2024
2		2	4	5	6	0	0	0	0	0
3
4	Average	3.6667
Sheet1

Cell Formulas
Range	Formula
B4	B4	=AVERAGEIF($B$1:$J$1,"<="&TODAY(),$B$2:$J$2)

 

[kubrendan2]

I'm sure that first way worked, but it was beyond my level of ability to make it work. That second idea worked great, other than it gives me a division by 0 error now, since the first month isn't over. i forget how to make that just be blank instead of displaying the error?

Thank you both so much for taking the time to help!!

 

[AhoyNC]

This should stop the error and show a blank.
=IFERROR(AVERAGEIF($B$1:$J$1,"<="&TODAY(),$B$2:$J$2),"")