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

 

[johannes2008]

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))))

Thanks for the suggestion. I have made it work for my spreadsheet except I believe its taking the averages from the wrong cells. It should be taking averages from the last 3 numbers it finds in columns G&H, O&P, W&X & AG&AH

=LET(f,FILTER(G114:AN114,(G114:AN114<>"")*(G114:AN114<>"")),AVERAGE(INDEX(f,,SEQUENCE(,8,COLUMNS(f)-1,-1))))

 

[johannes2008]

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.

In row 5 the answer/average would be 54.66
There will be enough data in each row as the document includes historical data.

 

[Peter_SSs]

I believe its taking the averages from the wrong cells. It should be taking averages from the last 3 numbers it finds in columns G&H, O&P, W&X & AG&AH

Difficult to understand. Remember that all we have to go on is what you show and tell us.

• What you showed us does go to the red ranges above.
• Previously you said you wanted to average some 'Actual' numbers but the columns mentioned above (red) do not seem to correspond with the 'Actual' columns given (green) and why are some sections skipped anyway?

johannes2008.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Oct			Nov			Dec			Jan			Feb			Mar
3	Actual			Actual			Actual			Actual			Actual			Actual
4	1		5	5		5	9		5	10		5	1		5
5		22	5		58	5		60	5		89	5		15	5
6		24	5		25	5		64	5		52	5		15	5
7	5		5	6		5	8		5	1		5	1		5
Average

• Previously you told us the average should come from 2nd last, 3rd last and 4th last. Now you are saying last 3. Can you clarify the actual requirement?

As I said before ..

More examples and expected results with XL2BB would be helpful.

We cannot copy/paste data from images.