Cumulative sum with filtered data

[Artyomaa]

Hello!

The easiest way to explain my request would be to refer to my attached example.

So basically i have a bunch of data that is generated by a computer. The computer records spesific paremeters(every column apart from C, J, and P) every 10 seconds.

I want to use the data to calculate the following key numbers:

- Cumulative SUM based on column M, in column C.
- Cumulative SUM of elapsed time, in column J.

If you check the formulas that i have in column C and J, those should clarify what i mean by cumulative sum. Column M (chainage) shows a distance in meteres, to a fixed point.

So, as you can see from "Sheet1" in the attached example, i have calculated all the values that i want. However, the problem occurs once i try to filter some of the columns. If you open the sheet called filtered, i have filtered the data in 2 columns. The formulas for the cumulative sum dont work properly when there is a "gap" in the rows, as a result of the filtering. I have highlighted an example of this problem, with red color (in the sheet "filtered"), where the rows jump from nr. 1036 to 1084. I think once you see the example, the issue will be quite apparent, so ill leave it at this. If there is any more details that you need, just ask!


Things that might be worth mentioning:

- Even though the computer is supposed to record every 10 seconds, it sometimes misses out on a few readings. So you will see that that time between each row, can be 20 or 30 seconds every now and then.
- From a first glance you will notice that the values in column M, are decreasing. In some areas of the column however, they will increase a bit, and then start decreasing. With the way my current formula in column C is set up, this is not a problem obviously. But thought it was worth mentioning.

Best regards

[Tsjallie]

The formulas for the cumulative sum dont work properly when there is a "gap" in the rows....

Can you give an example of the results you would expect when you filter the data.
The formulas seem to do exactly what they say they do
Or do you want the formulas to only look at visible rows?

[avk]

In filtered sheet, i found that some row is blank.
In row 1036 & 1084 you marking as red, in those cell what expected result to show. Can you explain.

[Artyomaa]

Can you give an example of the results you would expect when you filter the data...

I have now included an example of how i want the filtered data to look, check the "How it should be" sheet. So the main thing here, is that the values in column C and J should be the same in rows 1036 and 1084.

Or do you want the formulas to only look at visible rows?

I guess yes Because as you can see, the formulas in row 1084 refers to the cell 1083, which is a hidden cell due to the filtering.

In filtered sheet, i found that some row is blank.

Yes, im sorry for that. I had to delete alot of the data in order to reduce the size of the file, so i could upload it. I did this deleting while the data was filtered, so if you unfilter it, there will obviously be alot of blank rows. In the new example however, i fixed this - no blank rows.

I hope the new example clears out some confusion.

[Tsjallie]

I have now included an example ...

Forgot to attach it?

[Artyomaa]

Forgot to attach it?

Sorry about that I edited the post now, the file should be there

[avk]

Are you required formula to apply only for visible cells.

[Tsjallie]

For having the formulas to only look at visible rows you need to introduce a helper column to hold an unique index for the rows.
Assuming that this Index column would be inserted at the left of the sheet and so becoming column A then you could use this formula for making the index

Please Login or Register  to view this content.

where B is the column with the timestamp. Copy this formula in cell A10 and copy it down.
Then you can use index/match in your formulas to refer to the right (visible) cells.
Example for cell D1084:

Please Login or Register  to view this content.

With this formula for cell D1084 I get a value when filtered of 5.67 and when not filtered of 5.653.

I changed the data area into a table to make referencing more reliable. Also it has Excel copying the formulas down the column automatically.
Is this going in the right direction?

What I don't understand in the sheet How it should be is the Seconds Count cell (P1084) being 0:00.
I would expect it to be 11:40 (being the difference between the timestamps in row 1084 and row 1036) when filtered and with the formula looking at visible cells.

[avk]

instead of

Please Login or Register  to view this content.

use formula

Please Login or Register  to view this content.

Also in cell "E7" "F7" "G7" use

Please Login or Register  to view this content.

in all three column.

[Tsjallie]

