Average last 4 entries in a row.

[RickMoore]

I have a row of data that I would like to have a formula that will average
the last 4 cell at have entries. The data is continually added to
The data is in cells C4:AB4
The formula will be in cell AC4
There can be less than 4 entries
There can be blank cell
There are no zeros

 

[DonkeyOte]

RickMoore, do the blanks intersperse amongst data set ?

No:

=AVERAGE(INDEX(C4:AB4,MATCH(9.99999999999999E+307,C4:AB4)-3):AB4)

Yes:

=AVERAGE(INDEX(C4:AB4,LARGE(IF(ISNUMBER(C4:AB4),COLUMN(C4:AB4)),MIN(4,COUNT(C4:AB4))):AB4)
confirmed with CTRL + SHIFT + ENTER

 

[RagDyeR]

This formula will average the last 4 entries, *BUT*, will also average
entries that are less then 4, if 4 are not present.

=AVERAGE(INDEX(C4:AB4,INDEX(LARGE(ISNUMBER(C4:AB4)*COLUMN(C4:AB4),4),0)):AB4)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a row of data that I would like to have a formula that will average
the last 4 cell at have entries. The data is continually added to
The data is in cells C4:AB4
The formula will be in cell AC4
There can be less than 4 entries
There can be blank cell
There are no zeros

 

[RickMoore]

This formula appears to work for less that 4 entries, but it averages all the
entries in the row if there are more than 4. I copied the formula from your
reply, so I am pretty sure it is entered it properly. Thanks for you help!

 

[RickMoore]

The blank cells can be any place in the row There can can be any number of
blank cells between entries. Some rows have only 1 entry others have 20 with
any number of blank cells in between.
I tried entering the formula from below
=AVERAGE(INDEX(C4:AB4,LARGE(IF(ISNUMBER(C4:AB4),COLUMN(C4:AB4)),MIN(4,COUNT(C4:AB4))):AB4)
I got an excel message box that formula had an error. I copied it from your
reply, so it should be entered as you wrote it. I did enter it by using the
ctrl+shift+enter
I have been looking for a solution for years, I hope you can Help
Thanks

 

[RagDyeR]

Try this *array* formula instead.
It will average less then 4, but this one will *stop* at the last 4 entries:

=AVERAGE(INDEX(C4:AB4,LARGE(COLUMN(A:Z)*(ISNUMBER(C4:AB4)),4)):AB4)

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

This formula appears to work for less that 4 entries, but it averages all
the
entries in the row if there are more than 4. I copied the formula from your
reply, so I am pretty sure it is entered it properly. Thanks for you help!

 

[RickMoore]

This appears to work just as you said, Thanks for your help. I first
designed this spreadsheet using Excel 4, about 12 years ago and have always
wondered if there was a formula todo this. We always had a second row that
we had to double enter the data and delete to oldest entry.
Thanks again!!!

 

[Don Guillett]

=IF(COUNT(C4:AB4)=0,"",AVERAGE(INDEX(C4:AB4,LARGE(COLUMN(A:Z)*(ISNUMBER(C4:AB4)),4)):AB4))

if no numbers.

 

[Ragdyer]

You're very welcome, and thank you for the feed-back.

 

[Ragdyer]

OR, just use the actual count itself to create a True or False:

=IF(COUNT(C4:AB4),AVERAGE(INDEX(C4:AB4,LARGE(COLUMN(A:Z)*(ISNUMBER(C4:AB4)),
4)):AB4),"NEED DATA")