If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
average of 5
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 |
#2
|
|||
|
|||
average of 5
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 C1313 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. |
#3
|
|||
|
|||
average of 5
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 C1313 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. |
#4
|
|||
|
|||
average of 5
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. |
#5
|
|||
|
|||
average of 5
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 |
#6
|
|||
|
|||
average of 5
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 |
#7
|
|||
|
|||
average of 5
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. |
#8
|
|||
|
|||
average of 5
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 |
#9
|
|||
|
|||
average of 5
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 |
#10
|
|||
|
|||
average of 5
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. |
Thread Tools | |
Display Modes | |
|
|