Averaging using a start and end cell reference/address

Danny318

New Member
Joined
Jun 19, 2007
Messages
44
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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?
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top