Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging by N (97,2000)

    Assume that column A has a list of values in it. In column B I want a list of n averages, for instance b1=average(a1:a5), b2=average(a6:a10), etc.
    Is there a simple formula that can be replicated down column B that would be generic for any value of n? I thought that OFFSET might be a
    possibility but can't think of a way to implement it.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Averaging by N (97,2000)

    Put this in B1 and copy it down the rows

    <pre>=AVERAGE(INDIRECT("A"&(+ROW()-1)*NumValues+1&":A"&(+ROW()*NumValues)))</pre>


    The defined range "NumValues" is 5 in your example, but it can be any value>0 (it is the number of points you want to average. Change the columns as appropriate also.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Averaging by N (97,2000)

    This
    =AVERAGE(INDIRECT("A"&ROW()&":A"&(ROW()+$D$1-1)))

    will give A1:A5, A2:A6, A3:A7, ... etc

    He wanted A1:A5, A6:A10, A11:A15, ... etc


    =AVERAGE(INDIRECT("A"&(+ROW()-1)*$D$1+1&":A"&(+ROW()*$D$1)))

    If you want a cell listed, though if you defined the cell D1 as "NumValues" you have what I listed:

    =AVERAGE(INDIRECT("A"&(+ROW()-1)*NumValues+1&":A"&(+ROW()*NumValues)))

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging by N (97,2000)

    Oops, you're right (of course) <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging by N (97,2000)

    Ignore this post - it doesn't answer the question - sorry.

    The formula you provide will fill down correctly for any value of n. But perhaps you want to specify n in a separate cell, so that you can vary it easily. Say that you put the value of n in cell D1. Put the following formula into B1:

    =AVERAGE(INDIRECT("A"&ROW()&":A"&(ROW()+$D$1-1)))

    This formula can be filled down. You can change the number of cells over which the average is calculated by changing the value in D1.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging by N (97,2000)

    Using non-volatile INDEX...

    =IF(COUNT($A$1:A1)*NumVals<=COUNT($A$1:$A$25),AVER AGE(INDEX($A$1:$A$25,IF(COUNT($A$1:A1)>1,NumVals*( COUNT($A$1:A1)-1)+1,1),1):INDEX($A$1:$A$25,COUNT($A$1:A1)*NumVals ,1)),"")

    Using volatile OFFSET...

    =IF(COUNT($A$1:A1)*NumVals<=COUNT($A$1:$A$25),AVER AGE(OFFSET(A$1,IF(COUNT($A$1:A1)>1,NumVals*(COUNT( $A$1:A1)-1),0),0,NumVals,1)),"")

    NumVals stands for blocks of "N values" that you want to average.

    Both formulas should behave correctly against inserting rows before the data.
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •