Excel file - formulas not working

[Mik_J]

Hello all,

so I'm inserting xlsx file to a confluence page and all complicated formulas which I've done are not working. In excel file it looks like this: (there is as well conditional formatting)

But in confluence page it's not working if formula is complicated)

File is edited via Atlassian compaion and it is needed to read that result from cells to avoid update this table every time by hands.

[Katerina Kovriga {Stiltsoft}]

Hi @Mik_J ,

We can suggest trying our app - Table Filter and Charts for Confluence, maybe you've already been using it in your company for filtration and data aggregation.

The add-on also provides the Table from CSV macro that supports outputting data from a URL (for example, Google Sheets where you can use your formulas) or an attached file in CSV and TSV formats.

The recreated table looks like a regular Confluence table and can be further filtered, aggregated and visualized. All the changes made to the source spreadsheet or attachment are immediately reflected on the page.

By the way, you may also perform calculations right in Confluence with the help of the Table Transformer macro. And here is an example how to set conditional formatting for Confluence tables with this macro.

[Mik_J]

Hey @Katerina Kovriga {Stiltsoft} 

Yes, I've been already using Table Filter and Charts for Confluence. I will make sure 100% if there is a solution for my query and I will return with feedback.

But, why when file is opened via link in preview mode, all is working fine, but when it's added to confluence page it is not?

[Katerina Kovriga {Stiltsoft}]

Maybe it's some kind of a bug - I've checked the Excel macro in our test Cloud instance with simple formulas such as (A2+B2) and a standard predefined color scheme for conditional formatting - it worked for me. But we don't use the Excel macro often especially with complex formulas.

What concerns our app, we are almost ready with the new Spreadsheet macro - it looks like a fully functional Excel sheet right on the Confluence page with all Excel features included. Hope that this macro will help in similar cases.

[Mik_J]

So, I did some checks with that excel file, as some formulas are working good, Except this one's with formula EXACT, as it's not looping through arrays.

Example:

=SUM(IFERROR(IF(EXACT(
                                        IFS($K$10:$K$504=IF(B5="Not Defined";"";B5); ROW($K$10:$K$504);
                                              ISBLANK($K$10:$K$504)=TRUE; "blankk");
                                         IF(ISBLANK($F$10:$F$504)=FALSE; ROW($F$10:$F$504))
)=TRUE;1;0);0))

[Katerina Kovriga {Stiltsoft}]

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view&edit modes.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.