Say I want to enter the formula =AVERAGE(B3:B5) in the cell below (B6:B8).
How do I accomplish this without manually entering the cell reference? If I copy and paste, I get =AVERAGE rather than =AVERAGE(B6:B8) (B4:B6).

I must be able to increase three rows at once. I tried doing a little searching, but a lot of the results are a little jumbled up and don't really make much sense.
Nov 24, 2022 in Others 1,099 views

## 1 answer to this question.

I believe OFFSET makes it simpler, for example, assuming you want the first average in cell D2. Copy down this formula in D2.

```=AVERAGE(OFFSET(B\$3:B\$5,3*(ROWS(D\$2:D2)-1),0))
```

Generally speaking, the B\$3:B\$5 range indicates the first range you want to average, 3 is the number of rows to increment each time, and D2 is the start cell. Any or all of those can be altered, though, based on your specific requirements.

• 63,700 points

## How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## How to keep one variable constant with other one changing with row in excel

Use this form: =(B0+4)/\$A\$0 Since you are dragging across ...READ MORE

## How to access entire row by cell address or value in excel?

You may accomplish all of this by ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## Return blank cell only if referred cell is blank, but return aging if date is entered

Try this: =IF(ISBLANK(AC2),"",TODAY()-AC2) The TODAY  function automatically refreshes based ...READ MORE