I recently had a need for an Excel spreadsheet to calculate the charge and discharge rate of some batteries I was testing. I made a simple spreadsheet to track the charge and discharge rates that will estimate when the battery would be fully charged or discharged based on readings from my battery meter. I will list out the logic below, and make the spreadsheet available for download as well. If you have suggestions for improvements, please let me know.
This spreadsheet assumes you will have a steady charge or discharge rate, but even if you don’t it will calculate, within reason, a time based on the usage for the charging or discharging of a battery. The more data you input, the more accurate the estimate will be.
Formulas
To calculate the charging rate, I am using the following columns for data:
Time, Time Difference, Reading, Reading Difference, Amp Hours/hour, Hours left, Estimated Empty/Full Time, Notes, and Amp Hour Rating which we hope is correct, since a lot of batteries lie on this rating.
The Time and Reading columns are simple entry columns. I calculate the difference between readings, times, and amps per hour it is using, which are all pretty straight forward. The only part that I had to look up was dealing with time as both a time and a decimal. I will list all formulas below for any who are new to Excel. You can also click the image below to download the spreadsheet to your computer.
For the reading time differences the formula is
(previous reading time – current reading time) * 24
For charging or discharging Time Remaining, I use the formula:
(Amp Hour Rating of the Battery – Current Reading) / Current Amps per Hour Usage
For the reading differences a simple subtraction:
Current reading – Previous Reading
For Amps per hour it’s a simple division:
(Reading Difference/Time Difference)
To estimate the time remaining, I use the formula:
Current Time + (Hours Left/24)
Since the Hours Left is being displayed as a decimal value (e.g. 4.5 for 4-1/2 hours) you have to divide by 24 to get the Estimated Full/Dead Time value to display as a the correct time.
Equipment
I love this battery meter I got from Amazon. The picture below is the one I used for my testing. It does not come with the Anderson PowerPoles, so if you need those, you will have to add them. I prefer to get the meter without the PowerPoles since most of the ones that come with them use some generic knockoff, and they just aren’t as good or worth the risk.
It works as it should, and it was only around 50% more to get the 2 pack instead of just one. It’s smart to go for the 2 pack for something this useful. I have not regretted having 2. I use these a lot, and it is nice to be able to monitor more than one thing at a time. Please be aware that this meter has no way to know your battery’s remaining capacity. It resets all readings to 0 each time it loses power. It is meant only to track usage per session.
The display will ALWAYS show the Amperage, Voltage, and Watts being used in the top left, top right, and lower right sections respectively.
Amps (A)
Only measure current from source to load. Measuring current in reverse will damage the meter. The average current drawn since the last screen refresh.
Voltage (V)
The average voltage since the last screen refresh.
Watts (W)
The average power in Watts (Amps x Volts) since the last screen refresh.
The bottom left section will cycle every second showing one of the following readings:
Minimum Volts (Vm)
The minimum voltage measured on the source side since the session started.
Peak Watts (Wp)
The maximum value for wattage measured since the session started.
Amp-hours (Charge) (Ah)
The total charge used in Amp-hours since the session started.
Watt-hours (Energy) (Wh)
The total energy delivered in Watt-hours since the session started measured from the load side.
Peak Amps (Ap)
The maximum current drawn since the session started including any spikes or peak amperage readings for as little as a fraction of a second.
I use mine all the time to check solar output, verify battery amp hour ratings, keep track of my battery usage when doing POTA or during field day or emergency communication events. The meter itself uses a very small amount of power, but it is well worth that tiny amount to know when I need to change out the battery on my communications gear. It is also very useful when using lead acid batteries so I don’t drain them too low and damage the cells.
The Batteries
For my tests, I was testing the Eco-Worthy 12V 10Ah LiFePo4 batteries I got from Amazon recently. So far, I LOVE these batteries. Read all about them in my review here.