excel help...!

x in the city

Guys,

been eons since I used excel, how do i get a column to give me the current from a colulm of numbers times a resistive value, and also I need the average of this figure.

I have listed below what I have and what I wish to do.!


time (s)| volts | current = volts x resistor value | average current| power = volts x av current | energy in jules |


I assume the power would be got from the average current times volts?, I would also be interested in calculating jules.

Energy = power x time.

for the time would I use an average figure from the time (s) column? as it would be wayyy too long to isolate a particular value.

sorry if this is confusing!

pljudge321

Okay, this might be a bit hard to explain but ill give it a shot.

Fill in the times and volts in columns a & b, make the first cell in each column the titile of whats in the column.

Add the heading current in column c. In cell C2 press = and then click on cell B2 then press * and then enter the value of the resistor, press enter. Select column C2 and click and hold on the small black box that will appear on the lower right of the cell. Drag this down the column till the end.

For the average you would simply select a cell you want it to be in and press enter =average( , you can then use the mouse to draw a box to select all the values you want an average of.

Can you give us some details on the experiment because I'm slightly confused as to what you are measuring. Also current is voltage divided by resistance.

bnt

I'm attaching a basic spreadsheet I threw together. It's not quite clear from your post exactly what information you have, and what needs to be calculated, so I've assumed that the resistance is fixed and you're measuring the voltage.

When you have the Voltage and the Resistance, you can get the instantaneous Power directly from those (P = V²/R) - you don't have to calculate the current unless you want to. (I = V/R). Then, to get the energy usage over a time period, you'd take E = P x T, as you say, with the assumption that it's constant over that time period. (T must be in Seconds if you want Joules)

One essential skill to know with spreadsheets is how to lock down references using the $ sign. In this example, the Resistance in cell G2 is constant, so whenever I use it in a formula I say $G$2. That way I can copy the formula to another cell, and it still refers to G2 at all times.

x in the city

pljudge321 wrote: »

Okay, this might be a bit hard to explain but ill give it a shot.

Fill in the times and volts in columns a & b, make the first cell in each column the titile of whats in the column.

Add the heading current in column c. In cell C2 press = and then click on cell B2 then press * and then enter the value of the resistor, press enter. Select column C2 and click and hold on the small black box that will appear on the lower right of the cell. Drag this down the column till the end.

For the average you would simply select a cell you want it to be in and press enter =average( , you can then use the mouse to draw a box to select all the values you want an average of.

Can you give us some details on the experiment because I'm slightly confused as to what you are measuring. Also current is voltage divided by resistance.

guys, thanks for the help.

I have got the knack of this now.

I am measuring power consumption of a atmega microcontroller, the levels are small. but its all in the name of research...!

i have a question.

I got code written out that does various 'jobs', i extracted this much data (in volts) into excel to work with, over say x amount of time.

I have calculated the average current during this time (V/R), and got the corresponding power from this.

If I get the average of the time span (s), and multiply this by the power i just did, would this give an accurate energy representation in jules?

bnt

x in the city wrote: »

If I get the average of the time span (s), and multiply this by the power i just did, would this give an accurate energy representation in jules?

The energy used over that time period, yes. I'm in no position to comment on the accuracy - it depends on the accuracy of the input data you get. If the meter is giving you the instantaneous voltage at points in time, you might be missing changes happening in between the readings. If that could be a problem, are you taking readings often enough to capture what's actually happening, or is the meter doing the averaging for you?

x in the city

bnt wrote: »

The energy used over that time period, yes. I'm in no position to comment on the accuracy - it depends on the accuracy of the input data you get. If the meter is giving you the instantaneous voltage at points in time, you might be missing changes happening in between the readings. If that could be a problem, are you taking readings often enough to capture what's actually happening, or is the meter doing the averaging for you?

the data is grabbed from a tektronix scope in real time.

I take a snap shot in excel on the spot and it fires data into columns instantly.
I put a start and stop bit into my code so i grabbed all the important data in between this, so i could get some accurate results.

ie: power used from data activity

DublinDilbert

Effectively you want to calculate (integrate) the area under the curve. You effectively have a Voltage waveform, if you square it and divide by the resistor value you'll have the power consumption waveform in Joules.
P=VI
V=IR
=> P = V^2 / R

For a given power waveform if you take the average value, you are effectively getting the area under the curve, then divide this by the time interval and you have the power consumption in watts.

200 Joules used over 2 Seconds = 100W
200 Joules used over 20 Seconds = 10W

x in the city

DublinDilbert wrote: »

Effectively you want to calculate (integrate) the area under the curve. You effectively have a Voltage waveform, if you square it and divide by the resistor value you'll have the power consumption waveform in Joules.
P=VI
V=IR
=> P = V^2 / R

For a given power waveform if you take the average value, you are effectively getting the area under the curve, then divide this by the time interval and you have the power consumption in watts.

200 Joules used over 2 Seconds = 100W
200 Joules used over 20 Seconds = 10W

i will post up my excel sheet tomorrow and you can have a look? i think (hope!) its right.. i asked a lecturer and he agreed my method should work.

x in the city

Dilbert,

have this attached which is basically what I am doing but on a grander scale!
(I am using a buck voltage from 3.5volts down to 1.5 volts) so a lot ...(lot!) of messy colums


I am plotting so far volts on x (3.5 downwards) versus average power on y axis

the series will vary with frequency (11mhz/5.5mhz...etc).

so i would have different power ratings for different frequencies/voltages

if that makes sense?

I have not done the jules yet, but would do that once I have a few more graphs done, I can do a energy (jules) plot of the lowest and highest voltage ranges I get back or something like that.


*my attachment has volts1 and volts2, volts 2 is just a start and stop bit i have in my code to let me gather up all the (useful) data between these points

i have this called volt_1, the one i am interested in, i get the corresponding current on this with my 1.2R reference resistor on my probe, and likewise the av current and power from these.

I hope this is not too confusing..!!:o