Results 1 to 10 of 10

Thread: Evaluate formula

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Evaluate formula

    Assume I have this data on my worksheet from cell A1 through to B6:

    Code:
    1 a
    2 b
    3 c
    If In cell D1 I type:

    Code:
    =MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)
    
    


    and enter it "normally", I get #N/A.

    However, if I enter it as an ARRAY formula, (using Ctrl+Shift+Enter), I get 1.

    In VBA, if I write:

    Code:
    Dim a
        
        a = Evaluate("=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)")
    
    


    a returns 1.

    So my question is:

    1. Does using Evaluate ALWAYS execute the formula as an array, IF it is an array formula?

    2. What if I wanted it NOT to execute as an array formula but as a "normal" formula?

    Thanks







  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,416
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Evaluate formula

    Hi,

    Evaluate is a smart instruction ... it will evaluate both standard formulas ...as well as array formulas ...

    Hope this will help

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Evaluate formula

    Thanks but how smart is it?

    If a formula returns different values depending how it is entered, how will Evaluate know which one you want?
    Last edited by tiredofit; Aug 28th, 2019 at 08:25 AM.

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,416
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Evaluate formula

    Sorry ... but not sure to understand your question ...

    Obviously ... if you are dealing with changing values ...they should be stored in a variable ...as usual ...

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Evaluate formula

    Quote Originally Posted by James006 View Post
    Sorry ... but not sure to understand your question ...

    Obviously ... if you are dealing with changing values ...they should be stored in a variable ...as usual ...
    I mean this formula returns two different results:

    Code:
    =MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)


    If you entered it "normally", it will return #N/A.

    But if it was entered as an array, it will return 1.

    So what will this return?

    Code:
    Dim a a = Evaluate("=MATCH(1,(A1=A1:A3)*(B1=B1:B3),0)")


    Will it return as if it was entered "normally" ie #N/A or 1?



  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Evaluate formula

    If arrays are involved, the Evaluate function will process the formula as if it were array-entered. There is a proviso for this though... if the function involved cannot process arrays naturally (text functions for example), then array processing will need to be "induced" (probably the wrong term for what is going on, but it works for me) by embedding the function in another function that can process arrays (the IF function for example). For example, put these values in the indicated cells...

    A1: 12A
    A2: 34B
    A3: 56C

    Now execute this code line in the Immediate Window...
    Code:
    Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")
    Doing so will put all 12's in cells B1:B3 because, even though one of its arguments contains an array, the LEFT function cannot process arrays naturally. However, if you wrap the LEFT function inside of an IF function that processes an array, it will work as expected...
    Code:
    Range("B1:B3") = Evaluate("IF(A1:A3="""","""",LEFT(A1:A3,2))")
    This outputs 12 in cell B1, 34 in cell B2 and 56 in cell B3 as one would want.
    Last edited by Rick Rothstein; Sep 2nd, 2019 at 04:40 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Evaluate formula

    Quote Originally Posted by Rick Rothstein View Post
    If arrays are involved, the Evaluate function will process the formula as if it were array-entered. There is a proviso for this though... if the function involved cannot process arrays naturally (text functions for example), then array processing will need to be "induced" (probably the wrong term for what is going on, but it works for me) by embedding the function in another function that can process arrays (the IF function for example). For example, put these values in the indicated cells...

    A1: 12A
    A2: 34B
    A3: 56C

    Now execute this code line in the Immediate Window...
    Code:
    Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")
    Doing so will put all 12's in cells B1:B3 because, even though one of its arguments contains an array, the LEFT function cannot process arrays naturally. However, if you wrap the LEFT function inside of an IF function that processes an array, it will work as expected...
    Code:
    Range("B1:B3") = Evaluate("IF(A1:A3="""","""",LEFT(A1:A3,2))")
    This outputs 12 in cell B1, 34 in cell B2 and 56 in cell B3 as one would want.
    Thanks for your reply.

    Code:
    Code:
    Range("B1:B3") = Evaluate("LEFT(A1:A3,2)")



    When I did this, I got 12,34,56 in cells B1 to B3.

    FYI, I am using Excel 365.


    Last edited by tiredofit; Sep 2nd, 2019 at 05:48 AM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Evaluate formula

    Xl365 now processes Evaluate functions differently so you do not need to use the If function.
    But if you do not use the IF function it will not work for people using 2016 or earlier (not sure about 2019)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Evaluate formula

    Thanks

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Evaluate formula

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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