Formula that can determine the pair of values that gives the smallest difference

[mramono]

Hi

I have values in five different cells scattered across my excel sheet and I want to find the pair of those values that gives the smallest difference.

Assuming I have the value in a1, b1, c1, d1 and e1

Then I test the difference as follows

E.g. a1-b1 = 0.50
a1-d1 = 0.53
b1-c1 = 0.49
c1-d1 = 0.31
a1-e1 = 0.48
b1-e1 = 0.45
c1-e1 = 0.32
d1-e1 = 0.53

looking at the answers in column a2, such that a2 equals the formula that gives the smallest answer (In this case the formula in a2 must select c1-d1 because the answer is 0.31 which is the smallest of all the answers. The point is if the values in a1,b1,c1,d1,e1 changes respectively, the formula in a2 will be different as the smallest answer would have changed also for the pairs of values tested.

I will appreciate suggestion how to figure out such an excel function (in a2 for instance)

 

[Joe4]

I would use VBA to do this.
This code should populate A2 with the formula of the cells from A1:E1 that returns the smallest difference.

Sub MyMinDiff()

    Dim myCol1 As Long
    Dim myCol2 As Long
    Dim diff As Double
    Dim minDiff As Double
    Dim frml As String
    
    minDiff = 999999999
    
    For myCol1 = 1 To 5
        For myCol2 = 1 To 5
            If (myCol1 <> myCol2) And (Cells(1, myCol1) >= Cells(1, myCol2)) Then
                diff = Cells(1, myCol1) - Cells(1, myCol2)
                If diff < minDiff Then
                    minDiff = diff
                    frml = "=" & Cells(1, myCol1).Address(0, 0) & "-" & Cells(1, myCol2).Address(0, 0)
                End If
            End If
        Next myCol2
    Next myCol1
    
    Range("A2").Formula = frml
            
End Sub

 

[mramono]

I would use VBA to do this.
This code should populate A2 with the formula of the cells from A1:E1 that returns the smallest difference.

Sub MyMinDiff()

    Dim myCol1 As Long
    Dim myCol2 As Long
    Dim diff As Double
    Dim minDiff As Double
    Dim frml As String
    
    minDiff = 999999999
    
    For myCol1 = 1 To 5
        For myCol2 = 1 To 5
            If (myCol1 <> myCol2) And (Cells(1, myCol1) >= Cells(1, myCol2)) Then
                diff = Cells(1, myCol1) - Cells(1, myCol2)
                If diff < minDiff Then
                    minDiff = diff
                    frml = "=" & Cells(1, myCol1).Address(0, 0) & "-" & Cells(1, myCol2).Address(0, 0)
                End If
            End If
        Next myCol2
    Next myCol1
    
    Range("A2").Formula = frml
            
End Sub

Thank you, let me try the code.

 

[mramono]

Thank you Joe4 for the response:

A follow up question:

Again we still looking at having the values in five different cells scattered across my excel sheet and I want to find the pair of those values that gives the smallest difference.

Assuming still that I have the value in a1, b1, c1, d1 and e1

Where I must compare the pairs as follows: a1&b1, c1&d1, a1&e1, b1&e1, c1&e1, d1&e1

I would like to proceed the calculations (select) with the pair where the values are closest to each other. E.g. if a1-b1 << (c1-d1, a1-e1, b1-e1, c1-e1, d1-e1), the subsequent calculations are done using values in a1 & b1, the values in c1, d1 and e1 are rejected.

Can this be done without using a macro?

Context for the problem:
Assuming you want to determine lead content in the Zebra blood stream. You use 3 independent laboratories for this analysis. But first you put laboratory 1 and laboratory 2 head to head. Results from laboratory 1 is populated in a1, laboratory 2 in b1; then you ask both laboratories to repeat the analysis, you then put repeat analysis for laboratory 1 in c1 and repeat results for laboratory 2 in d1. You also choose a laboratory 3 to Umpire between the laboratory 1 and 2. The results of the umpire are entered in e1. Condition: You can only compare the results of each laboratory with another laboratory, you cannot compare a laboratory with its repeat. The idea is that any pair of results that gives the least difference shows repeat-ability from two independent laboratories of results hence the lead content in Zebra blood stream is the average between the two results. Therefore, lead content in the Zebra blood stream will be (a1+b1)/2 because the a1&b1 were found to be the two pair with least variance (difference).

Can this problem be solved using existing excel functions without resorting to marcos.

 

[Joe4]

My macro should do what you want.

Can it be done with the use of a Macro? Possibly, but the formula would probably be huge, as you cannot do loops in a formula, so you would need to check each and every possibility explicitly in that one formula (unless you wanted to use a whole bunch of helper cells (that checks the difference of each one).
Returning just the smallest difference would be much easier than trying to return a formula of the cells that make up that difference.

These kind of problems scream VBA. I probably wouldn't attempt to do it any other way.

 

[mramono]

My macro should do what you want.

Can it be done with the use of a Macro? Possibly, but the formula would probably be huge, as you cannot do loops in a formula, so you would need to check each and every possibility explicitly in that one formula (unless you wanted to use a whole bunch of helper cells (that checks the difference of each one).
Returning just the smallest difference would be much easier than trying to return a formula of the cells that make up that difference.

These kind of problems scream VBA. I probably wouldn't attempt to do it any other way.

*************************************************

Thank you Joe4.

You are absolutely correct. I tried to perform the function without a macro and formula was huge and i ran out of characters allowed in the cell (more than 8100 characters). Although I have never used macro before, I was able to apply your macro and it was able to perform the functions as i've described it. But i'm unable to modify the macro you provided to suit the application I need to resolve on my spreadsheet. Perhaps i should try do my best to make my intended application more explicit.

I have the situation as follows:

P1720 = 61.80, AB1720 = 48, AO1720 = 61.83, BA1720 = 42.70, BN1720 = 47.46

Column P and AO contains results from first laboratory (original and repeat respectively)
Column AB and BA contains results from second laboratory (original and repeat respectively)
Column BN contains results from the Umpire (third) laboratory

We want the answer in column BZ such that BZ1720 = either (P1720+AB1720)/2 or (AO1720+BA1720)/2 or (P1720+BA1720)/2 or (AB1720+AO1720)/2 or (P1720+BN1720)/2 or (AB1720+BN1720)/2 or (AO1720+BN1720)/2 or (BA1720+BN1720)/2

and the choice of the answer that appears in column BZ is based on which of the following gives the smallest answer or difference {(P1720-AB1720)/min(P1720,AB1720), (AO1720-BA1720)/min(AO1720,BA1720), (P1720-BA1720)/min(P1720,BA1720), (AB1720-AO1720)/min(AB1720,AO1720), (P1720-BN1720)/min(P1720,BN1720), (AB1720-BN1720)/min(AB1720,BN1720), (AO1720-BN1720)/min(AO1720,BN1720), (BA1720-BN1720)/min(BA1720,BN1720)}

Is it possible to have a macro that resolve this?

 

[Gerald Higgins]

This formula returns the MINIMUM of the paired differences, divided by 2.

=MIN(ABS(P2-AB2),ABS(P2-BA2),ABS(P2-BN2),ABS(AB2-BN2),ABS(AO2-AB2),ABS(AO2-BA2),ABS(AO2-BN2),ABS(BA2-BN2))/2

The result, for the given input values, is 0.27.

Is this what is required ?

Note - I changed the row references to 2, from 1720, for simplicity - adapt as required.

 

[Joe4]

A few questions:

1. Are you just looking for it to return the number of the smallest result, or the address of the cells that returns the smallest result (like my original macro does)?

2. Will the cells you are comparing always be P1720, AB1720, AO1720, BA1720, BN1720 or does this change?

 

[mramono]

A few questions:

1. Are you just looking for it to return the number of the smallest result, or the address of the cells that returns the smallest result (like my original macro does)?

2. Will the cells you are comparing always be P1720, AB1720, AO1720, BA1720, BN1720 or does this change?

**************************
Hi Joe

1. Yes, just like your original code was doing. I would like it to return the address of the cells that returns the smallest result.

2. No, I would like to compare different cells, but those cell will always be on the same column.

Thank you for your help.

 

[Joe4]

2. No, I would like to compare different cells, but those cell will always be on the same column.

So, you mean it will always be columns P, AB, AO, BA, and BN? Is that what you are saying?

Is this going to run on just one row at a time, and do you want the macro to run against a bunch of rows at once?
How should we communicate to the macro which rows to run on (we can do the selected rows, input for rows, or hard-code rows)?