Results 1 to 9 of 9

20021023, 01:30 #1
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Automatic Functions (Excel 2000 SR2)
I frequently need to calculate the same functions for different data. I would like to enter the text in column A and then use the text as the function name.
For example, suppose the data is in B2:B30 and the text average is in A31, and the text min is in A32. I would like B31 to use the text in A31 to create the formula =average(b2:b30) and b32 to be =min(b2:b30).
Thanks

20021023, 08:42 #2
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
It could be coded as a VBA Function
However, if there are only a limited number of possible functions e.g. less than about three  then a simple IF statement will do it.
=IF(A31="Average",AVERAGE($B$2:$B$30),IFA31="Min", MIN($B$2:$B$30),MAX($B$2:$B$30)))
(I used MAX for the case that A31 wasn't "Average" or "Min")
If the above were in B31 it could be copied to B32 and other cells like it.

20021023, 09:07 #3
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
<P ID="edit" class=small>(Edited by j.peter.orourke on 23Oct02 10:07. System slow.. not sending attachments. Tried again!)</P>Hi Cathy
The attached spreadsheet I think does what you asked for? I've named B2:B30 MyData, amend to suit. I've used Data, Validation, List to limit what can be put in Cells A31 and A32. (Min, Max, Average, Sum). I've then used a nested IF function in B31 to B32 to calculate what has been selected. So, you end with something like:
=IF(A31="Min",MIN(MyData),IF(A31="Max",MAX(MyData) ,IF(A31="Average",AVERAGE(MyData),IF(A31="Sum",SUM (MyData),0))))
There may be a neater solution..... Hope this gives you some ideas.
Regards
Peter

20021023, 09:40 #4
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
In fact, here's another way of doing it.. With this solution you calculate ALL the possible functions results in another area of the sheet and just display the result selected. If added the list of functions in F8:I8 and then calculated the results for each in F9:I9. F8:18 is a named range 'lstFunctions'. D11 is where you select the actual result you want, this uses Data, Validation, List ='lstFunctions' to limit valid choices. In cell E11 we then get the result we wanted by looking at where in the list 'lstFunctions' the chosen calculation, MIN, MAX, AVERAGE, SUM appears and then looking in the cell below that to get the result.
The formula to do this is:
=IF(ISERROR(MATCH(D11,lstFunctions,0)),0,INDIRECT( ADDRESS(9,(MATCH(D11,lstFunctions,0)1)+6)))
The ADDRESS(9 AND the +6 are the key parts. The 9 is the ROW, 9 in this case. The +6 is the column, 6 being F. $F$9 is where our first result is parked =MIN(MyData). The MATCH(D11,lstFunctions) bit will return a number between 1 and 4, depending upon the value in D11. We need to take 1 away from this and then add it to the 6 which will give us the correct column, F, G, H or I. Obviously these coordinates should be changed to suit your purposes.
This approach could be extended for a fairly lengthy list of functions.
Regards
Peter

20021023, 10:47 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Automatic Functions (Excel 2000 SR2)
I think the most straightforard method is to use SUBTOTAL
Create a list of the statfunction names (in this order)
AVERAGE
COUNT
COUNTA
MAX
MIN
PRODUCT
STDEV
STDEVP
SUM
VAR
VARP
Create a name for the list (eg FuncNum).
Then:
=SUBTOTAL(MATCH(A31,FuncNum,0),$B$2:$B$30)
will give you the average (if a31 has text average)
=SUBTOTAL(MATCH(A32,FuncNum,0),$B$2:$B$30)
Will give you the min(if a32 has text min)
Steve

20021023, 20:35 #6
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
<img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
I always wondered what SUBTOTAL was supposed to do.

20021023, 22:33 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Automatic Functions (Excel 2000 SR2)
Subtotal works even BETTER with Autofilter. It only calculates the VISIBLE rows!
You can do all the stat functions (average min/max, std) normally to get ALL the data, then use SUBTOTAL to get ONLY the filtered data!
I will display the info for the total in 1 row, the subtotal in another row at the top above the headers and then use freeze panes below the header (headers and Stats always showing at top). Filter on a customer, and get their stats and the overall stats!
Steve

20021024, 12:53 #8
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
Subtotal works great. I can put all kinds of functions. My next question was going to be about changing the rows that I want to calculate easily. The autofilter tip is exactly what I needed.
Thanks for all your help. The Excel people in Woody's Lounge are the BEST!

20021025, 00:06 #9
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Automatic Functions (Excel 2000 SR2)
Subtotal is great for accessing only visble rows, see This Post