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, 2022 in Others by Kithuzzz
• 38,010 points
1,099 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, 2022 by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
605 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, 2022 in Others by narikkadan
• 63,700 points
3,233 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, 2022 in Others by narikkadan
• 63,700 points
361 views
0 votes
1 answer

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

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

answered Dec 28, 2022 in Others by narikkadan
• 63,700 points
1,120 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, 2022 in Others by narikkadan
• 63,700 points
705 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, 2022 in Others by narikkadan
• 63,700 points
525 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, 2022 in Others by narikkadan
• 63,700 points
693 views
0 votes
1 answer

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

answered Dec 17, 2022 in Others by narikkadan
• 63,700 points
1,342 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, 2022 in Others by narikkadan
• 63,700 points
1,389 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
3,270 views
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