Automatic Functions (Excel 2000 SR2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

(Edited by j.peter.orourke on 23-Oct-02 10:07. System slow.. not sending attachments. Tried again!)

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

Reply | Quote

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 co-ordinates should be changed to suit your purposes.

This approach could be extended for a fairly lengthy list of functions.

Regards
Peter

Reply | Quote

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

Reply | Quote

I always wondered what SUBTOTAL was supposed to do.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

Subtotal is great for accessing only visble rows, see This Post

Reply | Quote