Averaging using a start and end cell reference/address

[Danny318]

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?

 

[barry houdini]

Hello Danny,

for part 1.....

Book1
	A	B	C	D
1
2	Week 1	25
3	Week 2	34
4	Week 3	32
5	Week 4	12
6	Week 5	10
7	Week 6	34
8	Week 7	22
9	Week 8	21
10
11	Start Week	Week 2
12	End Week	Week 6
13	Average	24.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]

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!!!