Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula (Excel 2000/SP3)

    Last year someone here in the Lounge helped me get a spreadsheet working and one of the formulas used the
    H Lewton

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula (Excel 2000/SP3)

    Hi,

    The 'EVAL' function may well be a user-defined function, in which case you should be able to find it by starting the Visual Basic Editor (Alt-F11) and looking in the module(s) attached to the workbook in which the function resides. You'll need to have the 'Project Explorer' visible in the Visual Basic Editor. If it isn't already visible, you'd enable it via Ctrl-R from within the Visual Basic Editor.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (Excel 2000/SP3)

    The EVAL function is no normal worksheet function. EVALUATE is an ancient XL4 macro function, which can be used in defined names.

    What your function seems to (try to) do is to pick up the list of numers delimited by returns, replace the returns with commas and add a "{" in fromt and a "}" at the back, thus creating an array of values.

    So the cell looks like this:

    1
    2
    3

    and the formula's result looks like this:

    {1,2,3}

    If your formula column is -say- column K and your numbers are in column H:

    - select the cell in column K (lets assume K5)
    - Insert, name, define
    Name: EVAL
    RefersTo:
    =EVALUATE("{" & SUBSTITUTE(H5,CHAR(10),",") & "}")

    Now use EVAL in your formula like this:

    (A5*$B$1-SUM(EVAL)))-I5
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula (Excel 2000/SP3)

    macropod,

    Thanks but he already looked for it that way and so did I and it doesn't appear to be there. I just can't remember how it came about. I'll forward this information though.

    Thanks again.
    H Lewton

  5. #5
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula (Excel 2000/SP3)

    pieterse,

    Thanks. I will forward this information to my friend and see if that helps him, thus helping out my spreadsheet.
    H Lewton

  6. #6
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula (Excel 2000/SP3)

    Found it. "Eval" is part of an Excel add-in called "morefunc"

    Thanks all.
    H Lewton

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula (Excel 2000/SP3)

    The sinner must have been me. Here more info on morefunc:

    Download MoreFunc
    Function Descriptions
    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula (Excel 2000/SP3)

    Aladin,

    No sin involved. In fact it is a rather great formula that was developed around that "eval" function. I just wanted a little enhancement and the person I sent the file to did not have the "morefunc" on his machine until by some stroke of luck I remembered that "morefunc" and sent it to him. After he loaded it as an add-in it took him about 30 seconds to come up with what I needed.

    Thanks.
    H Lewton

Posting Permissions

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