Dear members,

I would like to ask you for a problem regarding the efficiency of calculations in some excel worksheets. There are several Tags which are retrieving data through PI Data Link and they are shown in a big size workbook. This excel workbook has several sheets with calculations and links to different Tags, and all the data coming from PI Data Archive, are based on TimeStamps during the whole weekend, every minute, I mean, there are several Tags (columns) and several Timestamps(rows). So, when I try to modify something, or just copy a table, group of rows / columns from the big excel file to another empty or partially empty excel file, it takes me a lot of time. It always appears underneath the excel file a message like “Calculating (8 Threads) = … % And it is recalculating so slowly, even there is nothing to recalculate.

I think my computer is efficient enough to support a lot of calculations (Lenovo – Intel Core i5 8^{th} Gen – 1.70 GHz 1.90 GHz – 8 GB of RAM – 64-bit Operating System, x64-based processor).

I am aware of there are a lot of Timestamps but, is there a way to avoid a recalculation every modification? Because it is drawing out my work every day.

The target of my excel analysis is to do it fully in PI System, through Element Analysis. But realistically, nowadays it is a bit complex to implement the total calculation process, it needs a step by step process, so I need this intermediate step in excel. There is any way to improve the performance?

Any suggestion will be welcome.

Thanks for your support,

Regards,

OMAR.

Hi Omar,

Have you tried changing the calculation settings? When you are building or editing the worksheet you could set it to manual calculation so that Excel will only recalculate when you tell it to do so.

There is some information on this here:

https://support.office.com/en-us/article/change-formula-recalculation-iteration-or-precision-in-excel-73fc7dac-91cf-4d36-86e8-67124f6bcce4

As you mention, you could improve performance by using Asset Analytics to do some of the calculations rather then bringing all the raw data into Excel first. Another factor that can impact performance is the latency between your machine and the PI Server (though I would not think that this was the primary factor).