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

 

[chrisb182]

I use Microsoft Excel 365

 

[Fluff]

In that case I would suggest you update your profile.
Two other options depending on whether you have the new Take function or not

=AVERAGE(TAKE(FILTER(D2:O2,D2:O2<>0),,-5))

or

=LET(f,FILTER(D2:O2,D2:O2<>0),AVERAGE(INDEX(f,SEQUENCE(,MIN(5,COLUMNS(f)),COLUMNS(f),-1))))

 

[chrisb182]

In that case I would suggest you update your profile.
Two other options depending on whether you have the new Take function or not

=AVERAGE(TAKE(FILTER(D2:O2,D2:O2<>0),,-5))

or

=LET(f,FILTER(D2:O2,D2:O2<>0),AVERAGE(INDEX(f,SEQUENCE(,MIN(5,COLUMNS(f)),COLUMNS(f),-1))))

Hahaha. Marvellous.

Cheers buddy.

 

[Fluff]

Glad we could help & thanks for the feedback.