Counting time

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!


Also, please check your posts for typos, because it's not entirely clear to me whether some of the characters you typed above are intended to be there or not. For example, I don't know what to make of this:

I need to eb the same as the previous

.... and from the data quality of your post I can't assume that anything that you typed about your data can be taken at face value.

i cant understand that at all! also why are you array entering formula?

Can you spell out the rules for your expected results in plain words?

The only difference between your (expected) column C and (calculated) column B seems to be in row 50. What are you expecting to see based on which rules?

i want to add up the time, the time repeats over and over. IE you could have the same data continuing 300 times, but I need it to ALL add up. to a TOTAL time in seconds.But the problem is those formulas don't always work. I honestly don't know how to be much clearer then that. ADD up the TIME. so in row 50, where it starts back at 0 it's going to go BACK up to 59 then back to 0 then back to 59 then back to 0. make sense? I need it to just add up, but sometimes data is collected within the same second so it doesn't count as a new second IE row 14 & 15 they are counted in the total time as apart of the SAME second. If this still doesn't make sense. I honestly have no idea what else to say.

EDIT: to answer what i expect to see...

i'm expecting if the time counting is equal to the previous one IE row 14 & 15 they should be counted as the same second in total time, if a 59 is followed by a 0 or 1 or 2 etc it is added to it's total time. IE rows 48-51. If there is a 0 in the data because of a loss of data, it should just be added to the previous second and counted to be counted. there is NO exact answer to what i expect, they will vary from every data set. I can't give you a specfic example for everything, because sometimes the data starts at 0 sometimes it starts at 45. It all varies.

Why not (starting in E3 dragged down)

=IF(AND(ROW()>4,D3=E2,D2=0),E2,D3-D2+E2)

That seems to give you your expected results. Yes?

Perhaps, in B3:

Please Login or Register  to view this content.

copied down?

Combining with ChemistB's solution:

=IF(A3=0,LOOKUP(2,1/(A$2:A2<>0),B$2:B2)+1,B2+A3-A2)

should work best.....

Edit... I guest Chemist changed his solution....

your guys formulas do work unless I have a 1 after a 59 then they don't work. It works for my example data set. But if you put a 1 in instead of one of hte 0's you'll see it doesn't work. I have to take into account that the data can change and hardly be the same multiple times.

EDIT: if needed I can give you more data points. but they're basically repeating, it's just near the 55 second and 5 second (of the next minute) mark, where they screw up.

EDIT: Also the first equation Chemist gave me doesn't work.

So if there is a 1 after the 59 should the number be 60 in the next cell?

also if this helps, this equation USUALLY works, unless there is a 0 in between numbers like column D. =IF(A3=0,H2+1,IF(A3=1,H2+1,IF(A3=A2,H2,IF(A3-A2=A3,H2+1,A3-A2+H2))))...


EDIT: disregard that last statement, if you place this equation into my created data you'll see it does NOT work. thanks.

Is this any better?

=IF(A3<A2,LOOKUP(2,1/(A$2:A2<>0),B$2:B2)+1,B2+A3-A2)

If I put 0's amidst, I get accumulation of seconds... so I am not understanding anymore your request....

It would be suggested to update a better example showing a combination of things that could happen so that all bases are covered, in one column and in the next column, manually enter accurate expected results.

if the time (column A) goes 58 59 1 2 3 it would be (column B) 58 59 60 61 62. or 58 59 0 2 3 it would be 58 59 60 62 63

If going from 59 to 0 raises the value by 1, wouldn't going from 59 to 1 raise it by 2?

Can anyone please explain what we're actually trying to achieve?

Something like:
We start with the data in column (?) and want to achieve the expected results I demonstrated (manually) in column (?)
If the value in (??) is (??) then the value in (??) needs to be (??+/-??)
and If the value in (??) is (??) then the value in (??) needs to be (??+/-??)
...
etc.

?

Okay, now explain "dropped data" to me. You gave the example of 34,35,37,0,39. That's different from when a number just "turns over", yes? (For example 59, 0, 1)
What do you want when data is dropped? Fill in the following series
Col A ...34, 35, 37, 0, 39... Col B: 45, 46, 48, ?, ?
Thanks

Okay, next question, when do you determine that it's dropped data and not "turned over" data? i.e. where's the cutoff; 59, 58, 55, 50?

Yes, it makes sense to me. I'm just trying to figure out how to explain it to Excel.

lol thank you for making me feel less stupid. Explaining over the internet has been VERY difficult. and when trying to figure it out please remember the start can be anything too. IE. the count could start like this 0 0 0 49 50 51 53... because the seconds start with whatever time it is. Maybe this is an important key i left out? the seconds are according to time. I was trying to take the time of day and making it all one value so it would be easier to use. IE i took 2:30:45 and made it all into seconds with the FIRST spot starting at 1. hope that doesn't confuse you more.

Is there a typical range of seconds between readings? Maximum and minimum?

So with 0 0 0 49 50 51 53
would be be 1,1,1,50,51,52,54?

Re dropped data:
What if the numbers either side of the drop are not in order?
You've explained:
6,7,8,14,10 = 1,2,3,4,5

but what about:
6,7,8,14,9 = 1,2,3,?,?

and:
6,7,8,14,11=1,2,3,?,?

perhaps if we tried to correct the wrong data first eg 13,25,16 replace with 13,0,16 as these should only be scattered about . then work on the resulting column instead?

mdw,

I like what you're doing but have two issues:

1. Surely if the anomalies weren't 0s they'd be easier to tackle, as 0s aren't necessarily anomalous (as explained somewhere in this monster)?

2. You test for and ( number > above, number > below ) but 13,14,3,16 is also an anomaly and is not already a zero

Proposed alternative pending...

i agree, if the data is garbage to start then it's going to be very difficult if not impossible to analyse properly.

Following on from mdw's good idea, OP, suggest you read the post again:

=IF(AND(ABS(A2-A1-1)>3,ABS(A3-A2-1)>3),"anom",A2)

?