103 and 101 in SUBTOTAL are only needed to exclude manually hidden rows.
Filtered rows are always excluded.

[avk]

yes incase by manually hide the rows then on safer side use 101 and 103.

[Artyomaa]

Then you can use index/match in your formulas to refer to the right (visible) cells.
Example for cell D1084:

Please Login or Register  to view this content.

First of all, im a little confused on where i should put this formula? Should it populate the entire column D? When i try to paste it into a cell in column D, i get an error with the "[Running meters]" part of the formula. I tried changig the " , " to " ; . But still, when i paste the formula and press enter, i gives me an error and highlights "[Running meters]"

With this formula for cell D1084 I get a value when filtered of 5.67 and when not filtered of 5.653....

If you mean for cell D1036, that would be correct. D1084 and 1036 are supposed to both be 5.653 after filtering. I tried to point this out in my previous post Check the second sheet in Example 2

[Tsjallie]

Attached is your first example which I adjusted:

I converted the data area into Excel table. That's why the formula uses column names (e.g. [Running meters]) instead of ranges (e.g. $D$10:$D$6078).
This makes references more reliable when the data area grows or shrinks. Also formulas are automatically copied over the whole column.

Column A has the formula rendering a unique index for all rows. This index automatically adjust when rows are filtered.

Column D, K and Q have formulas using the index so the only look at visible rows.

[Artyomaa]

Attached is your first example which I adjusted...

I tried to filter your example, and i still dont seem to get the desired result. Cells D1036 and D1084 still have different values, and so do Q1036 and D1084. The values in these cells have to be identical.

To hopefully clear out some confusion, ill try to explain what the data actually is. The data is recordings of a boring process. When the boring machine advances, the column Q "Chainage" should be changing for every row. Now, the data that i get is raw, meaning there will be recorded paremeters even when the machine is not boring, e.g. when there is no advance. So in order to only show the data that corresponds to advance sequences, i filter the data in a certain way. Thats why, the cumulative sum of column D and Q (when filtered), shouldnt change between cells where there is a discontinuiti in the timeframe. The machine is NOT boring between row 1036 and 1084, and thus the cumulative sum should remain the same in these cells. This is what i tried to point out in example 2, where i tried to show (the cells highlighted with green) how i wanted the data in column D and Q to look after filteting.

I realised something now. Column D is actually fine. Its impossible to make it show the same value for D1036 and D1084, because the corresponding ccells in column Q are slightly different. In theory, the values in column Q should be the same between rows that have a time discontinuity, and if they were, column D would also be 100% correct. So, we can forget about column D, this one is as correct as it can get And my original formula gives the same result as it turns out.

The column Q is still a problem though. Is there a way to make the values in Q1036 and Q1084 be the same? And so that this happends every time there is hidden rows between adjacent rows.

I hope i havent created more confusion.

[Tsjallie]

Just for my understanding:

• Column Q is the "Seconds Count" column?
• And that column should show the difference between the timestamp of the same row and the timestamp of the previous row, but only if the machine has been boring?
• So actually in column K (Time Elapsed) you want to show the nett boring time?
• Which would mean that column Q (Seconds Count) shows 0:00 when Chainage is the same as in the previous row?

[Artyomaa]

I converted the data area into Excel table...

By the way, could you explain how you did this? Im a beginner to excel I think this will be very usefull for me, because i get the raw data in a separate excel file, and then afterwards i copy the values from that file into the template sheet that we have been discussing here.

[Tsjallie]

There are three reasons for that:

1. When referencing cells or ranges you can use column names which makes references more reliable
2. Adding rows is more easy and reliable as Excel automatically extends the table when you type or copy data in the rows right under the table
3. When Exel extends the table it automatically copies formulas

The 3rd feature however only applies when all formulas are consistent within a column. In your case the first cell in columns D, K and Q have the value 0 which prevents Excel from copying the formula

[Artyomaa]

- Column Q is the "Seconds Count" column?

