Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
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. "Vlad" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
=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) "Vlad" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
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" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
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) "Bob Phillips" wrote in message ... =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) "Vlad" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spreading Formulas across the whole document
bump....can anyone please help
"Vlad" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a cell in another document in Excel formulas? | Excel Discussion (Misc queries) | |||
Formulas not updating until I save document | Excel Discussion (Misc queries) | |||
Spreading 1 large number evenly into several cells | Excel Worksheet Functions | |||
How to create a formatted 'readable' document based on Excel document? | Excel Discussion (Misc queries) | |||
Spreading a list. | Excel Discussion (Misc queries) |