Having problem with a formula to find average every 10 rows

**Options78** · 07-09-2020, 08:39 PM

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

**bebo021999** · 07-09-2020, 10:06 PM

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.