Average Last 5 in row, excluding blanks

[chrisb182]

Hi All,

I've seen many answers to this question on here but none of them are working for my spready.

Basically I want to average the last 5 numbers in a row, excluding any blanks.

Here's a screenshot of some numbers I'm working with, with the expected result.

If someone could help, I'd be immensely grateful!

Cheers,

Chris

 

[RobP]

This one should work:

=AVERAGEIF(O3:INDIRECT(ADDRESS(3,COLUMN(O3)-4)),"<>0",O3:INDIRECT(ADDRESS(3,COLUMN(O3)-4)))

sequence_average_payments_52weeks.xlsx
	CW	CX	CY	CZ	DA	DB	DC
1
2
3	0	12.9	0	7.7	17.2		12.6
4
Sheet1

Cell Formulas
Range	Formula
DC3	DC3	=AVERAGEIF(DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-4)),"<>0",DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-4)))

 

[chrisb182]

This one should work:

=AVERAGEIF(O3:INDIRECT(ADDRESS(3,COLUMN(O3)-4)),"<>0",O3:INDIRECT(ADDRESS(3,COLUMN(O3)-4)))

sequence_average_payments_52weeks.xlsx
	CW	CX	CY	CZ	DA	DB	DC
1
2
3	0	12.9	0	7.7	17.2		12.6
4
Sheet1

Cell Formulas
Range	Formula
DC3	DC3	=AVERAGEIF(DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-4)),"<>0",DA3:INDIRECT(ADDRESS(3,COLUMN(DA3)-4)))

Hi mate,

I'm afraid that's not working. I entered that formula (replacing DA3 with O3) and it comes out with 17.93 for your example above.

 

[RobP]

Hi, not sure to understand - when you say my example above - the 12.6 is the answer which was in your example (thus so you can see it works for last 5 values (or 3 actually in this case).

The formula you can see in DC3 from the sheet above is slightly different, as you said from the one I suggested for your numbers which start in Col O and count backwards.

Which data are you using to get 17.93 can I ask ?

thanks
ROb
(keep in mind the ADDRESS no."3" in the formula refers to Row 3 - which is where I had it in my example. Sorry, you might need to change it to "4" as below)

=AVERAGEIF(O3:INDIRECT(ADDRESS(4,COLUMN(O3)-4)),"<>0",O3:INDIRECT(ADDRESS(4,COLUMN(O3)-4)))

The -4 inside means count back 4 steps. eg. take last 5 cols of data)

 

[chrisb182]

Hi, not sure to understand - when you say my example above - the 12.6 is the answer which was in your example (thus so you can see it works for last 5 values (or 3 actually in this case).

The formula you can see in DC3 from the sheet above is slightly different, as you said from the one I suggested for your numbers which start in Col O and count backwards.

Which data are you using to get 17.93 can I ask ?

thanks
ROb
(keep in mind the ADDRESS no."3" in the formula refers to Row 3 - which is where I had it in my example. Sorry, you might need to change it to "4" as below)

=AVERAGEIF(O3:INDIRECT(ADDRESS(4,COLUMN(O3)-4)),"<>0",O3:INDIRECT(ADDRESS(4,COLUMN(O3)-4)))

The -4 inside means count back 4 steps. eg. take last 5 cols of data)

Ahhh OK. That makes sense. Changing the ADDRESS to '4' does work to get the 12.6 figure.

Bearing in mind I have 500+ rows to maintain and I'll have to drag the formula down, is there anyway for the ADDRESS to change automatically to correspond with whatever Row the formula is on?

 

[RobP]

ouch .. yes.. instead of the "3" - replace with ROW() - as this will give you the current row number. Let me know if it works.

 

[Fluff]

How about

Fluff.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O																										AO
1
2	0	0	0	0	0	0	20.3	33.9	13.6	19.8	18.1																											21.1
3	0	0	0	0	0	0	0	12.9	0	7.7	17.2																											12.6
4	4	8	6	11	22	13	5	9	28	0	17																											14.4
5	34.9	17.3	17.1	23.4	30.5	19.6	26.9	0	32.1	31.1	27.5																											27.4
Data

Cell Formulas
Range	Formula
AO2:AO5	AO2	=IFERROR(AVERAGEIFS(O2:INDEX(D2:O2,AGGREGATE(14,6,SEQUENCE(,12)/(D2:O2<>0),5)),O2:INDEX(D2:O2,AGGREGATE(14,6,SEQUENCE(,12)/(D2:O2<>0),5)),"<>0"),AVERAGEIFS(D2:O2,D2:O2,"<>0"))

 

[chrisb182]

@RobP - Good news: The ROW() part of the formula works fine. Unfortunately, it didn't work for all the rows.

@Fluff - That formula works great. Thank you very much!

I really appreciate both of your replies!

 

[Fluff]

Just realised I left the sequence function in, which doesn't exist in xl2016, so are you using a newer version of xl? If so what?

 

[RobP]

Ha. typical.. [Deep sigh]