A reader needs to find the difference between the time listed and the earliest time listed for that same day. Here’s the data:
| Date | Time | Difference |
|---|---|---|
| 6/9/2014 | 14:49:05 | 0:00:00 |
| 6/9/2014 | 14:49:47 | 0:00:42 |
| 6/9/2014 | 14:50:33 | 0:01:28 |
| 6/9/2014 | 14:51:17 | 0:02:12 |
| 6/9/2014 | 14:51:31 | 0:02:26 |
| 6/9/2014 | 14:51:56 | 0:02:51 |
| 7/9/2014 | 6:19:55 | 0:00:00 |
| 7/9/2014 | 6:21:09 | 0:01:14 |
| 7/9/2014 | 6:21:31 | 0:01:36 |
| 7/9/2014 | 6:22:25 | 0:02:30 |
| 7/9/2014 | 6:22:53 | 0:02:58 |
| 7/9/2014 | 6:23:23 | 0:03:28 |
| 7/9/2014 | 6:23:47 | 0:03:52 |
The formula in the Difference column, C2, is
|
1 |
{=B2-MIN(IF($A$2:$A$14=A2,$B$2:$B$14,""))} |
, filled down to fit the data.
It’s an array formula, so don’t type the curly braces, but enter with Ctrl+Shift+Enter, not just enter. The array part of the formula, the part subtracted from B2, is the smallest value where the date in column A is a match. By selecting everything in the MIN function in the formula bar and pressing Ctrl+=, you can see how Excel is calculating the minimum.
|
1 |
=B2-MIN({0.617418981481482;0.617905092592593;0.6184375;0.618946759259259;0.619108796296296;0.619398148148148;"";"";"";"";"";"";""}) |
Because we’re dealing with times, the numbers aren’t so easy to read. But the important part is at the end of the array – a bunch of empty strings. When the date doesn’t match, the IF function returns an empty string. MIN ignores any text, so only the smallest of the numbers listed is returned.
Or not using CSE, “borrowing” from Daniel Ferry,
(ref http://dailydoseofexcel.com/archives/2010/03/23/maxif-and-minif-with-multiple-variables/)
@Tristan, interesting but seems a little long-winded. Isn’t this a bit simpler for non-CSE?
Yes, yes it is. I need to start utilizing N()..
If the data is sorted by date + time (like the sample), this formula will be enough.
=IF(A2A1,0,B2-N(B1)+C1)
Regards
@Ihm
MIN also ignores logicals, so the value_if_false clause can be omitted:
=B2-MIN(IF(N(INDEX($A$2:$A$14=A2,)),$B$2:$B$14))
Regards