Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Based on Drop Down Item Selected | Excel Worksheet Functions | |||
start a macro or procedure based on user putting an x in a cell | Excel Discussion (Misc queries) | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions |