1

I've got a column of bus stop ID numbers (Column A) and Column B are various timings of which each value is the time a person boards/alights the bus. In order to know how long the bus is stopped, I want to find the difference between the max/min in Column B given a certain value of A.

84009   9:17:38 AM
84009   9:17:40 AM
84009   9:17:41 AM
84009   9:17:43 AM
84009   9:17:50 AM
84009   9:17:55 AM
84029   9:22:47 AM
84029   9:22:47 AM
84029   9:22:49 AM
84029   9:22:50 AM
84019   9:23:49 AM
83049   9:28:19 AM
82069   9:32:32 AM
82069   9:32:49 AM
82049   9:33:58 AM
82049   9:33:58 AM
82049   9:33:59 AM
82049   9:34:02 AM
82049   9:34:10 AM
82029   9:35:13 AM

So the first 7 rows should print 00:00:17, the next 4 is 00:00:04 etc. since they are of the same A value. Is this possible? Feel like its getting close but not quite. Thanks a million. Any help would be greatly appreciated

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
user3415903
  • 13
  • 1
  • 4

1 Answers1

3

Use this one:

=MAX(IF(A1:A6=1889,B1:B6))-MIN(IF(A1:A6=1889,B1:B6))

and press CTRL+SHIFT+ENTER to evaluate it.

Test workbook

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • how did you improve the typography of your answer? – Gary's Student Mar 13 '14 at 15:23
  • sorry, Garry, what do you mean?) – Dmitry Pavliv Mar 13 '14 at 15:24
  • I mean the use of "pictures" for **CTRL + SHIFT + ENTER** – Gary's Student Mar 13 '14 at 15:27
  • 2
    use `..` like this: `CTRL+SHIFT+ENTER` – Dmitry Pavliv Mar 13 '14 at 15:28
  • Thanks for the reply simoco, problem is the values in column A are not in running order. The solution gives the right result for the first value of Column A only. Is there a way to input 'any given constant' instead of '1889' – user3415903 Mar 17 '14 at 17:39
  • use value from any cell, say `C1`: `=MAX(IF(A1:A6=C1,B1:B6))-MIN(IF(A1:A6=C1,B1:B6))` – Dmitry Pavliv Mar 17 '14 at 17:40
  • Circular reference it says. I believe the problem is that it is read A1:A6='one particular value only.' Tried A1:A6='minvalue in A': 'maxvalue in A' but that didn't work either. I'm doing this for a project with a huge data log of bus entry/exit timings. – user3415903 Mar 17 '14 at 18:07
  • I've tried your formulas and various permutations. Tried copying the values in A to another column D and entering C1: =MAX(IF(A1:A6=D1,B1:B6))-MIN(IF(A1:A6=D1,B1:B6)); however the problem is that it is doing the formula only for D1 and not subsequent values of D. – user3415903 Mar 17 '14 at 18:16
  • `it is doing the formula only for D1` - of course, it works as expected, if you need for another values drag formula down, say in C2: `=MAX(IF($A$1:$A$6=D2,$B$1:$B$6))-MIN(IF($A$1:$A$6=D2,$B$1:$B$6))` and so on – Dmitry Pavliv Mar 17 '14 at 18:19
  • Dear simoco, at risk of sounding like a complete idiot, can I ask, how do I apply your formula to my workbook. What is happening is that when I apply the array formula, the A1 in '$A$1:$A$20=A1' remains constant throughout whereas in yours in changes with the row. *flustered* – user3415903 Mar 17 '14 at 20:29
  • can you share link to your workbook (e.g. using https://www.dropbox.com) ? It's hard to help you without seeing your workbook.. – Dmitry Pavliv Mar 17 '14 at 20:34
  • I figured out what is your problem:) You selected entire range `C1:C100` and applyed formula. But you should select only `C1`, enter formula, press CTRL+SHIFT+ENTER and then drag it down until `C100` – Dmitry Pavliv Mar 17 '14 at 20:56