Automate

 View Only

  • 1.  Limition formulas Excel

    Posted 09-26-2024 09:23

    Dear all,

    As I am struggeling with some formulas in a Query, I am wondering, if there are any limitations of Excel formulas?
    For example, I'd like to find the minimal value (MINIFS) from a list in another tab. After validating the formula I get the following messages. I remember similar issues with the formula XLOOKUP too, which is quite new.
    Any thoughts or insights one could share? Thank you very much.



    ------------------------------
    Maik Wienkötter
    Process Manager Material Master Data
    ZWILLING J. A. Henckels AG
    ------------------------------


  • 2.  RE: Limition formulas Excel

    Employee
    Posted 09-26-2024 14:47

    Hi Maik,

    There are limitations, as what you are seeing in Studio is an Excel "emulator", if you will, not full-blown Excel embedded in Studio

    It supports most Excel formulas but not all.

    Also why in Studio you might see #NAME?, but if you publish and run from Excel, the real value will be displayed.

    Best Regards,

    Sigrid



    ------------------------------
    Sigrid Kok
    Precisely Software Inc.
    ------------------------------



  • 3.  RE: Limition formulas Excel

    Posted 10-01-2024 07:37

    Hi Sigrid,

    Thank you very much for your reply and the explanations regarding Excel view in Automate Studio.

    I have followed your advice, published the script and executed it. Unfortunately, the same error ocurred as mentioned in the screenshots below.

    Do you know, if there is an issue in particular with formula "MINIFS"?

    Kind regards

    Maik



    ------------------------------
    Maik Wienkötter
    Process Manager Material Master Data
    ZWILLING J. A. Henckels AG
    ------------------------------

    Original Message


  • 4.  RE: Limition formulas Excel

    Employee
    Posted 10-14-2024 15:06
    Edited by Sigrid Kok 10-14-2024 15:50

    Hi Mark

    Sorry for the long delay.

    I created the simplest of tests.  If you run from the Excel add-in - not Studio (and likely not run on server) - it should be fine:

    It also works in Query, but note my criteria was hard coded

     but you could also use something like: =MINIFS(C2:C999,A2:A999,A2)

    Best Regards,

    Sigrid



    ------------------------------
    Sigrid Kok
    Precisely Software Inc.
    ------------------------------

    Original Message