currently have data where by its measuring usage of each electrical sockets, Energy by date time (seconds).

I need to create a new column as below. Cant be done in excel due to the amount of data/rows

Date/Time Socket ID Energy New Column

18/10/2019 - 13:01:01 1 0.1 0.1
18/10/2019 - 13:01:05 1 0.3 0.3
18/10/2019 - 13:01:07 1 0.5 0.5
18/10/2019 - 13:01:09 1 0.6 0.6
18/10/2019 - 13:01:11 1 1.0 1.0

18/10/2019 - 13:01:15 1 0.0 1.0
18/10/2019 - 13:01:18 1 0.3 1.3
18/10/2019 - 13:01:20 1 0.5 1.5
18/10/2019 - 13:01:22 1 0.7 1.7

As you can see the Energy column reading increases at each reading from 18/10/2019 - 13:01:01 however when the socket is switched off the reading then sets to 0.0. what I need to do is bridge the gap. I need to create a new column as above and say *IF the current reading is less then the MAX reading then ADD MAX reading to current reading*. So in this case 1.0 is the MAX reading and lets say 0.0 is current I need to add 1.0 to 0.0.

also their are multiple sockets so it would look more like this,

Date/Time Socket ID Energy New Column

18/10/2019 - 13:01:01 1 0.1 0.1
18/10/2019 - 13:01:01 2 4.3 4.3
18/10/2019 - 13:01:01 3 5.5 5.5
18/10/2019 - 13:01:01 4 1.6 1.6
18/10/2019 - 13:01:01 5 1.2 1.2

18/10/2019 - 13:01:15 1 0.0 1.0
18/10/2019 - 13:01:15 2 0.1 4.4
18/10/2019 - 13:01:15 3 0.2 5.7
18/10/2019 - 13:01:15 4 0.0 1.6
18/10/2019 - 13:01:15 5 0.3 1.5

Hope this makes sense?

Forum Discussion

help