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.

Try

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




--
Regards,

Peo Sjoblom



"MWS" wrote in message
...
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 something like this:

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

Biff

"MWS" wrote in message
...
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!!!!