| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average last 4 entries in a row.
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 -- Rick |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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),CO LUMN(C4:AB4)),MIN(4,COUNT(C4:AB4))):AB4) confirmed with CTRL + SHIFT + ENTER -- DonkeyOte ------------------------------------------------------------------------ DonkeyOte's Profile: http://www.thecodecage.com/forumz/member.php?userid=231 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123395 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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),CO LUMN(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 -- Rick "DonkeyOte" wrote: 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),CO LUMN(C4:AB4)),MIN(4,COUNT(C4:AB4))):AB4) confirmed with CTRL + SHIFT + ENTER -- DonkeyOte ------------------------------------------------------------------------ DonkeyOte's Profile: http://www.thecodecage.com/forumz/member.php?userid=231 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123395 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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! ================================================== === "RickMoore" wrote in message ... 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! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
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!!! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
|
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
You're very welcome, and thank you for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RickMoore" wrote in message ... 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!!! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Average last 4 entries in a row.
OR, just use the actual count itself to create a True or False:
=IF(COUNT(C4:AB4),AVERAGE(INDEX(C4:AB4,LARGE(COLUM N(A:Z)*(ISNUMBER(C4:AB4)), 4)):AB4),"NEED DATA") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Don Guillett" wrote in message ... =IF(COUNT(C4:AB4)=0,"",AVERAGE(INDEX(C4:AB4,LARGE( COLUMN(A:Z)*(ISNUMBER(C4:A B4)),4)):AB4)) if no numbers. -- Don Guillett Microsoft MVP Excel SalesAid Software "RagDyeR" wrote in message ... 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! ================================================== === "RickMoore" wrote in message ... 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! -- Rick "RagDyeR" wrote: 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! ================================================== === "RickMoore" wrote in message ... 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 -- Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average of the last x entries with conditions | Excel Worksheet Functions | |||
| How do I get the average of entries in a group of columns? | Excel Discussion (Misc queries) | |||
| average low 10 of last 20 entries | Excel Worksheet Functions | |||
| Average of column entries | Excel Discussion (Misc queries) | |||
| Any way to calculate an average for more than 30 entries? | Excel Worksheet Functions | |||
Linear Mode
