Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  Ability to add VLOOKUP and SUMIFS formulas to PAX that work once uploaded to PAW

    Posted Mon February 07, 2022 05:22 PM
    Hi Everyone,

    I am working on a PAW budgeting tool that allows users to select their region and department to be able to see a resulting budget increase in a certain account based on a "SUMIFS" from a hidden/password protected tab within the workbook, based off those PAW selected roll-ups (region/department). Within the Excel PAX add-in, the SUMIFS work and pull back a value. However, once I uploaded that template to PAW, the SUMIFS formula (based off the PAW selected roll-ups (region/department) auto-resulted in a "#NAME?" error.​

    We want to disseminate this information via PAW through this template to keep unrelated budget advice from users who don't need to use it (i.e. utilizing the PAW security) to keep unrelated regions/departments information confidential.

    Does PAW allow for these "smarter" formulas such as VLOOKUPS and SUMIFS? I know PAW allows for simple formulas that don't error out (sums, divisions & percent calculations), but would really like to know if there's a workaround for these more advanced formulas.

    ------------------------------
    Marisol Hobbs
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Ability to add VLOOKUP and SUMIFS formulas to PAX that work once uploaded to PAW

    Posted Tue February 08, 2022 12:20 AM
    Edited by System Admin Fri January 20, 2023 04:30 PM

    Hi Marisol,

    Viewing an Excel sheet through PAW leverages Planning Analytics Spreadsheet Services (old TM1 Web).
    Not all Excel formulas are supported. SUMIFS is one that is not. Have a looks at unsupported functions here

    You could probably work around this with SUM and nested IFs in an array formula e.g.



    If you have not used array formulas before, they have the braces on around the formula and effectively evaluate each cell to get an answer before performing the next function. In the above, the cells are checked to match then summed.

    You also need to Ctrl+Shift+Enter to enter an array formula.

    Hope this helps.



    ------------------------------
    George Tonkin
    ------------------------------

    Original Message


  • 3.  RE: Ability to add VLOOKUP and SUMIFS formulas to PAX that work once uploaded to PAW

    Posted Tue February 08, 2022 04:24 AM
    Expanding on George's post, as well as a list of unsupported excel functions, there is a list of supported excel functions here 
    Supported Microsoft Excel functions - TM1 Web - IBM Documentation

    The other "smarter" formula you mention, Vlookup is a supported excel function and works with Planning Analytics Spreadsheet Service.

    ------------------------------
    Simon Saul
    ------------------------------

    Original Message


  • 4.  RE: Ability to add VLOOKUP and SUMIFS formulas to PAX that work once uploaded to PAW

    Posted Tue February 08, 2022 05:10 AM
    Marisol,

    Apologies in advance, if already considered.
    As an alternative approach, have you explored 
    - creating / using existing supporting TM1 cubes to manage the user selections as a replacement to vlookup in the spreadsheet,
    - TM1 pick lists to control user selections are valid,
    - hierarchies or alternate dimensions for sumifs  and
    - dynamic reports using MDX to control the rows presented to the user?.
    Granted this may sound a little complex and a little more time consuming, but will then provide you with the advantage of controlling behavior centrally and re-using in other templates cutting down on and manual updates to multiple workbooks that may result in the previous method.


    ------------------------------
    Raj Kerai
    ------------------------------

    Original Message


Related Content