I have a spreadsheet in cell b13 I start entering data till b59. I want to
take a running average of the last 5 cells that contain data. Not all cells
will have an entry and every 6th cell has a different formula. I also want to
move this running average to row c.
--
mrbob16

One way to model it up for delivery

Target range is B13:B59
In C13: =IF(COUNT(B13)=0,"",ROWS($1:1))
In D13: =INDEX($B$13:$B$59,SMALL($C$13:$C$59,ROWS($1:1)))
Copy C13:D13 down to D59. This dynamically screens the target range in col B
for numbers, and then packs it up in col D (makes it into a contiguous range)

Then place in say, E13:
=IF(COUNT($D$13:$D$59)<5,"",
AVERAGE(OFFSET($D$13,COUNT($D$13:$D$59)-1,,-5)))

E13 will return the desired running average of the last 5 cells
that contain data within the target range
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"mrbob16" wrote:
I have a spreadsheet in cell b13 I start entering data till b59. I want to
take a running average of the last 5 cells that contain data. Not all cells
will have an entry and every 6th cell has a different formula. I also want to
move this running average to row c.

I am using a lot of columns and those cells are being used. If I get what you
are saying is I have to make a small database that will then average the
numbers in order they are placed there? And would it be easier if I sent you
what I'm working on so you can see it or just try to put it in here.

thanks
bob

--
mrbob16


"Max" wrote:

One way to model it up for delivery

Target range is B13:B59
In C13: =IF(COUNT(B13)=0,"",ROWS($1:1))
In D13: =INDEX($B$13:$B$59,SMALL($C$13:$C$59,ROWS($1:1)))
Copy C13:D13 down to D59. This dynamically screens the target range in col B
for numbers, and then packs it up in col D (makes it into a contiguous range)

Then place in say, E13:
=IF(COUNT($D$13:$D$59)<5,"",
AVERAGE(OFFSET($D$13,COUNT($D$13:$D$59)-1,,-5)))

E13 will return the desired running average of the last 5 cells
that contain data within the target range
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"mrbob16" wrote:
I have a spreadsheet in cell b13 I start entering data till b59. I want to
take a running average of the last 5 cells that contain data. Not all cells
will have an entry and every 6th cell has a different formula. I also want to
move this running average to row c.

Upload your sample file using a free filehost,
then post a link to it here

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"mrbob16" wrote in message
...
I am using a lot of columns and those cells are being used. If I get what
you
are saying is I have to make a small database that will then average the
numbers in order they are placed there? And would it be easier if I sent
you
what I'm working on so you can see it or just try to put it in here.

My file is way to big for the minimum. But I posted an example and maybe that
would help.
--
mrbob16


"Max" wrote:

Upload your sample file using a free filehost,
then post a link to it here

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"mrbob16" wrote in message
...
I am using a lot of columns and those cells are being used. If I get what
you
are saying is I have to make a small database that will then average the
numbers in order they are placed there? And would it be easier if I sent
you
what I'm working on so you can see it or just try to put it in here.

http://freefilehosting.net/download/42g42
hey Max I tried again to upload that problem and realized it said 5mb and i
thought it said 5kb so i didn't think it would fit but here it is and i tried
to explain what i need on the bottom of the page.

thanks again if could look at it one more time.

--
mrbob16


"Max" wrote:

Upload your sample file using a free filehost,
then post a link to it here

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"mrbob16" wrote in message
...
I am using a lot of columns and those cells are being used. If I get what
you
are saying is I have to make a small database that will then average the
numbers in order they are placed there? And would it be easier if I sent
you
what I'm working on so you can see it or just try to put it in here.

I'm afraid it's the same conclusion, Bob.

There are 2 issues with your requirements which seem insurmountable to me
a. That you want an average result on every line in col C, but it is to
somehow disregard any interceding blanks in col B as part and parcel of the
"last 5" data rows
b. The "interceding" difficulty is further compounded by the presence of a
6th "average" line after each set of 5 lines reserved for data in col B which
contains a different formula in col C, and this 6th line is to be, like the
blanks in col B, somehow skipped in the desired average of the last 5 cells
with data in col B

