Calculate battery power used

Occasional Contributor

Help please.

I have four columns of data with 50k lines.  Col1 has an equipment identifier (23 units), cols2 & 3 contain datetime codes for equipment login/logout and col4 holds battery level percentages.  The table is sorted by Equipment ID then chronologically top to bottom by login datetime. The figures in col4 descend from between 90% & 100% to “around” 20%, with multiple login/logout events between, at which point a recharge/change event occurs and the next cell (below) will be back at between 90% & 100%.

Need to determine the time period (hrs) and actual percentage used between battery change/charge events.

Can this be achieved with the use of a formula or would a macro be required?

12 Replies
This is do-able with a formula. Do you happen to have a sample workbook?


I do have a sample sheet but I don't know how to attach it here.


Choose Reply, then Open Full Text Editor.  You'll see this below the compose box:




Interesting that you have a drag and drop option below the 'Hint @ links to members' note! Whereas I do not??  Any idea?

It could be the user level. It seems file upload might be unavailable for newer accounts. You could upload the file to OneDrive or another file hosting site and share the link.
This is doable with an array. Are you using 365? I'll have a formula as soon as I get a spare moment or two to create the formula.


I think this might work for you.  Adjustments can be made as needed.


Thank you for this but if it were only that easy, I think even I could have done it.  I didn't explain my requirement very well I don't think.  Take a look at the attached;  it will describe my problem.


best response confirmed by mtarler (Trusted Contributor)


This was a fascinating project because MAP was bonking out (I7 processor, 32 GB RAM) with this large data set.  I had used MAP to identify the rows where the calculations would run and to help with the subtraction.  MAP/INDEX/ROW seems to have limits.  I did not try this with insider functions.


I went with simpler dynamic arrays.  Ultimately, this data set has to be paired down to perform the calculations.




Hello Patrick - I've just spent the day working out what you did

I have reduced my data down to a month's worth - it was originally an entire quarter.  I then made a slight mod because we have an anomaly in our data retrieval system that could mean it is up to 30% out.  We just live with it.  Other than that it worked very well - Thank you very much - I would never had got to that point on my own.

You're welcome. Glad it worked!