What's the remaining number?

hi,

given 5 number datasets, how would I find the 2 numbers that are closest, the 2 numbers that are second closest and not the 1st pair and the last number remaining?

ex)

5

6

23

27

120 ***

so 2 numbers are the closest to each other (5&6), a different two are the second closest (23&27) and I want to know the one that doesn't pair up with any of the others (120).

any ideas for formula?

Re: What's the remaining number?

There is no alegbraic formula, per se, but it would pretty easy to set up a formula in Excel or a programmable calculator that compares differences and gives the closest and second closest set of differences. If limited to just 5 data points per set, and if they are provided in sorted order as in your example you have only 4 differences to compare with each other. But you will have to decide how to handle ties - for example if given a data set of 1, 2, 3, 4, and 5 there is no "closest" or "second closest" difference, so what answer do you expect? Also you mention a "last number remaining," but consider that there might be two last numbers remaining: for example in the set 5, 6, 8, 20, 50 the closest pair is 5,6 and the second closest is 6,8 so there are two numbers remaining (20 and 50).

1 Attachment(s)

Re: What's the remaining number?

Hi, sorry, I must have not been clear. Okay, I have 5 numbers, I want to identify the two that are closest. Those are then ruled out. Then I want to identify the two that are closest of the remaining three. Those are then ruled out. I have one number left.

Yes, I am using Excel but I'm having trouble coming up with the logic for it. I just nailed down getting my 5 numbers acurately.

The current example is of:

123.3605396

92.17713822

124.3338132

83.63726534

116.4912436

I can see that the answer is 116.4912436, but how would I arrive at that using excel so I can use many different datasets and get the answer over and over.

1 Attachment(s)

Re: What's the remaining number?

Here's one way to do it in Excel - there may be others that are more elegant. I edited your spreadsheet - see cells Z8:AE13, which does the following:

1. Column Z: Start by putting the 5 data points in order from lowest to highest - you can use the large() function for that.

2. Column AA: Then calculate the deltas between adjacent pairs of numbers

3. Column AB: Now cast out the two numbers on either side of the smallest delta.

4. Column AC: Compress the remaining three data points into 3 rows.

5. Coluimn AD: Calculate the two deltas

6. Column AE: The result is either the 1st or last of the 3 numbers, whichever is not associated with the smaller delta

I must point out that this blows up if there are ties, so a bit of error checking should be added in.

Hope this helps!

Re: What's the remaining number?

Wow, thanks. That's much better than how I did it.

Hey, I'm trying to find different ways of establishing the "top" triangle of a five pointed star for a point of reference. I've tried cardinal directions (outer point angle from center), outer point closest to vertical from center and now i'm trying symmetry (oddball outer angle, oddball inner pentagon angle, etc..).

Can you think of any other way to establish one triangle in a five pointed star as a constant?

Re: What's the remaining number?

Sorry, I don't understand what you are getting at. What do you mean by a "top" triangle. I'm guessing that given the (x,y) coordinates of the 5 points of the star you're trying to determine which point has the largest y-coordinate (therefore the "top"), but that seems awfully trivial. What am I missing?

Re: What's the remaining number?

Sorry, I don't actually mean "top" as in largest y value. I mean I'm trying to find various ways of determining one triangle of a five pointed star as a starting point like "A" and then all the rest of the points can be labelled B,C,D & E. I assume that to do that, all I have to do is identify all of the constants that are present right?

For all varieties of five pointed stars, with all different lengths and angles of star arms, if you had to pick one triangle as the "main" one or "A", what constants would you use?

Re: What's the remaining number?

Still not following you. What "constants" are you referring to? If you start with the tip of one arm labelled 'A' at coordinates (x,y), are you talking about the distance and angle from 'A' to the next tip of the star at 'B?' Perhaps if you gave an example of what you're trying to accomplish it would help. Be specific as to what data you have and what result you're trying to determine. And also be clear as to how the star is constructed - if there are five straight lines from point A to C, C to E, E to B, B to D, and D to A, then all you need is the ccordinates of the 5 tips. But if the star is more "free form" then you also need to define the coordinates of the "valleys" between the points.