+ Reply to Thread
Results 1 to 3 of 3

Having problem with a formula to find average every 10 rows

  1. #1
    Registered User
    Join Date
    06-10-2020
    Location
    Toronto
    MS-Off Ver
    2007
    Posts
    13

    Having problem with a formula to find average every 10 rows

    I have a spreadsheet with two tabs that keeps wins information for cases I work on for my job. The "Winners" tab has all the info and the "Summary" tab summarizes it all. Within the "Winners" tab, I have it broken up by a days, and each day has 10 lines attributed to it. For example, day 1 starts at the top from line 2 and goes until line 11. Day 3 from line 12 to 21, Day 3 from line 22 to 31 and so on until Day 50 which ranges from line 492 to 501. I would like to find the average for each day's wins (in dollars), which is found in Column T (so every 10 lines starting from T2), but I would like to have it displayed in the "Summary" tab (instead of the "Winners" tab where the actual information is located). I managed to find the following formula:

    =AVERAGE(OFFSET('Winners'!T2:T11,(ROW()-ROW(Summary!H2))*10,,10,))

    This formula gives me the correct figure for the first 10 rows (day 1), but then when I drag it down to the subsequent cells beneath it simply repeats the same answer for every cell. Is there anything wrong with my formula that is causing it to calculate the same day 1 answer over and over again instead of picking up the next 10 day batch? Also, certain days don't have a full 10 lines worth of data, meaning there's some empty cells as you go down the column, would that contribute to the problem?

    Thanks in advance

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,264

    Re: Having problem with a formula to find average every 10 rows

    It works for me in my samle attached.

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    You can adapt it to your file to see if it works. If not, try to upload a sample.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-10-2020
    Location
    Toronto
    MS-Off Ver
    2007
    Posts
    13

    Re: Having problem with a formula to find average every 10 rows

    It worked, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  2. Replies: 3
    Last Post: 08-01-2013, 04:33 PM
  3. Replies: 7
    Last Post: 03-25-2006, 07:00 PM
  4. Replies: 7
    Last Post: 03-25-2006, 07:00 PM
  5. Formula to find average of field for all rows that contain another field
    By John in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-25-2006, 07:00 PM
  6. Replies: 7
    Last Post: 03-25-2006, 07:00 PM
  7. Replies: 7
    Last Post: 03-25-2006, 07:00 PM

Tags for this Thread

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