Morning ladies and gents,

I'm looking to create a spreadsheet in excel to link with PI Sever to calculate the daily average of a particular tag (for several similar tags).

I'm using excel 2007 so I was going to use the PIAdvCalcVal function. I've got a blank spreadsheet with headings for the values I need, dates down the left hand side and would like to create the spreadsheet so that the average daily value for each tag is automatically input into the specific cell associated. Presumably similar coding can be used for each, just changing the tag stamp.

I'm looking to get the spreadsheet to automatically update, i.e. the values to autofill into the spreadsheet but I understand there's a couple of concerns. What I was looking at using was the formula below where the start time and end time could be changed for each subsequent cell.

PIAdvCalcVal(“INSERT SPECIFIC TAGNAME”,” 31-aug-11 00:00:00”,” 31-aug-11 23:59:59”,”average”,”time-weighted”,0,1,1)

This would presumably calculate the average value for the specific tag between midnight and 1 sec prior to midnight and place it in the cell? Is that feasible, does the coding look ok?

Secondly, how would I go about getting the spreadsheet to automatically update? Presumably the spreadsheet will be kept on the h: of a specific computer with a link to PI available (though the spreadsheet will obviously not be open 24/7.

From my understanding this function would be non-volatile, i.e. once the average value has been calculated it has no reason to change - it should stay in it's specific cell until manually deleted by the user.

But how do I get the spreadsheet to link with PI? Upon opening the spreadsheet a day or two later, will it automatically link to the PI Server and calculate the values necessary or will it need 'prompting'?

If my coding is wrong, can any of you suggest how to advance this a.s.a.p? I can't physically check my plan as yet as I don't have the software on this machinem and will have only a short period of time to implement the idea.

Any help would be fantastic!
Many Thanks,
Cameron

OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System.
We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ  or contact OSIsoft vCampus for more details.

As squatty implied it is really, really easy to do. As you have teh dates in the left column (e.g. A2, A3, etc), put your tags across the top row(e.g. B1, C1, etc).

In cell B2 create your formula

=PIAdvCalcVal(“INSERT SPECIFIC TAGNAME”,” 31-aug-11 00:00:00”,” 31-aug-11 23:59:59”,”average”,”time-weighted”,0,1,1)

and with cell references:

=PIAdvCalcVal(B$1, $A2, $A2+1,”average”,”time-weighted”,0,1,1)

Copy this formula to the rest of your table. Each time you recalculate sheet the averages will be recalculated and the results will update - just like other Excel formulas. However as you quite rightly say the historical ones will be static. As the number of values returned increases performance will reduce. Simply copy and Paste Special as values once ina while for the values more than a day old.

If you have the PI Datalink addin and a 'connection' to PI set up then a simple press of F9 should recalcualte for you. Automatic calculation should also work but may require something on the sheet to change (like an =NOW formula) to nudge the auto recalc.

You can also use this simpler formula:
=PICalcVal(B$1,$A2,$A2+1,"average", 1, 0,pi_server)

all the best,

Miles