You have scatter diagram, where you have nine dots. Each dot has value on X and Y-axis.

To draw a graph, activate the data range B2:C11, select under insert menu a scatter graph.

Now you have graph with dots.

It would be easier to identify each dot with a letter.

Write the dot identifiers in A column.

Activate the dots in the graph and click right mouse button. Then select add data labels.

Press select range and activate name range from A3 onwards.

If you inserted an extra dot in graph, which ones of the existing dots would be the closest to the extra dot ?

The value is defined by counting obs x value minus dot’s x value. That value is squared. Then obs y value and dot’s y value. That also is squared. Then we sum two values and take root square. This is repeated for all the dots.

The sentence in D3 is =SQRT(($B$12-B3)^2+($C$12-C3)^2). That is copied down till D11.

When we have values, we should rank the number. Let’s take top three dots. This we can do with RANK.EQ function.

Here is a simple example about RANK.EQ formula. Just enter the value and range. The highest number has first rank.

If we want to take just three biggest numbers, we add an IF sentence.

The formula in C3 is =IF(RANK.EQ(B3;$B$3:$B$7)>3;””;(RANK.EQ(B3;$B$3:$B$7))). Note that this is counting highest as the first value.

If the lowest number is the first, then you need to add one more argument with value one.

Then we need IF-sentence to take three lowest numbers.

The sentence in C3 is =IF(RANK.EQ(B3;$B$3:$B$7;1)>3;””;(RANK.EQ(B3;$B$3:$B$7;1))).

When we implement this logic to our original Excel, it looks like this:

The sentence in E3 is =IF(RANK.EQ(D3;$D$3:$D$12;1)>3;””;RANK.EQ(D3;$D$3:$D$12;1)).

That is copied down to E12.

As you can see from the graph, the closest points for the OBS are G, H and F.

One application of this functionality is if we want to define attributes for OBS, then attributes of G, H and F are also the closest attributes to OBS.