Forum Discussion
sorting binary data
I have some pump runtimes that I'm wanting to condense into readable and presentable data. I have a snippet of a sample table that shows the data I'm working with on a larger scale. I have some previous programming experience but cannot connect the dots right now to get to where I want to be. Any help at all would be much appreciated. 0 signifies not running 1 signifies running. The larger tables are minute by minute. Like I said this is a snippet. Ideally my end goal is an output that says both pumps off for x minutes then pump 1 or 2 on for y minutes then both off again for x then on for y repeated until the entire table has been gone thru. The reports that have the raw data are generated in excel and I'd like to be able to apply some formulas to them to get my desired results. Any help is appreciated. Thank you.
| Bennett's LS | Bennett's LS |
| PUMP #1 | PUMP #2 |
| Value | Value |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 1 | 0 |
| 1 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
| 0 | 1 |
| 0 | 1 |
| 0 | 1 |
| 0 | 0 |
3 Replies
A rather long and tedious 365 formula.
= LET( pumps, Pump1&Pump2, change?, pumps <> DROP(VSTACK("",pumps),-1), new?, DROP(VSTACK(change?,TRUE),1), accum, SCAN(0, change?, LAMBDA(acc,c?, IF(c?, 1, acc+1))), status, SWITCH(FILTER(pumps, new?), "00", "Both off", "10", "Pump1 on", "01", "Pump2 on", "Both on"), time, FILTER(accum, new?), HSTACK(status, time &"min") )jjonescity Attached is one way that you can summarize the data as you described. I'm sure there are more elegant ways to do it, but this works. I added a few helper columns to work through the steps as you can see.
The key to a few of the helper columns is to use a range reference that starts with a fixed reference and goes to a relative reference, like $D$9:$D9. When you fill the formula down, the range reference will grow to include the current row and the rows above it.
For example, the formula in the "Previous" column is =IF(G14="","",XLOOKUP(F14,$F$9:$F13,$G$9:$G13,0,,-1)). In the screen shot below, the formula is on row 13, and the range references start at row 9 (fixed) and go to row 13 (relative, current row). This formula finds the previous value in the Total column for the Type that's in the current row.
