Averaging using a start and end cell reference/address

Danny318

New Member
Trying to make a formula that allows me to select a start and end cell reference and average the values in the selected range. For instance:

Week 1 25
Week 2 34
Week 3 32
Week 4 12
Week 5 10
Week 6 34
Week 7 22
Week 8 21

Start Week: Week 2
End Week: Week 6

Average week 2 thru week 6 (should get 24.4)

I must be able to change the start/end week parameters.

Part 2: Say I want to add the selected range but EXCLUDE week 3. What's the magic formula???

Any ideas?

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

barry houdini

MrExcel MVP
Hello Danny,

for part 1.....
Book1
ABCD
1
2Week 125
3Week 234
4Week 332
5Week 412
6Week 510
7Week 634
8Week 722
9Week 821
10
11Start WeekWeek 2
12End WeekWeek 6
13Average24.4
14
Sheet2

formula in B13

=AVERAGE(INDEX(B2:B9,MATCH(B11,A2:A9,0)):INDEX(B2:B9,MATCH(B12,A2:A9,0)))

for part 2 .....

Do you want to sum the whole range excluding specific week(s), e.g. week 3 or do you want to sum between certain weeks again, e.g. 2 and 6, and exclude week 3 from that total?

Danny318

New Member
Sorry, I meant to say average, not sum.

Same deal as before except I want to say, average week 2-6 but don't include week 4.

I tried the first formula and it worked like a charm. Thank you so much!!!

Replies
23
Views
619
Replies
4
Views
300
Replies
3
Views
341
Replies
1
Views
446
Replies
23
Views
948

1,191,040
Messages
5,984,295
Members
439,882
Latest member
gerdc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back