Formula for determining next occurrence in a list

**KevanC** · 10-22-2010, 04:41 PM

Hello... I've been working for a week on a spreadsheet and have had great trouble building the right formula. I've done a lot of Google searching and I keep getting this forum as a result to many of the questions. So, I figured I'd post the problem here.

I am building a spreadsheet that will determine the closest fire apparatus to a location. I have the columns labelled Station, Time, Distance, Apparatus. I have already determined the distance and time the apparatus is from a station. However, we have over 60 fire stations. I would like for the spreadsheet to first search down the list of time for the minimum time. There are several occurrences of stations having the same time. So, as a second criteria, I'd like it to look at the distance for the minimum distance. Finally, I'd like to look and see if that station has apparatus available. I'd like the list to return the first, second, and third closest engines and ladders to a location. Not every station has both an engine and ladder. I've used an "x" to denote whether a station has an engine and ladder, or not.

Here is an example:

A1= Station, B1=Time, C1=Distance, D1=Engine, E1=Ladder
A2=1, B2=4, C2=3.2, D2=x, E2=<blank>
A3=2, B3=1, C3=0.4, D3=x, E3=x
A4=3, B4=4, C4=3.5, D4=x, E4=x
A5=4, B5=8, C5=5.5, D5=x, E5=<blank>

Is it possible for a formula to return: the closest and 2nd closest engine, the closest ladder and 2nd closest ladder? All it would need to do is return the number in column A. I'd appreciate any help or suggestions you may have. Thanks!

**beaunydal** · 10-22-2010, 05:22 PM

This is one way using an array formula.
Array formulas must be entered using the Ctrl+Shift+Enter key combination.
This formula Ctrl+Shift+Enter-ed into H2 returns the closest Engine.

Please Login or Register  to view this content.

When it is filled across to I2 it returns the closest Ladder.
When the formula in H2 is filled down to H3 it returns the second closest Engine.
When the formula in I2 is filled down to I3 it returns the second closest Ladder.
All ranges will need to have their bottom row number increased to accommodate a larger table.

**KevanC** · 10-22-2010, 08:45 PM

Thanks... I've never used an array formula. I'll do some analysis of your formula so I can recreate it in the larger spreadsheet.

**KevanC** · 10-23-2010, 09:33 AM

Here is the spreadsheet that I'm working on. Also, the time should be the first reference and then to distance. I wasn't sure if that formula did that. I have instances in the actual spreadsheet where there is a longer time with a shorter distance. Is it possible to do it this way? I appreciate your assistance. I've got a start on what you sent. It took me a bit to figure it out since I'm new to arrays. But, I think I've got a handle on it. Thanks.

**beaunydal** · 10-24-2010, 12:56 AM

Does this sound feasible?
Use an array formula that constructs an array of values that are blank when there is no Engine otherwise the value is Time + Distance/1000.
The position of the smallest element in this array will be the same as the position of the Station number in $A$7:$A$73 where there is an engine and the Time is the smallest and the Distance is the shortest.
Similarly for the second (and third) smallest element, its position corresponds with the position of the Station where there is an Engine and the Time is the second smallest (which could be the same as the smallest Time) and the Distance is the second smallest (which could be the same as the smallest Distance).
The part of the final array formula that generates this array is...

Please Login or Register  to view this content.

The smallest element in this array is returned by...

Please Login or Register  to view this content.

When the formula is filled down two more rows the ROW($Q1) increments to ROW($Q2) then ROW($Q3) to return the second and third smallest elements.
The position of the smallest array element, and hence the position of the appropriate Station, is found using the MATCH function...

Please Login or Register  to view this content.

.
The Station number is then found using the value returned by the MATCH function inside the INDEX function acting on $A$7:$A$73...

Please Login or Register  to view this content.

Filling this formula down 2 more rows yields the second and third closest Stations (with respect to Time first then Distance second).
If fewer than 3 Stations satisfy the "Has Engine" criterion then one or more of the three formulas will return #NUM! error.
If you prefer the error message to be bypassed and replaced with a blank cell instead then the ROW($Qn) part can be compared with a count of the non-blank elements in the array generated by...

Please Login or Register  to view this content.

like this...

Please Login or Register  to view this content.

Filled down 2 more rows then across 1 more column for the corresponding Stations with a Ladder.
See attached doc.

Please note that the formula would fail to distinguish between different Stations satisfying all three criteria with exactly the same values.
For example, say two Stations with numbers m and n (m<n) both have an Engine, and both have the same Time and Distance values. They will both result in the same value in the array returned by...

Please Login or Register  to view this content.

The array element corresponding to Station m will appear closer to the top of the array than will the same value element corresponding to Station n.
If the array element corresponding to Station m is the kth smallest array element then Station n's array element will be the (k+1)th smallest element.
When the MATCH function looks for the kth smallest element it will correctly return the position of Station m.
However, when it looks for the (k+1)th smallest element it will again return the position of Station m, not the position of Station n.
Hopefully the likelihood that your data could contain different Stations with identical Time and Distance values, while also being in the possession of an Engine or a Ladder, is so remote that it need not be considered.
Otherwise a more complicated formula will be required to break such ties.