How to use an increment an average formula by more than one row in excel

0 votes
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 in Others by Kithuzzz
• 20,660 points
27 views

1 answer to this question.

0 votes

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.

answered Nov 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

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

answered Oct 24 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

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

answered Nov 17 in Others by narikkadan
• 37,660 points
27 views
0 votes
1 answer

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

answered Nov 24 in Others by narikkadan
• 37,660 points
30 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
119 views
0 votes
1 answer

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

answered Oct 8 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
50 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21 in Others by narikkadan
• 37,660 points
35 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

How to increment row value in an index function in excel?

Try this- =INDEX($F$27:$F$40,COLUMN(A$1)) It will automatically increase the row ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
34 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP