Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Dependent Worksheet Functions
I am creating a summarising worksheet that evaluates a list of
numbers. The list of numbers is held in a column of adjacent cells called 'ListVal1'. I wish to evaluate the numeric cells in 5 possible ways as determined by the user - the analysis could be any one of SUM, AVERAGE, MIN, MAX or COUNT. The user selects the analytical process by reference to a text value in a related cell - in this case cell 'B11'. The following formula works but is both cumbersome and has reached the limit of nested 'if statements' =IF(B11="","",IF(B11="SUM",SUM(ListVal1),IF(B11="A VERAGE",AVERAGE(ListVal1),IF(B11="MAX",MAX(ListVal 1),IF(B11="MIN",MIN(ListVal1),IF(B11="COUNT",COUNT (ListVal1),"")))))) Does any one have a more flexible (less cumbersome) approach? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Dependent Worksheet Functions
Is this better?
=lookup(B11,{"","SUM","AVERAGE","MAX","MIN","COUNT ";"",SUM(ListVal1),AVERAGE(ListVal1),MAX(ListVal1) ,MIN(ListVal1),COUNT(ListVal1)}) "Stephen Allen" wrote: I am creating a summarising worksheet that evaluates a list of numbers. The list of numbers is held in a column of adjacent cells called 'ListVal1'. I wish to evaluate the numeric cells in 5 possible ways as determined by the user - the analysis could be any one of SUM, AVERAGE, MIN, MAX or COUNT. The user selects the analytical process by reference to a text value in a related cell - in this case cell 'B11'. The following formula works but is both cumbersome and has reached the limit of nested 'if statements' =IF(B11="","",IF(B11="SUM",SUM(ListVal1),IF(B11="A VERAGE",AVERAGE(ListVal1),IF(B11="MAX",MAX(ListVal 1),IF(B11="MIN",MIN(ListVal1),IF(B11="COUNT",COUNT (ListVal1),"")))))) Does any one have a more flexible (less cumbersome) approach? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Dependent Worksheet Functions
My recommendation...
First, in B11, set up a data validation (drop down list), for the functions that you are willing to allow the user to do. I then named cell B11 (Insert|Name|Define) as CalcType. Second, in another cell (my example will be B12), set up another drop down list that lists the possible 'ranges' that the person will be able to calculate. I then named B12 as CalcRange. Finally, set up a 2 column table somewhere (my preference is to always have a tab, hidden usually, of just tables). In the first column, I typed the list of possible selections, SUM, AVERAGE, MAX, MIN, COUNT, and in the 2nd column, I typed the formulas as =SUM(INDIRECT(CalcRange)), =AVERAGE(INDIRECT(CalcRange)), =MAX(INDIRECT(CalcRange)), =MIN(INDIRECT(CalcRange)), =COUNT(INDIRECT(CalcRange)). I then named this range as CALC. Then, your formula would be: =IF(CalcType="","",VLOOKUP(CalcType,Calc,2,FALSE)) -- John C "Stephen Allen" wrote: I am creating a summarising worksheet that evaluates a list of numbers. The list of numbers is held in a column of adjacent cells called 'ListVal1'. I wish to evaluate the numeric cells in 5 possible ways as determined by the user - the analysis could be any one of SUM, AVERAGE, MIN, MAX or COUNT. The user selects the analytical process by reference to a text value in a related cell - in this case cell 'B11'. The following formula works but is both cumbersome and has reached the limit of nested 'if statements' =IF(B11="","",IF(B11="SUM",SUM(ListVal1),IF(B11="A VERAGE",AVERAGE(ListVal1),IF(B11="MAX",MAX(ListVal 1),IF(B11="MIN",MIN(ListVal1),IF(B11="COUNT",COUNT (ListVal1),"")))))) Does any one have a more flexible (less cumbersome) approach? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Dependent Worksheet Functions
My thanks to both Joel and John C
I will probably try Joel's approach mainly because I have been totally remiss and never used array formulae before. This is as good a place to start as any. Thanks again Stephen Allen On Jul 22, 2:38*pm, John C <johnc@stateofdenial wrote: My recommendation... First, in B11, set up a data validation (drop down list), for the functions that you are willing to allow the user to do. I then named cell B11 (Insert|Name|Define) as CalcType. Second, in another cell (my example will be B12), set up another drop down list that lists the possible 'ranges' that the person will be able to calculate. I then named B12 as CalcRange. Finally, set up a 2 column table somewhere (my preference is to always have a tab, hidden usually, of just tables). In the first column, I typed the list of possible selections, SUM, AVERAGE, MAX, MIN, COUNT, and in the 2nd column, I typed the formulas as =SUM(INDIRECT(CalcRange)), =AVERAGE(INDIRECT(CalcRange)), =MAX(INDIRECT(CalcRange)), =MIN(INDIRECT(CalcRange)), =COUNT(INDIRECT(CalcRange)). I then named this range as CALC. Then, your formula would be: =IF(CalcType="","",VLOOKUP(CalcType,Calc,2,FALSE)) -- John C "Stephen Allen" wrote: I am creating a summarising worksheet that evaluates a list of numbers. The list of numbers is held in a column of adjacent cells called 'ListVal1'. I wish to evaluate the numeric cells in 5 possible ways as determined by the user - the analysis could be any one of SUM, AVERAGE, MIN, MAX or COUNT. The user selects the analytical process by reference to a text value in a related cell - in this case cell 'B11'. The following formula works but is both cumbersome and has reached the limit of nested 'if statements' =IF(B11="","",IF(B11="SUM",SUM(ListVal1),IF(B11="A VERAGE",AVERAGE(ListVal1)*,IF(B11="MAX",MAX(ListVa l1),IF(B11="MIN",MIN(ListVal1),IF(B11="COUNT",COUN T*(ListVal1),"")))))) Does any one have a more flexible (less cumbersome) approach?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting worksheet dependent on columns drop down list | Excel Discussion (Misc queries) | |||
Return a list dependent upon the selection of a preceeding list | Excel Worksheet Functions | |||
WORKSHEET FUNCTIONS - LIST OF ALL ?? | Excel Worksheet Functions | |||
How do I create a dependent list, to a current list? | Excel Discussion (Misc queries) | |||
How to get the complete list Excel Worksheet Functions | Excel Worksheet Functions |