Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    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

  2. #2
    5 Star Lounger
    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.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, 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 23-Oct-02 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
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, 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 co-ordinates should be changed to suit your purposes.

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

    Regards
    Peter

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    5 Star Lounger
    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    3 Star Lounger
    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!

  9. #9
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •