Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
How to find avg for specific step size? I have annual data and want to find
weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
"Omkar" wrote:
How to find avg for specific step size? I have annual data and want to find weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula? One way .. Source data is assumed in A1 down Put in any starting cell, say in B2: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6)) Copy B2 down B2 returns: =AVERAGE(A1:A6) B3 returns: =AVERAGE(A7:A14) and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
"Omkar" wrote:
How to find avg for specific step size? I have annual data and want to find weekly avg. i.e avg A1:A6 then A7:A14 etc.. How to do it using sigle formula? One way .. Source data is assumed in A1 down Put in any starting cell, say in B2: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6)) Copy B2 down B2 returns: =AVERAGE(A1:A6) B3 returns: =AVERAGE(A7:A14) and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
Corrections, sorry:
Put in any starting cell, say in B2: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6)) The formula in B2 should be: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7)) and lines B2 returns: =AVERAGE(A1:A6) B3 returns: =AVERAGE(A7:A14) and so on .. should read as: B2 returns: =AVERAGE(A1:A7) B3 returns: =AVERAGE(A8:A14) and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
Thank you Max.
I did as you said, but I am getting different answers. (I compared values which I found from separating each data series of 7 points and finding their avgs.) Can you explain what is significance of each term. I know some. But you have written nothing for Rows,Columns,Height for Offset function. Also what is *7-6 signifies in reference? Please try to help me. Thank you for your help. "Max" wrote: Corrections, sorry: Put in any starting cell, say in B2: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6)) The formula in B2 should be: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7)) and lines B2 returns: =AVERAGE(A1:A6) B3 returns: =AVERAGE(A7:A14) and so on .. should read as: B2 returns: =AVERAGE(A1:A7) B3 returns: =AVERAGE(A8:A14) and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
Hi!
What is the starting cell of the range where your numbers are located? Is it A1? What step size do you want? In your post you have: A1:A6 then A7:A14 etc.. A1 to A6 = 6 A7 to A14 = 8 Biff "Omkar" wrote in message ... Thank you Max. I did as you said, but I am getting different answers. (I compared values which I found from separating each data series of 7 points and finding their avgs.) Can you explain what is significance of each term. I know some. But you have written nothing for Rows,Columns,Height for Offset function. Also what is *7-6 signifies in reference? Please try to help me. Thank you for your help. "Max" wrote: Corrections, sorry: Put in any starting cell, say in B2: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,6)) The formula in B2 should be: =AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7)) and lines B2 returns: =AVERAGE(A1:A6) B3 returns: =AVERAGE(A7:A14) and so on .. should read as: B2 returns: =AVERAGE(A1:A7) B3 returns: =AVERAGE(A8:A14) and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
"Omkar" wrote:
I did as you said, but I am getting different answers. (I compared values which I found from separating each data series of 7 points and finding their avgs.) I'm not sure what happened over there, but here's a sample implementation to illustrate .. http://www.savefile.com/files/6466382 Averaging 7 cell column ranges.xls Btw, I had presumed there were some typos in your original post's range refs: .. weekly avg. i.e avg A1:A6 then A7:A14 .. I focused more on your "weekly avg" and presumed it should have read as: avg A1:A7 then A8:A14 .. Can you explain what is significance of each term. I know some. But you have written nothing for Rows,Columns,Height for Offset function. Also what is *7-6 signifies in reference? Some explanations .. In the expression: OFFSET(INDIRECT("A"&ROW(A1)*7-6),,,7) ROW(A1) is used as the serial incrementer when we copy down In any cell, =ROW(A1) resolves to 1, when copied down, it becomes ROW(A2) which returns 2, then ROW(A3) which returns 3 and so on.. The *7-6 is an arithmetic op applied so that we can get the desired series: 1, 8, 15, ... (steps of 7 cells), viz.: ROW(A1)*7-6 resolves to: 1 x 7 - 6 = 1 ROW(A2)*7-6 gives: 2 x 7 - 6 = 8 ROW(A3)*7-6 returns: 3 x 7 - 6 = 15 and so on, as the formula is copied down (see the above results by putting in any cell: =ROW(A1)*7-6, then copy down) The numbers 1, 8, 15 are then concatenated with "A" to yield the text string cell refs: A1, A8, A15 which represent the desired start points for the ranges: A1:A7 A8:A14 A15:A22 INDIRECT resolves the text string cell refs to give the OFFSET references, viz.: OFFSET(A1,,,7) OFFSET(A8,,,7) OFFSET(A15,,,7) The "7" is the height param in OFFSET which grabs a 7 cell col range from the OFFSET reference, hence the 3 OFFSETs above would effectively return the ranges: A1:A7 A8:A14 A15:A22 for the AVERAGE( ... ) function to evaluate -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find avg for step size?
Typo, lines: A15:A22
should read as: A15:A21 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find | Excel Worksheet Functions | |||
Find Function | Excel Discussion (Misc queries) | |||
chart size | Charts and Charting in Excel | |||
My tabs' font size is smaller - how do I restore default size? | Excel Discussion (Misc queries) | |||
Font size prints same size regardless of how I set it in Excel | Excel Discussion (Misc queries) |