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?

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?

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?

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 -