rate of change formula for battery voltage loss - please help if you can

[blake_mulder]

I have been monitoring the battery voltage drop on my car battery (with the ignition off) with a voltage data logger and the information is in excel format. Is it possible for me to look at the rate of change in excel somehow? I would really like to be able to see if there is a rate of change in the voltage drop or if it is constant.

If you could offer any advice on how to do this I would be grateful.

Blake

[shg]

If you have readings at constant time intervals in A1:A100, in B2 and copy down, =(A2-A1) / T, where T is the difference in time. The result is volts per whatever the unit of T is.

[blake_mulder]

I have the time in column B and the voltage in column D - The logger is set to record 1 sample every 60 seconds but for some reason it still records at odd intervals, eg 65 seconds, 67 seconds, 61 seconds etc - it would appear that as the voltage drops the rate of change slows down so if I could express this rate of change that would be brilliant!

[blake_mulder]

voltage drop screenshot.jpg

Screenshot of some of the data

[shg]

        ---B---- C ---D--- --E---
  615     Time       DCV   V/min 
  616   20:22:54    12.45        
  617   20:24:13    12.46   0.008
  618   20:25:12    12.45  -0.010

E617: =(D617-D616)/(B617-B616)/1440

[blake_mulder]

shg, thankyou so much for your help - can you explain why divide by 1440? I am still logging data so the spreadsheet is growing...is the 1440 the total no of readings? Please forgive my ignorance on this subject

[blake_mulder]

One more question please, if my car wont start when the voltage gets to, say 11.5v, is it possible to predict from the trend of voltage loss, how long it will take (hours/mins or time) for the battery to reach that critical 11.5volts? can I put a formula for this in to the spreadsheet and keep feeding in data as I pull information from the data logger? Thankyou for any help

[Richard Buttrey]

Hi,

You could simply chart the voltages and times, add the trend line (you'd need to decide which trend line best fits your data) to the chart and format it to show the formula for line. From that you could work out the time for the 11.5v measure. Alternatively use the LINEST() function.

However surely temperature will be a variable which affects the voltage loss? Not sure how significant that might be but if there are day/night measurements then temperature swings may be significant and perhaps should be incorporated in your model.

Addendum
Just had a quick look at your data and the plots seem inconclusive with no discernible relationship between time and voltage. The voltage rises and falls over your sample period.

[shg]

1440 is the number of minutes in a day, so the rate of change is in volts per minute.

[shg]

I dunno. If you posted a workbook instead of a picture, maybe.

[blake_mulder]

Oh dear, this is where I come unstuck....How do I post a workbook? I can see no icon to upload a workbook

[shg]

The forum rules tell all ...

[blake_mulder]

ok, its nearly 1am here so I will check in the morning and get back here asap!

[blake_mulder]

car voltage day 2.xlsok, how is this?

[shg]

That appears to be corrupted.

[blake_mulder]

deleted, new post added

[blake_mulder]

Battery voltage.xls

how about this?

[blake_mulder]

I have downloaded these files back to my laptop and they both opened fine, do I need to convert them to some other format?

[blake_mulder]

Hi Richard and thanks for coming back to me. Unfortunately your talking to someone who doesn't do spreadsheets so I need to keep things simple! Trend lines and LINEST is double dutch to me! Just trying to make some sense of the data I am collecting. The formula shg gave me has opened up some new ways for me to estimate the voltage loss seen on my vehicle battery with the ignition off. In a nutshell I am loosing some 250 milliamps due to some equipment on the car drawing power and if the car isnt started after a few days to charge the battery its very frustrating when I go to use the car and it wont start! Over the next few days I will trouble shoot the car electrics but at the moment I have a data logger connected to a netbook in the car which is connected over wifi to my laptop to monitor in real time whats happening from my living room. Also, what I am learning here I intend to use with regard to my solar panel setup which powers my ham radio equipment batterys so its all good fun!

---------- Post added at 07:48 PM ---------- Previous post was at 07:42 PM ----------

Oops forgot to mention in the data there is a small solar panel wired to the battery so in daylight hours current is added to the battery which then falls of in the hours of darkness - I only plot the voltage loss after the sun has gone down!

[blake_mulder]

Oops forgot to mention in the data there is a small solar panel wired to the battery so in daylight hours current is added to the battery which then falls off in the hours of darkness - I only plot the voltage loss after the sun has gone down!