Averaging values from a list, based on user input

[MWS]

I need to have users input a starting week number in cell A1 and an ending
week number in cell B1. In cell C1, I need to compute the average volume from
the first week, based on the variable the user input, through the last week,
also based on user input.

If the user values "2" in A1, and "4" in B1, via VLOOKUP(A1,D1:E9,2), 30 is
returned, which resides in cell E3. Via VLOOKUP(B1,D1:E9,2), 50 is returned,
which resides in cell E5.

Column D Column E
Row 1 Week Volume
Row 2 1 20
Row 3 2 30
Row 4 3 40
Row 5 4 50
Row 6 5 60
Row 7 6 70
Row 8 7 80
Row 9 8 90

Need: To average the values within the range E3:E5

I tried the following: AVERAGE(VLOOKUP(A1,D1:E9,2),VLOOKUP(B1,D1:E9,2)), but
it correctly averages the two returned numbers, but I need it to average the
identified range of numbers.

Any and all help would be appreciated. thanks in advance!!!!

 

[NBVC]

I need to have users input a starting week number in cell A1 and an
ending
week number in cell B1. In cell C1, I need to compute the average
volume from
the first week, based on the variable the user input, through the last
week,
also based on user input.

If the user values "2" in A1, and "4" in B1, via VLOOKUP(A1,D1:E9,2),
30 is
returned, which resides in cell E3. Via VLOOKUP(B1,D1:E9,2), 50 is
returned,
which resides in cell E5.

Column D Column E
Row 1 Week Volume
Row 2 1 20
Row 3 2 30
Row 4 3 40
Row 5 4 50
Row 6 5 60
Row 7 6 70
Row 8 7 80
Row 9 8 90

Need: To average the values within the range E3:E5

I tried the following:
AVERAGE(VLOOKUP(A1,D1:E9,2),VLOOKUP(B1,D1:E9,2)), but
it correctly averages the two returned numbers, but I need it to
average the
identified range of numbers.

Any and all help would be appreciated. thanks in advance!!!!

Try:

=Average(If($D$2:$D$9>=A1)*($D$2:$D$9<=B1),$E$2:$E$9)

This formula must be confirmed with CTRL+SHIFT+ENTER not just
ENTER...you'll see {} brackets appear arround the formula.

 

[Peo Sjoblom]

Try

=AVERAGE(INDEX(E2:E9,A1):INDEX(E2:E9,B1))

 

[T. Valko]

Try something like this:

=IF(COUNT(A1:B1)<2,"",AVERAGE(INDEX(E2:E9,A1):INDEX(E2:E9,B1)))

Biff