Average of last 3 numbers in non-continuous range

[johannes2008]

Hello

I am working on on a spreadsheet that uses the average of the last three months "actual" data to forecast the rest of the fiscal year. As "actual" data is entered for the next month I want the forecast value to update. To add to the complexity my "actual" data cells are non-continuous and not every cell has numbers in it. As well I want it to use the 4th, 3rd, and 2nd last data points as the last data point if for the current month and not complete yet.

So in the above example for row 4 the formula should take K4, H4 & E4, average them and return the value to S4. The formula should not use Q4 because it is empty nor use N4 as that is the current month and the data is not complete. However once a number is present in Q4 it should then use H4, K4 & N4 to calculate the average.

I hope this makes sense

Thanks

 

[awoohaw]

will you be continuing to add columns for each month as your year progresses? Have you considered putting the data in to columns and then using a pivot report to get the data you need?

Also, we need to know what version of excel you are using as solutions can drastically vary (or even not exist) depending on that. you should update your profile so the version(s) you use are displayed with your profile avatar in the messages.

 

[awoohaw]

try this:

Book1
	E			H			K			N			Q		S
1													if q blank dn use n
2
3
4	5			9			10			1					8
5	5			9			10			1			5		6.666667
Sheet2

Cell Formulas
Range	Formula
S4:S5	S4	=AVERAGE(H4,K4,IF(ISBLANK(Q4),E4,N4))

 

[johannes2008]

will you be continuing to add columns for each month as your year progresses? Have you considered putting the data in to columns and then using a pivot report to get the data you need?

Also, we need to know what version of excel you are using as solutions can drastically vary (or even not exist) depending on that. you should update your profile so the version(s) you use are displayed with your profile avatar in the messages.

The data will go from Jan 2023 to Mar 2024 and we use Excel 365.

 

[awoohaw]

okay, the formula I give above will work if you insert the exact same number of columns with each new month.

 

[awoohaw]

okay, the formula I give above will work if you insert the exact same number of columns with each new month.

wait, i may be wrong on that... let me test it out.

 

[awoohaw]

Okay, yeah, it will be clunky. You'll have to COPY column S formulas to the 3rd column over, then delete column S. You will have DIV/0 errors until you enter updated data. If you insert columns then you'll have to update the cell references in the formulas and copy them down.

 

[Peter_SSs]

So in the above example for row 4 the formula should take K4, H4 & E4, average them and return the value to S4.

What should happen in row 5?
What should happen if any row has some values but not enough to collect 3 relevant values?

More examples and expected results with XL2BB would be helpful.

 

[awoohaw]

What should happen in row 5?
What should happen if any row has some values but not enough to collect 3 relevant values?

More examples and expected results with XL2BB would be helpful.

Good question Peter. But, I was going on what the OP said by only data missing in the most recent full month. If it is blank, I guess the average array will have a blank value in it.

 

[Peter_SSs]

Pending answers to the above, you could test this as a starting point.

23 02 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2		Oct			Nov			Dec			Jan			Feb			Mar
3
4		1		5	5		5	9		5	10		5	1		5			8
Average

Cell Formulas
Range	Formula
S4	S4	=LET(f,FILTER(B4:R4,(B2:R2<>"")*(B4:R4<>"")),AVERAGE(INDEX(f,,SEQUENCE(,3,COLUMNS(f)-1,-1))))