FWIW, here's my best scenario should you decide to review your specs /
layout re-design. Supposing the data is continuous in B13 down (w/o any
interceding blank cells nor breaks every 6th line), and you want an average
of the last 5 cells with data in col B to reflect in col C, then in C13,
copied down:
=AVERAGE(OFFSET(B13,,,-MIN(COUNT(B$13:B13),5)))
will return those results. The top 4 formula cells, ie C13:C16 will return
the desired average of last 1 to 4 data cells in col B, while C17, C18, etc
down will return the "last 5".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"mrbob16" wrote:
http://freefilehosting.net/download/42g42
hey Max I tried again to upload that problem and realized it said 5mb and i
thought it said 5kb so i didn't think it would fit but here it is and i tried
to explain what i need on the bottom of the page.

thanks again if could look at it one more time.

Hi,

When you say every 6th cell has a different formula, what are you telling
us? Should we be ignoring every 6th cell? Why don't you show us some sample
data. We are supposed to average the last 5 cells with entries but ?? what
about that 6th cell. So are you saying your data might look like this:

Bob
5
11/1/2008
4
Red

34

Green
Blue
13
23

54

so a running average beside the last cell would average from Green to 54?
That's five cells with entries. Or should it average from 4 to 54? But if
the number 13 is one of those "other formulas" we should average from 5 to
54? And if the cell above 54 contains a spacebar, then what? Do we average
through the date or ignore it or what?

Thanks,
Shane Devenshire



"mrbob16" wrote:

I have a spreadsheet in cell b13 I start entering data till b59. I want to
take a running average of the last 5 cells that contain data. Not all cells
will have an entry and every 6th cell has a different formula. I also want to
move this running average to row c.
--
mrbob16

I'll try to do it here

col a col b col c
11-1-08 2.547
11/2/08 2.555 need avg
11/3/08 2.560 need avg of 3
11/4/08 2.575 need avg of 4
11/4/08 need avg of 4or 5 if there is data
blank avg of the avgs
11/5/08 2.575 avg of last 5
11/5/08 blank avg of last 5
11/6/08 2.580 avg of last 5

all columns to the right of c contain other data and the avg line is
basically an avg of those 5 rows above whether they have data or not but I
need to keep a running avg in col c for just the last five cells in colb that
have data not blanks. I hopw this helps.

I'm not to great with computers so max I'll try and get it uploaded. I hope
this will help.

thanks
bob
--
mrbob16


"Shane Devenshire" wrote:

Hi,

When you say every 6th cell has a different formula, what are you telling
us? Should we be ignoring every 6th cell? Why don't you show us some sample
data. We are supposed to average the last 5 cells with entries but ?? what
about that 6th cell. So are you saying your data might look like this:

Bob
5
11/1/2008
4
Red

34

Green
Blue
13
23

54

so a running average beside the last cell would average from Green to 54?
That's five cells with entries. Or should it average from 4 to 54? But if
the number 13 is one of those "other formulas" we should average from 5 to
54? And if the cell above 54 contains a spacebar, then what? Do we average
through the date or ignore it or what?

Thanks,
Shane Devenshire



"mrbob16" wrote:

I have a spreadsheet in cell b13 I start entering data till b59. I want to
take a running average of the last 5 cells that contain data. Not all cells
will have an entry and every 6th cell has a different formula. I also want to
move this running average to row c.
--
mrbob16

Bob,
Tried but afraid I'm out of ideas for you, sorry.
What you're after may not be possible to achieve, other than manually.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"mrbob16" wrote in message
...
I'll try to do it here

col a col b col c
11-1-08 2.547
11/2/08 2.555 need avg
11/3/08 2.560 need avg of 3
11/4/08 2.575 need avg of 4
11/4/08 need avg of 4or 5 if there is data
blank avg of the avgs
11/5/08 2.575 avg of last 5
11/5/08 blank avg of last 5
11/6/08 2.580 avg of last 5

all columns to the right of c contain other data and the avg line is
basically an avg of those 5 rows above whether they have data or not but I
need to keep a running avg in col c for just the last five cells in colb
that
have data not blanks. I hopw this helps.

I'm not to great with computers so max I'll try and get it uploaded. I
hope
this will help.

thanks
bob
--
mrbob16

Well I must thank you anyway because I and my coworkers have been working on
this for about 2 months now and we thought it has to be simpler than having
to to do it manuallyeach time and without setting up a data base to grab it
from. So again I thank you for your time and if you do find a way let me know

thanks

--
mrbob16


"Max" wrote:

Bob,
Tried but afraid I'm out of ideas for you, sorry.
What you're after may not be possible to achieve, other than manually.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"mrbob16" wrote in message
...
I'll try to do it here

