Free Yourself from Excel Manual Calculation Mode While Handing PI Data

By John Beath | Excel Tips

Jul 05

We frequently work with data from historian systems such as PI (by OSIsoft) or other external sources like GREET (Argonne National Laboratory fuel and transportation model) that are designed to connect to a DCS or to another complex data source.  Unfortunately, people move around and they aren’t always connected to the data source.  Further, it may be desirable for data to be frozen for various reasons (such as to retain the integrity of a report submitted to an agency).  Using a copy/paste approach alone results in an inability to refresh the data without some complex manipulation and risk of errors.

However, JBE has developed a unique solution to this problem using (of all things) a circular reference associated with a switch cell. 

Our thanks to Allen Wyatt’s Excel Tips web site ( for the idea, which we adapted to this specific situation.  The formula you would use takes the form:

=IF(B5=”OFF”, B16, IF(B5=”RESET”, 0 , IF(B5=”FROZEN”,D5,PITimeDat(WWTP!$C$5,WWTP!$B12,”interpolated”))))

where cell B5 contains the text for the switch that turns the data pull on or off.  The cell where this formula resides is actually B16, setting up the circular reference situation (and hence no change to data) for an “OFF” setting.

It takes some effort to wrap this around existing data formulas, but if you work carefully with absolute and relative addresses, you can adapt it to an existing spreadsheet.  As a result, you can work freely with formulas without having to be in manual calculation mode.  One final tip – we suggest that you place all external connection formulas on the same worksheet to make it clear where these are and to be confident as to whether they are switched on or off.

PI freeze infographic

About the Author

Read John's full bio here:

Leave a Comment:

Leave a Comment: