Calculate average based on user selection
 

Hi

This should be simple, but I don't have an answer. How do I calculate the
average of a range of values based on a user selection. I have drop down
box with the months, which the user selects the month. It should then
calculate the average up to that month (Year to date). Here is the data:

Month Members
Jan 250
Feb 300
Mar 475
Apr 389

So, the user selects Mar then the average from Jan to Mar is 341. The user
makes another selection and selects Feb, then the average from Jan to Feb is
275. So, everytime the user selects the month, the year to date average is
calculated.

Thanks in advance.

William

Calculate average based on user selection
 

Assume your months are in A1:A12 and values in B1:B12, drop down in C1

=AVERAGE(B1:INDEX(B1:B12,MATCH(C1,A1:A12,0)))

Calculate average based on user selection
 

One possible solution:

Use a helper column in which you enter the AVERAGE formulas next to each
monthly value. Then in your user selection area, use Data Validation to
restrict the user's selection to the months you have listed. Lastly, use
VLOOKUP to pull the average based on the month selected.

So, assuming your data starts in cell A2 and uses one row for each month
with the YTD average formula in column C:

Data Range
A2:C13

User Selection Cell: A15 (Data -- Validation -- List) Source: =$A$2:$A$13
Result based on user selection cell: B15 (=VLOOKUP(A15,$A$2:$C$13,3,FALSE)

Hope that helps.
--
Elisabeth


"William" wrote:

Hi

This should be simple, but I don't have an answer. How do I calculate the
average of a range of values based on a user selection. I have drop down
box with the months, which the user selects the month. It should then
calculate the average up to that month (Year to date). Here is the data:

Month Members
Jan 250
Feb 300
Mar 475
Apr 389

So, the user selects Mar then the average from Jan to Mar is 341. The user
makes another selection and selects Feb, then the average from Jan to Feb is
275. So, everytime the user selects the month, the year to date average is
calculated.

Thanks in advance.

William