How to increment by 35 rows in an Excel formula using fill handle

Question

In the formula for a cell, how to advance the cell row number by 35 rows using the fill handle? For example, in R1, I have the equation is =AVERAGE(L39:L74) R2=AVERAGE(L75:L110), R3=AVERAGE(L111:L146), etc using the fill handle.

Answer 1

Hi @Unterman, Nathan,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, your first range is L39:L74 and you want to move down the row by increasing the by 36 rows (start at L39, next range at L75). You can achieve that by using the OFFSET formula like Q&A Assist suggested in the previous reply. However, the suggested formula is only correct for R1 and the other R2, R3 and so on still uses the range of R1 to calculate.

I suggest you can try using this formula in cell R1 to see if it meets your request:

=AVERAGE(OFFSET($L$39,(ROWS($R$1:R1)-1)*36,0,36,1))

I hope this helps you move forward smoothly. If you have more questions or need further clarification, feel free to leave a comment here and I’ll be happy to continue supporting you.

Thank you for your patience and understanding, and I look forward to assisting you further.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It took a bit to prove the second answer correct, but it agrees with spot check manual calculations. Although I do not understand all aspects of the command, it has helped analyze 4.5 GBy of data. Thank-you.

The first answer was spurious.

Answer 3

To increment by 35 rows in an Excel formula using the fill handle, you can utilize a combination of the OFFSET function or a structured approach with a helper column. However, Excel does not natively support filling with a fixed increment like 35 rows directly using the fill handle. Here’s a method you can use:

1. Use the OFFSET Function: Instead of directly using AVERAGE(L39:L74), you can modify your formula to use OFFSET. For example, in cell R1, you can write: =AVERAGE(OFFSET(L$39, (ROW()-1)*35, 0, 36, 1)) This formula calculates the average of the range starting from L39 and offsets it down by 35 rows for each subsequent row you fill down.
2. Drag the Fill Handle: After entering the formula in R1, click on the fill handle (the small square at the bottom-right corner of the cell) and drag it down to fill the subsequent rows. Each row will automatically adjust the range to average the next set of 36 rows, incrementing by 35 rows each time.

This method allows you to achieve the desired increment without manually adjusting each formula.

---

References:

• Programmatically automatically fill ranges with incrementally changing data