Spreading Formulas across the whole document

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a document that it has 35 thousand entries of different times. I need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You
 
Are you looking for a cumulative time from the first entry to whatever entry
is on the row you're looking for? If so, lock the first time down with the
F9 key in your formula.

For example, assuming the first piece of data is in A1, here are the formulas:

12:01:03 AM
12:01:06 AM 0:00:03 =A2-$A$1
12:02:26 AM 0:01:22 =A3-$A$1
12:02:30 AM 0:01:27 =A4-$A$1
12:03:50 AM 0:02:46 =A5-$A$1

Type the first formula, then copy it all the way. The $A$1 won't change,
but the other references will.
 
=SUMPRODUCT((B1:I10<>"")*(B1:I10-A1:H10))

adjust to suit, but note how the subtracted ranges are offset by a column

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Oh, and format the results cell as [h]:mm

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thank for your quick respone Bob, but i am not sure what to substitute for
the B1 and I10 and H10, if you can please explain that to me..


I also wanted to clarify something....just in case you guys did not understand
The time goes on for around 34 thousand rows. Since the Date spands from
June 18th to Feb 28th. I wanted to find out what is the time interval between
the later time and the earlier time. So for example in this case

12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM

12:01:06.309 - 12:01:03.396 = Answer
12:02:25.823 - 12:01:06.309 = Answer
12:02:30.195 - 12:02:25.823 = Answer
12:03:49.709 - 12:02:30.195 = Answer
---------
Total

Now the total i am pretty sure how to get, the hard part for me is figuring
out a formula that will span over the long document and that will the higher
time and subtract it by the lower time just like i showed in the
example......this might be impossible since i am not a Excel guru....but any
help or hint that will direct me to the right track would be much appreciated!

Tahnk You






Bob Phillips said:
Oh, and format the results cell as [h]:mm

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Vlad said:
I have a document that it has 35 thousand entries of different times. I
need
to figure out the time intervals between every single entry. The formula i
use is pretty simple =(H#-H#). For example how would i adjust that formula
so
it takes in account all the 30 something thousand of other rows.

My thing looks something like this
Time Interval
12:01:03.396 AM
12:01:06.309 AM
12:02:25.823 AM
12:02:30.195 AM
12:03:49.709 AM


Is there a formula that i can get the difference between each time and add
it up for a total amount of hours....

please any advice is VERY appreciate it

Thank You
Click to expand...
Click to expand...
 
A1 will be the first cell with a time, and B1 will be the cell to the right.
I1 will be the last cell on the FIRST ROW with data in, H1 will be the cell
to the left of that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top