+ Reply to Thread
Results 1 to 7 of 7

Average every nth cell in a column

  1. #1
    Registered User
    Join Date
    10-30-2008
    Location
    ID
    Posts
    6

    Average every nth cell in a column

    I've read a number of posts regarding this problem and tried changing the codes around, but I'm still not getting the right values.

    Column D has values every third row, starting with D6 and ending with D96. so D6, D9, D12.....etc. have values. In D100, I want to display the average of the values D6:D96, excluding the blank cell zeros. Please help any way you can. Thanks so much.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677
    You can use the following array formula

    =AVERAGE(IF(MOD(ROW(D6:D96)-ROW(D6),3)=0,IF(D6:D96>0,D6:D96)))

    confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

  3. #3
    Registered User
    Join Date
    10-30-2008
    Location
    ID
    Posts
    6
    Didn't work. Gave me the #DIV/0!

  4. #4
    Registered User
    Join Date
    10-30-2008
    Location
    ID
    Posts
    6
    never mind. it did work

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    If you only want to exclude blanks (i.e., include actual zero values), then a minor tweak for DLL's formula:

    =AVERAGE(IF(MOD(ROW(D6:D96) - ROW(D6), 3) = 0, IF(D6:D96 <> "", D6:D96) ) )

    As before, an array formula -- MUST be confirmed with Ctrl+Shift+Enter.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    10-30-2008
    Location
    ID
    Posts
    6
    Is there any way to make it a running average? Trying to do that is what gave me the divide by zero error. Thanks

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677
    If you want a running average in an adjacent column then try this formula in E6

    =IF(D6="","",IF(MOD(ROWS(D$6:D6)-1,3),"",AVERAGE(IF(MOD(ROW(D$6:D6)-ROW(D6),3)=0,IF(D$6:D6>0,D$6:D6)))))

    confirm with CTRL+SHIFT+ENTER and copy down column

    As shg says, that will exclude zeroes from the average, if you only want to exclude blanks cells, not zeroes, change to

    =IF(D6="","",IF(MOD(ROWS(D$6:D6)-1,3),"",AVERAGE(IF(MOD(ROW(D$6:D6)-ROW(D6),3)=0,IF(D$6:D6<>"",D$6:D6)))))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1