Averaging values from a list, based on user input

M

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

NBVC

MWS;504824 said:
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.
 
T

T. Valko

Try something like this:

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

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top