Im so sorry!! In the previous post, where i was mentioning column Q, i actually ment column K! Column K is the cumulative sum of the "elapsed boring time", while Q is "Seconds Count" column like you pointed out. Column Q does NOT come with the raw data from the computer, Column Q is just a helper column that i made, so that i could make a cumulative sum of elapsed boring time.

And that column should show the difference between the timestamp of the same row and the timestamp of the previous row, but only if the machine has been boring?

Yes! So for instance, between colum 1034 and 1084 boring has not been taking place, thus column Q should be reading 0:00 (guess this answers your question a few posts ago). Im not sure if you need column Q at all. Perhaps there is a way to directly use the timestamps and achieve what i want?

- So actually in column K (Time Elapsed) you want to show the nett boring time?
- Which would mean that column Q (Seconds Count) shows 0:00 when Chainage is the same as in the previous row?

Yes, almost The thing is though, that in theory the chainage should remain the same between rows where boring has not been taking place. But because the computer only records data every 10 seconds, the boring machine might actually advance a bit after the row that suggests that it stopped boring. Im not sure if that makes sense? Its difficult to explain. But like i was trying to say in my previous post, the value in N1036 and N1084 should, in theory, be the same. But as you can see, they are not. So the point here is, that im afraid you cant use: "...column Q (Seconds Count) shows 0:00 when Chainage is the same as in the previous row..." as a "criteria" in your formulas, if that was what you were thinking of doing ofc

Im not sure if you looked at the sheet called "How it should be", in example 2 that i attached. The green highlighted cells in column K shows quite clearly how i want the data to look after the filtering In essence, the formula that makes a cumulative sum in this column, should "stall" in cell K1084 and show the same value as in K1036, and then in K1085 the cumulative sum formula continues as before and until there is a new "jump" in the row number.

I have a feeling we are getting closer to a common understanding

[Artyomaa]

When Exel extends the table it automatically copies formulas

Does this apply to formatting as well? So that whenever i add data to the table, it will automatically format(number format and colour format) the area that has values values in it.

The 3rd feature however only applies when all formulas are consistent within a column. In your case the first cell in columns D, K and Q have the value 0 which prevents Excel from copying the formula

Aha, i understand. But is it not possible to generate the 0 values from a formula?

[Tsjallie]

Does this apply to formatting as well?

Yes

But is it not possible to generate the 0 values from a formula?

Yes. You can have a formula checking if the previous row is the header row and if yes return 0.

[Tsjallie]

I think indeed we're getting somewhere now.
Important, however, is to have some way to determine whether the machine has been boring or not.
As far as I know "Chainage" is the only lead for that. So I'm affraid we'll have to do with that somehow (unless you have yet some other trick on your sleeve).
When I look at the Chainage data then I see the value generally decreasing, but sometimes it's staying the same or even increasing.
Like you say "in theory" when no boring is going on the Chainage should stay the same, but in practice there may be slight differences.
Are these slight differences due to manoeuvering the machine when extending the drilling rod?
If so then may be we can assume a certain minimum decrease in the Chainage to determine whether the machine has been boring or not?

[Artyomaa]

When I look at the Chainage data then I see the value generally decreasing, but sometimes it's staying the same or even increasing

This is correctly observed! I also mentioned this at the end of my original post

Are these slight differences due to manoeuvering the machine when extending the drilling rod?

Excellent thinking! Yes, when the chainage suddenly starts increasing its because the drill rod is retracted, in order to extend it As for the parts where the chainage stays the same, this can be due to two reasons:

1. The machine is not boring, like we already agreed on.
2. The machine is boring, but sometimes the sensor that records the chainage may freeze up, for reasons that are unknown to me In those cases the chainage stays the same for a quite a bit, while other paremeters actually suggest boring.

Important, however, is to have some way to determine whether the machine has been boring or not.

The thing is, that a way to do this is already established The filtering that I set for two of the columns, is exactly doing that. So all the values in the filtered sheet, are more or less showing data for when the machine is boring "More or less" , because the filtering might remove some rows that actually belongs to a boring sequence, but this has no significant impact on averaged values over the entire boringtime.