col a col b col c
11-1-08 2.547
11/2/08 2.555 need avg
11/3/08 2.560 need avg of 3
11/4/08 2.575 need avg of 4
11/4/08 need avg of 4or 5 if there is data
blank avg of the avgs
11/5/08 2.575 avg of last 5
11/5/08 blank avg of last 5
11/6/08 2.580 avg of last 5

all columns to the right of c contain other data and the avg line is
basically an avg of those 5 rows above whether they have data or not but I
need to keep a running avg in col c for just the last five cells in colb
that
have data not blanks. I hopw this helps.

I'm not to great with computers so max I'll try and get it uploaded. I
hope
this will help.

thanks
bob
--
mrbob16

Hello,

I suggest to go this route:

Example:

Values in A13:D27:
1 1 13 1
1 2 14 1.5
1 3 15 2
1 15 2
15 2
2 4 16 2.5
2 5 17 3
2 6 18 4
2 7 19 5
19 4
3 19 5
3 8 20 6
3 9 21 7
3 10 22 8
22 5.5

Formulas in A13:D27:
1 1 =ROW() =IF(ISBLANK(A13),AVERAGE(B$13:B13),AVERAGE(INDEX($ B$13:$B
$26,IFERROR(MATCH(C13-4,$C$13:$C$26,0),1)):B13))
1 2 =C13+1-ISBLANK(B14) =IF(ISBLANK(A14),AVERAGE(B$13:B14),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C14-4,$C$13:$C$26,0),1)):B14))
1 3 =C14+1-ISBLANK(B15) =IF(ISBLANK(A15),AVERAGE(B$13:B15),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C15-4,$C$13:$C$26,0),1)):B15))
1 =C15+1-ISBLANK(B16) =IF(ISBLANK(A16),AVERAGE(B$13:B16),AVERAGE(INDEX
($B$13:$B$26,IFERROR(MATCH(C16-4,$C$13:$C$26,0),1)):B16))
=C16+1-ISBLANK(B17) =IF(ISBLANK(A17),AVERAGE(B$13:B17),AVERAGE(INDEX
($B$13:$B$26,IFERROR(MATCH(C17-4,$C$13:$C$26,0),1)):B17))
2 4 =C17+1-ISBLANK(B18) =IF(ISBLANK(A18),AVERAGE(B$13:B18),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C18-4,$C$13:$C$26,0),1)):B18))
2 5 =C18+1-ISBLANK(B19) =IF(ISBLANK(A19),AVERAGE(B$13:B19),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C19-4,$C$13:$C$26,0),1)):B19))
2 6 =C19+1-ISBLANK(B20) =IF(ISBLANK(A20),AVERAGE(B$13:B20),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C20-4,$C$13:$C$26,0),1)):B20))
2 7 =C20+1-ISBLANK(B21) =IF(ISBLANK(A21),AVERAGE(B$13:B21),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C21-4,$C$13:$C$26,0),1)):B21))
=C21+1-ISBLANK(B22) =IF(ISBLANK(A22),AVERAGE(B$13:B22),AVERAGE(INDEX
($B$13:$B$26,IFERROR(MATCH(C22-4,$C$13:$C$26,0),1)):B22))
3 =C22+1-ISBLANK(B23) =IF(ISBLANK(A23),AVERAGE(B$13:B23),AVERAGE(INDEX
($B$13:$B$26,IFERROR(MATCH(C23-4,$C$13:$C$26,0),1)):B23))
3 8 =C23+1-ISBLANK(B24) =IF(ISBLANK(A24),AVERAGE(B$13:B24),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C24-4,$C$13:$C$26,0),1)):B24))
3 9 =C24+1-ISBLANK(B25) =IF(ISBLANK(A25),AVERAGE(B$13:B25),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C25-4,$C$13:$C$26,0),1)):B25))
3 10 =C25+1-ISBLANK(B26) =IF(ISBLANK(A26),AVERAGE(B$13:B26),AVERAGE
(INDEX($B$13:$B$26,IFERROR(MATCH(C26-4,$C$13:$C$26,0),1)):B26))
=C26+1-ISBLANK(B27) =IF(ISBLANK(A27),AVERAGE(B$13:B27),AVERAGE(INDEX
($B$13:$B$26,IFERROR(MATCH(C27-4,$C$13:$C$26,0),1)):B27))

Please note that I did not apply an average of averages but just a
simple total average which I regard as correct (better).

IFERROR(exp1,expr2) is an Excel 2007 function. In older versions you
would have to write IF(ISERROR(expr1),expr2,expr1).

Regards,
Bernd