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 |
#11
|
|||
|
|||
average of 5
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. |
#12
|
|||
|
|||
average of 5
Thanks again for looking at my problem. Would it be alright if I redesign it
and send you the new format and see if what you wrote would work before I go changing everything. Believe it or not I need this for one little spec out of I can't tell you how many that are more important but I still need it. thanks again bob -- mrbob16 "Max" wrote: 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. |
#13
|
|||
|
|||
average of 5
Suggest you start a new thread for your revised specs/set-up
Avail your post to all responders -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "mrbob16" wrote in message ... Thanks again for looking at my problem. Would it be alright if I redesign it and send you the new format and see if what you wrote would work before I go changing everything. Believe it or not I need this for one little spec out of I can't tell you how many that are more important but I still need it. thanks again bob -- mrbob16 .. |
#14
|
|||
|
|||
average of 5
On Dec 3, 4:02*pm, mrbob16 wrote:
Thanks again for looking at my problem. Would it be alright if I redesign it and send you the new format and see if what you wrote would work before I go changing everything. Believe it or not I need this for one little spec out of I can't tell you how many that are more important but I still need it. Well, I couldn't make what Max wrote earlier work, but the concept of a helper column is valid, if you ask me. No matter how many columns you have, you can add another. Unless you are actually at the maximum width. It doesn't have to be column D, it can be far away if necessary. So, if using row 13 as the starting point as in previous examples, and column EA as your helper column.... In column EA: EA13 =1 EA14 =IF(COUNT(B14)=0,EA13,EA13+1) In column C: C13 =AVERAGE(INDIRECT("B"&12+ IF(ISNA(MATCH(EA13-4,$EA$13:EA13,0)), 1,MATCH(EA13-4,$EA$13:EA13,0))):B13) (I broke that formula up for display here, put it back together in Excel with no spaces) |
#15
|
|||
|
|||
average of 5
You know, I forgot about wanting a different average every 6th cell.
But I don't understand that part. If your 5-cell average may cover more than 5 contiguous rows due to blanks, what does an average of the averages mean every 6th row? It would vary, if you ask me. Should it be an average of the last 5 unique averages, instead? Also, how do you make space for this in a column of different average formulas? See, if you come to row 24, which should be a "6th row average" with a different formula, and if there are amounts in B24 and B25 rather than blanks, then your average of 5 cells where B24 is the 5th cell will NEVER show up. So you've just lost a data point. I guess my point is that the "6th row average" ought to be 2 different things than you have outlined. From what I see, anyway. 1) Perhaps, it should be an average of the last 5 unique averages, not the last 5 rows. 2) But definitely, it ought to be in a separate column so as not to lose data in Column C. |
#16
|
|||
|
|||
average of 5
If I could spiky can I send you the chart. it is a little easier to show than
to explain. The different formula for the sixth cell is in column c for the averages of the the last five tests whether there is number or not and it is just for that column. What I need is a running average in the other cells to get that average. again let me know if I could send it to you or upload it to you so you can visualize what I mean. Thanks bob -- mrbob16 "Spiky" wrote: You know, I forgot about wanting a different average every 6th cell. But I don't understand that part. If your 5-cell average may cover more than 5 contiguous rows due to blanks, what does an average of the averages mean every 6th row? It would vary, if you ask me. Should it be an average of the last 5 unique averages, instead? Also, how do you make space for this in a column of different average formulas? See, if you come to row 24, which should be a "6th row average" with a different formula, and if there are amounts in B24 and B25 rather than blanks, then your average of 5 cells where B24 is the 5th cell will NEVER show up. So you've just lost a data point. I guess my point is that the "6th row average" ought to be 2 different things than you have outlined. From what I see, anyway. 1) Perhaps, it should be an average of the last 5 unique averages, not the last 5 rows. 2) But definitely, it ought to be in a separate column so as not to lose data in Column C. |
#17
|
|||
|
|||
average of 5
On Dec 9, 5:16*pm, mrbob16 wrote:
If I could spiky can I send you the chart. it is a little easier to show than to explain. That's fine. |
#18
|
|||
|
|||
average of 5
Actually Spiky the file is still available if you look at the reply I left on
12/2 to max the link is still there to download. -- mrbob16 "Spiky" wrote: On Dec 9, 5:16 pm, mrbob16 wrote: If I could spiky can I send you the chart. it is a little easier to show than to explain. That's fine. |
#19
|
|||
|
|||
average of 5
I tried my solution in your file and it works, just plug it into the
particular cells like I said above and then copy C13 to each cell where it belongs, and copy EA14 down as far as necessary (at least to EA59). And it will ignore the fact that the 6th row is different as long as you don't put anything in B18, B24, etc. I'm still not exactly sure what you want in the 6th cell, like C18, C24, etc. In different posts you've indicated 2 or 3 different ideas for these cells, and another in the file itself. I think one of these is all you are looking for, and it can be copied to each cell where you want it: C18: =AVERAGE(C13:C17) *or* C18: =AVERAGE(B13:B17) BTW: After using a column off to the right like this, change your print settings to NOT include it. Otherwise you'll get lots of extra paper. And you don't have to use column EA, it could be any empty column. You'd just have to change each instance of "EA" to whatever column you use in each formula. |
#20
|
|||
|
|||
average of 5
Hello,
I suggest to go this route: Example: Values in A1327: 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 A1327: 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 |
Thread Tools | |
Display Modes | |
|
|