If so then may be we can assume a certain minimum decrease in the Chainage to determine whether the machine has been boring or not?

I assume the comment right above answers this.

unless you have yet some other trick on your sleeve

My excel skills are very limited, as im a beginner. But i could suggest two things that might help you come up with some solution:

1. Maybe we could make excel somehow determine when the filtered data has a "jump" in rows, like between 1036 and 1084. And that in these cases, the helper column Q (seconds count), will give the value of 0:00 to row Q1084.

2. What about using this as a critirium: whenever the difference between the timestamp in a row and the row previous to it, is larger than 00:30 (because it usually is 00:10, sometimes 00:20, and on rear occasions 00:30), the difference in time between these rows will not accumulate to the cell in column K. Taking rows 1036 and 1084 as an example again, the result will be as following: The difference between the timestamps of 1036 and 1084 is far greater than 00:30, thus Q1084 will read 0:00, and since K1084 is linked to column Q, the value in K1084 will remain the same as in 1036.

Im generally starting to think that this is going to be hard to solve, with the state of the data that im working with :/

Thanks for your patience!

[Tsjallie]

The filtering that I set for two of the columns, is exactly doing that

So what you're actually saying is that if Paremeter 4 > 150 AND Paremeter 5 > 0 THEN the machine is boring.
If that rule is accurate (enough) then that would give us a 3rd way to solve the timer issue:
If Parameter 4 > 150 AND Paremeter 5 > 0 THEN count seconds between the current timestamp and the previous one ELSE Seconds Count = 0:00

If this is true then I would go for that 3rd solution, because 1 and 2 are rather "circumstantial" and more complicated.

[Artyomaa]

So what you're actually saying is that if Paremeter 4 > 150 AND Paremeter 5 > 0 THEN the machine is boring.

That is correct!

If that rule is accurate (enough) then that would give us a 3rd way to solve the timer issue

Its in fact very accurate! So indeed, it sounds to me like your solution is the best The only thing is, that in rear cases, i might want filter paremeter 4 to something like <165. Is there a way to add the filtering criteria into the seconds count formula? Paremeter 5 will always be <0, so its only for paremeter 4

[Tsjallie]

The Second Count formula can be constructed so that it only counts the seconds when Paremeter 4 is between 150 and 165.
Formula would be

Please Login or Register  to view this content.

But that leaves very few records.
And if I also filter on Paremeter 5 <> 0 then even only one record remains.
Not sure if that's got something to do with the data or may be I'm overlooking something.

[Artyomaa]

Im sorry for the late response!

But that leaves very few records. And if I also filter on Paremeter 5 <> 0 then even only one record remains..

Hmm, when i set the filters for parameter 4 and 5, i still have 721 records left. Could you upload an example? Because i dont quite understand where i should put the formula

[Tsjallie]

Maybe the data I'm working with is no longer adequate.
I've uploaded it.
Without filetering the workbook has 6078 visible rows.
When I filter the Paremeter 4 column on between 150 and 165 then that leaves 48 visible rows.
If I additionally filter the Paremeter 4 column on <> 0 then that leaves only 1 visible row.
The formula would be added to row 1085 in the Second Count column.

[Artyomaa]

The formula would be added to row 1085 in the Second Count column.

Hmm. So, if i understood correctly, i am required to actually know where a "jump" in rows occurs (row 1085 in this case) in order for this to work? The problem with that, is that i will usually have 15-30 of these "jumps". So the formula kind of has no value if i have to manually find all the jumps. I hope i didnt misunderstand you? The data in the example file is just a small exert from a larger set of data that im working with, thats why there's only 1 "jump" there.

When I filter the Paremeter 4 column on between 150 and 165 then that leaves 48 visible rows.

Paremeter 4 should only be filtered as "larger than". I mentioned 165, because sometimes, i might want to filter it larger than 165, but it most cases the filter for paremeter 4 will be ">150"

