Analysis Office: How to work with Excel formulas

In Analysis Office 2.2 a new feature was published. You can now use your own Excel formulas in a Crosstab. First you have to add a row or column in your Crosstab. The table design is a feature since Analysis Office 2.0.

 

You can add your formula.

Excel formula in Analysis Office Crosstab
Excel formula in Analysis Office Crosstab

Then you can drag your formula down to the end of the Crosstab.

Expand Excel formula in Analysis Office Crosstab
Expand Excel formula in Analysis Office Crosstab

When you now add a new characteristic to the rows, you will see the Excel formula will be automatically adjusts.

Expand Excel formula in Analysis Office Crosstab
Expand Excel formula in Analysis Office Crosstab

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 5th edit
The book SAP Analysis Office - The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.
59,99 €
Analysis Office - The Comprehensive Guide 4th edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.7 and contains 299 Pages.
39,99 €
Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
29,99 €

Write a comment

Comments: 6
  • #1

    Iwan Santoso (Friday, 15 March 2019 21:52)

    Hi Tobias,

    Have you come across situation where you move your AO workbook to Test environment via transport and get the formula to be available in the target system? my situation is that in target system, the formula is left blank and it works fine in development environment.

    Regards,
    Iwan

  • #2

    Tobias (Saturday, 16 March 2019 10:52)

    Hi Iwan,
    is your workbook stored in BW or on a BIP? I have workbooks on a BIP and no problem with any formulas. The created columns with Table Design are also visible in your target system?

    Best regards,
    Tobias

  • #3

    DAmian (Wednesday, 27 March 2019 22:03)

    Hello tobias:

    we are trying to write a formula in SAP IP but for some reason the formula dissappears and the value only remains. Any suggestions woulr be welcome.

    Thanks!

  • #4

    Tobias (Thursday, 28 March 2019 10:16)

    Hi Damian,

    can you send me your example via mail? On which version are you? It seems like a bug.

    Best regards,
    Tobias

  • #5

    Othmar (Thursday, 20 August 2020 19:09)

    From experience:
    1. Workbook must be connected with the Datasource with activated updating (should be obvious, but can be a source, why things don't work as expected.)
    2. If there are too many values selected, the copying down the formulas does not work correctly; therefore select first less data, write your formulas and then reupdate the Workbook with all the data needed..
    3. If Excel-Formula is not supported by AO, the Formula disappears - or at least just gives N/A back. (Easy formulas like Sum, Medium; Add / Minus; Multiplication / Division work fine. Even referencing to cells out of the AO-Workbook. Not possible: vlookup, if - then, etc.).

    @Tobias: A list of supported formulas/functions would be appreciated if available. (Could not yet find something)

  • #6

    Tobias (Monday, 31 August 2020 09:55)

    Hi Othmar,

    in the ebook you find a more information about formulas. What exactly are you looking for? Normally you can use all Excel formulas. If you like we can exchange via mail.

    Best regards,

    Tobias