Plotting Data and performing integration using a spreadsheet program

I haven't written a post in ages...my apologies but I rarely have time these days or anything of interest to post about...my work has taken over. I hope to get back to writing and doing more things for myself but I have no idea when.  I suspect I will have to make myself get back into blogging.  It is useful as an aide memoire to myself if nothing else.  

A colleague asked me to plot some sensor data for him in a spreadsheet program and perform some analysis on it and to be completely honest I had forgotten how.

So I did some quick research and here is an example of what I did so that I can refer to it next time...It may be useful to someone else so I'm writing it up as a post.

Sensor data is collected in various ways but is normally saved to a text file in comma separated values.  

Here is an example of some comma separated value data:

sample,measurement1,measurement2
0,0,0
1,1,1.12
2,2,2.05
3,3,3.01
4,4,3.95
5,5,5.05
6,6,6.02
7,7,7.08
8,8,7.99
9,9,9.04
10,10,10.02

I made the data up as I cannot share the actual data that was discussed.

The first column is the sample No.

The second column is the current measurement in mA

The third column is the voltage measurement in volts

In case anyone is wondering these are supposed to be measurements from a simple circuit to find an unknown resistance.

A constant current power supply has been used to supply energy to an unknown resistor;  The voltage and current in the circuit were measured and recorded and the results are in the csv text file as shown above.

Here is the circuit:

Here is the tabulated measurement data:

Here is the data plotted as a line graph:

We can calculate the resistance and plot that as well...

So the Resistance (from Ohms Law) is equal to (measured volts / supplied current) * 1000

I have added a column to the table:

We can now plot this data as well if we wanted:

If we like we can get the spreadsheet program to compute the mean average resistance:

For completeness we can calculate the mean average by performing an average calculation:

The value is 1016.58 Ohms - I wish LibreOffice Calc gave me a way of easily displaying the value on the graph...I used the AVERAGE function to calculate it.

Returning to the first graph we can also use the spreadsheet program (Libre Office Calc) although Excel also has this functionality; to calculate the power consumed.

This is achieved by adding a polynomial trendline to the graph in question and then from the trendline performing integration to calculate the area under the curve which is equal to the power consumed in Watts.  Helpfully the trendline function displays the integral for us!  It has been a long time since I had to use integration and possibly the only time I have actually ever had a practical reason to apply it.  My university education wasn't a waste!

 

The function has been found to be: 

If we integrate this we have:

If we apply limits we have:

 If we then calculate out the integral applying the limits we get:

Which gives a value of 40.46...

We need to account for the fact that the current was in milli-amps:

Power (Watts) = Amps * Volts

so we need to divide our solution by 1000 giving:

Power (Watts) = 0.04

A simple check using the tabulated data finds this to be correct:

Now I'm aware in this example we could have used the (base * height) / 2 method to achieve the same result...however when we have a graph that isn't linear (straight line) as the example then the integration method is the best solution.

There is also the trapezoidal method which could be performed on the data itself but that is for another post.

I may yet write a python script which reads in the CSV data, performs the above plots and then performs the integration for me...It may be a useful exercise but that is also for another post!

That's all for now...take care - Langster!