[Tsjallie]

Paremeter 4 should only be filtered as "larger than". I mentioned 165, because sometimes, i might want to filter it larger than 165, but it most cases the filter for paremeter 4 will be ">150"

Ok, that makes a huge difference.
If I now filter on [Paremeter 4] > 150 and [Paremeter 5] > 0 then that leaves 719 rows. Almost the number you get (721). Not sure if the difference is relevant.

As you say this filtering is very accurate for determining whether there has been boring between two consecutive measurements we can put this filter in the formula for the [Seconds Count] column.
Then the [Time Elapsed] would just be the sum of [Seconds Count] so far.
And also [Running Meters] should only add the progress of [Chainage] when [Seconds Count] > 0:00
If I adjust the formulas for that then [Time Elapsed] "stalls" when no boring is going on. Same goes for [Running Meters].
So filtering on [Seconds Count] > 0:00 would eliminate the "gaps". No need to find these gaps manually.

So far the good news. Now comes the bad news.
When I look at the data they are not like you show in the sheet "How it should be"
Perhaps I'm still missing something

[Artyomaa]

Now comes the bad news.
When I look at the data they are not like you show in the sheet "How it should be"
Perhaps I'm still missing something

Indeed, they are not exactly like i wanted it to be. I dont quite understand how the formula in [Seconds count] reads a value of 0:30 for cell Q1084. However, the fact that it reads 0:30, and not the timestamp-difference between row 1036 and 1084, is a huge step forward Ideally i would like Q1084 to read 0:00, because its the start of a new boring cycle. If it's hard to make it this way, which i can imagine it is, then it's not a big deal. The total time bored will be some minutes longer because of this, but its not a huge issue

[Tsjallie]

The [Seconds Count] only counts the difference between the [TimeStamp] of the same record and the [TimeStamp] of previous record if [Paremeter 4] > 150 and [Paremeter 5] > 0.
That would mean that between that measurement and the previous measurement the machine has been boring.
That's why [Seconds Count] in row 1084 reads 0:30 which is the difference between B1084 and B1083.

Ideally i would like Q1084 to read 0:00, because its the start of a new boring cycle.

The above tells that the new boring cycle started with row 1083 and not with row 1084. But perhaps that's not true?

Would it be possible at all to get 100% accuracy given the fact that the measurements sometimes are not accurate "for a reason unknown to you"?

[Artyomaa]

The above tells that the new boring cycle started with row 1083 and not with row 1084. But perhaps that's not true?

Aah, now i understand. But yes, like you guessed, its not entirely correct. The new boring cycle starts in cell Q1084.

Would it be possible at all to get 100% accuracy given the fact that the measurements sometimes are not accurate "for a reason unknown to you"?

The data is accurate for the purpose of counting seconds elapsed between adjacent timestamps So if we can just get the formula to count 0:00 for cells where a new boring cycle starts (like in cell Q1084), then it will be very accurate.

[Tsjallie]

But yes, like you guessed, its not entirely correct. The new boring cycle starts in cell Q1084.

Ok, so the formula actually should look at [Paremeter 4] and [Paremeter 5] of the previous row instead of the same.
I adjusted the formula for [Seconds Count] accordingly and [Seconds Count] in row 1084 now indeed shows 0:00. See attached workbook.
But there still is a difference in [Time Elapsed] (163:20 vs 235:00) and [Running Meters] (5.564 vs 5.653).

[Artyomaa]

Hello again!

Im really sorry for the late response, i have had some very busy days last couple of weeks!

But there still is a difference in [Time Elapsed] (163:20 vs 235:00) and [Running Meters] (5.564 vs 5.653).

I tried to implement the formula into my actual workbooks, and it looks like it's finally working just the way i want! The reason the running meters and time elapsed arent the same in your example and my example, is simply because it's not the same amount of data in my example as in yours

So, your latest tweak seems to have sealed the deal! Thank you very much for all your help and patience!

Best regards