How to totalize the values in Excel ?

I have downloaded the instantaneous flow (l/sec) every minute from the historical log file of Wonderware HMI.

I now want to see if it is possible to totalize the values, so I can get the average flow per day or flow per hour.


Could some please throw some light on this.

Would very much appreciate you intervention.

I have attached the txt file for review.


Thank you.

Would appreciate if someone gave me some help on the above issue. Is it that the issue cannot be addresse ??

Well, this isn't exactly a PLC question. I assume you want to know how to Total and average the values in Excel. It would be better to Post your Excel Spreadsheet so we could try out a few things.

What have you tried to do yourself?

Stu....

Took a quick look at your raw data.

I am only going to talk about the data in rows 1 thru 101. Column A is date B is time of day and C is the flow rate ie lpm.

ASSUMING we are looking at just 1 thru 101 and in Excel
Stick a few extra rows at the top of sheet to put your calcs in.
At A1 enter word TOTAL At B1 enter the "command" =SUM(C1..C101)
Now copy that and change SUM to COUNT and correct C2 and C102 to 1 and 101.

Divide total by count and you have average.

Sign up for an Excel class - if you are handling large sets of data similar to this you NEED this capability - will save you hours.

Dan Bentler

Totalized Flow....

Guys, Thank you for the response.

Just for your information, I know to use excel well and really don't need much of classes.

The case that I have put forward is not about just doing average, sum and count.

It about how to integrate the data that I have given.
If you just do sum and averages that is not the totalized flow.

To get the exact flow, in a day or hour, you need to INTEGRATE the data.


I don't know how to do that exactly and that's the reason I posted the question. It's a complex equation.

Anyway, thank you for trying to help out.

Are you wanting real time averages, totals ?

To get the exact flow, in a day or hour, you need to INTEGRATE the data.

Click to expand...

Only a Math professor would say that. No type of integration is going to be more accurate than using the actual data.

For the rest of us, finding an integral is simply a mathematical way to find the area underneath a curved line. You have the line, you have a series of data points. To get an average flow as you requested, say for one hour (60 minutes), for the 60 minute period that you are interested in, simply multiply each 1-minute (L/Second) flow data by 60 to get the Liters per minute for that 1-minute period, then add up the 60 data points, then call it "XX Liters per hour".

You should understand that the above method is going to be as accurate as any other mehtod, simply because your data points are only for every 60 seconds, so you must assume that the rate did not change during the other 59 seconds. The real rate probably did change, and you don't have data for that, so all you can get is an "average". No fancy math integration is going to change that fact.

Here is the calculated Liters per Hour and Liter per Day. The ZIP file is an Excel *.XLS file.

There is a large gap in the data starting on 10/15/2011, Time 23:31 (2-day weekend non-running days). Because of that, a true mathematical integration over the complete data period will give a false impression that the average flow rate per hour is much less. Using the Excel function "COUNTIF" filters out the zero data points, giving the averages for the time the process was actually running. The Average Calculated Liters per Day is skewed because the beginning and ending days are not complete 24-hour periods.

Average Liters/ Hour for the time actually running = 2012271.32

You will need to set up some calculations in the PLC
add values for an Hour store that in an 'hourlog'
Calculate the avg for that hour - do the same for the day.
Thes send that data inconjunction with your existing log function.

Thank you...

Very much appreciate the support. It has been really helpful.

not sure - which PLC r u using