Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using text to create functions (Excel 2000)

    I need to create some variable functions depending on specified conditions.

    For example, In B1 I have an IF function that returns a range name.

    In A2 I have an IF function that returns a function name such as MIN, MAX, or AVERAGE.

    In A3 I have another IF function that returns a different function such as RANGE, STDEV, etc.

    In B2, I'd like to evaluate =A2(B1) which might be =MIN(name). In B3 I'd like to evaluate =A3(B1).

    I've tried concatenating. I've tried using the VALUE FUNCTION, but I can't get whatever's in A2 and A3 to be recognized as a worksheet function name.

    Any suggestions?

    Cathy

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using text to create functions (Excel 2000)

    In VBA there is an Evaluate method which allows you to do this sort of stuff, but I don't know of any similar function in Excel. The closest thing is the Indirect function. In your case, you can back up one step to the If and use it there:
    <pre>=IF($A$5="X",MIN(INDIRECT($B$1)),IF($A$6="X", MAX(INDIRECT($B$1)),
    AVERAGE(INDIRECT($B$1))))</pre>


    It's a little ugly, but it gets the job done. If you really want a user function to evaluate a string, just reply back and one of us will give you some code. It would just be a one liner. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using text to create functions (Excel 2000)

    Catherine indicated in email that she would like the VBA code. I suspect it will get her into trouble, <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> but here goes:

    The simplest user function would only take one cell as an argument. So, if you had sin(pi()/2) in cell A1 and =usrEvaluate1(A1) in A2 then the following user-function would put a 1 in A2:
    <pre>
    Public Function usrEvaluate1(s As String)
    usrEvaluate1 = Application.Evaluate(s)
    End Function
    </pre>
    However, you may as well make it more robust, so that it works like the Sum function
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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