C# needed or possible in Excel?

[CiBi]

Heya guys

 

I've been trying to figure out what approach would be best but since I'd need to do some studying in both cases its been hard to determine just using Google. For that reason I thought it might be a whole lot simpler to just ask people who might have already done something like this.

 

I learned C# in school a while back and haven't used it in a long time but now I'm thinking I might want to get back into it to automate something.

However, the thing I want to automate might just be easily possible in Excel.

 

The goal is the following: I want to calculate the average battery autonomy from a TSV log. I want to automate it so I can redo it every time I get a new log.

 

The log contains a whole bunch of information, charging currents etc but the only parameters I need are 'timestamp', 'SoC' and 'charger state'.

Below some example data (cleaned up).

 

Formula would be something like this:

when charger state changes from ≠ 'NO_PS' to = 'NO_PS' that is time1 and SoC1

when charger state changes back from = 'NO_PS' to ≠ 'NO_PS' that is time2 and SoC2

 

autonomy = ((time2 - time1) / (SoC1 - SoC2))*100

 

Then I would want to average out all the total autonomy's I get from a log file.

 

My concrete question is, is this possible in Excel? If possible, is it feasible to do it in Excel or would another way be preferable? And optionally some more direct pointers would also be very welcome.

 

example file.tsv

[mariushm]

Without resorting to macros, It's possible to do but in multiple passes 

 

First, determine start and finish by adding formulas to a column after column C  

Something like...

 

On row 6, column D :  =IF(C6='NO_PS', IF(C5<>'NO_PS', 'start','crap), IF(C5='NO_PS','stop','crap'))   -- something like that. 

 

So you'll have "start" on the row that shows the first no_ps, and stop on the row after the lest no_ps 

on the next column E, you can use another formula to copy the date   IF (D6 = 'start',  IF (D5 <> 'start', A6,''),E5)  or something like that

 

can be done with a bunch of nested ifs  copy date from left side only if the row above doesn't have start, but current row has start

copy date from previous row if current row has start and previous row has start 

if the value is stop or finish or whatever, copy date from left 

so now wherever you have the "stop" or "end" you know the right column has stop date and a row above you have the start date, so you can use formula.

 

[wanderingfool2]

16 hours ago, CiBi said:

My concrete question is, is this possible in Excel? If possible, is it feasible to do it in Excel or would another way be preferable? And optionally some more direct pointers would also be very welcome.

Pretty much anything is possible in Excel (just some things aren't worth the effort).  [See below for how I would approach it]

 

It can really depend on how you want to consume the data, and what you intend to do with the data.  I found that usually it's good keeping things in excel formats when involving other uses who will need to analyze the data (non-techy people).  It can also be good for quick visualizations, prototyping or one off kind of things.

 

But yea, if I were to be doing it, similar solution to what @mariushm said (especially if it just needed to do it a few times, and you weren't exactly comfortable with things like C#).

 

My syntax and stuff might be wrong...I don't have excel in front of me at the moment

Column D3 (Essentially, D column will be whether to store the last valid row)

if(AND(C2 <> "NO_PS", C3 = "NO_PS"), row(), D2)

^-- Effectively the D2 part copies the previous result if there wasn't a switch (so we know which was the last valid row)

 

Column E3 (Essentially, E now trigger when the switch is detected...and will calculate the answer)

if(AND(C2 = "NO_PS", C3 <> "NO_PS"), (A3 - INDIRECT("A" & D3))/(B3 - INDIRECT("B" & D3)), "")

^--- INDIRECT("A" & D3) and INDIRECT("B" & D3) is effectively looking up the value of time1, SOC1

You might want to do a sanity check though and do in like column F INDIRECT("A" & D3) just to make sure it's doing things as expected

 

From there you could just use =sum() =average() etc (or copy column E and paste as value...then sort).  Up to you.  Hope it helps, if you have any questions feel free to ask

[Avocado Diaboli]

Honestly, I would go the VBA route here. The kind of stuff you're trying to figure out in roundabout ways through formulas gets too complex too quickly to keep track of and maintainable.

 

One question I have is how that formula is supposed to work. Is the calculated time difference in hours, minutes, seconds or milliseconds? I assume given the granularity of the actual data you've provided, you're after minutes. Here's what an implementation of that would look like, assuming you have an Excel spreadsheet prepared the same way how your screenshot looks with all the data in the first spreadsheet and the columnes laid out identically.

 

Also note that I did a bit of rounding here to calculate the time differences as exact minutes and again later when calculating the average.

 

Sub FindAutonomy()

Dim l As Long
Dim usedRange As Long
Dim numberOfAutonomies As Long
Dim count As Long
Dim timeStart As Double
Dim timeStop As Double
Dim timeDiff As Long
Dim socStart As Integer
Dim socStop As Integer
Dim socDiff As Integer
Dim sumOfAutonomies As Double
Dim averageOfAutonomies As Double

With ThisWorkbook.Sheets(1)
    usedRange = .usedRange.Rows.count
    .Range("A1").Select
End With

For l = 1 To usedRange
    With ActiveCell
        If Not .Offset(l, 2) = .Offset(l - 1, 2) And .Offset(l, 2).Value = "NO_PS" Then
            timeStart = CDbl(.Offset(l))
            socStart = .Offset(l, 1).Value
        End If
        
        If Not .Offset(l, 2) = .Offset(l + 1, 2) And .Offset(l, 2).Value = "NO_PS" Then
            timeStop = CDbl(.Offset(l + 1))
            socStop = .Offset(l + 1, 1).Value
            
            timeDiff = Round((timeStop - timeStart) * 1440, 0)
            socDiff = socStart - socStop
                        
            sumOfAutonomies = sumOfAutonomies + Round((timeDiff / socDiff) * 100, 2)
            numberOfAutonomies = numberOfAutonomies + 1
            
        End If
    End With
Next l

averageOfAutonomies = Round(sumOfAutonomies / numberOfAutonomies, 2)

MsgBox averageOfAutonomies

End Sub