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)

 

[mramono]

Columns "B" & "AA" - column B is column number 2, column "AA" is column number 27.
2 + 27 = 29
So I am saying is the two column numbers we are comparing add up to 29, do not include it.
If you add up your other two column numbers in the same way, you will see those add up to 53.


Try changing this line of code:

frml = "=" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0)

to

frml = "=(" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0) & ")/2"

That's looks like a whole another ball of wax...

Hi Joe4


Thank so much, for some head way. I introduced another module (module 2) and used the same code and instead of "Cells(rows, cloop + 64).Formula = frml" , I replaced it with "Cells(rows, cloop + 77).Formula = frml"


These two modules now allow me to return the same answers but starting from two different columns (64 and 77) respectively. To complete my desired solution.


The code frml = "=(" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0) & ")/2", is currently returning the form (N2+AZ2)/2 on column 64

How can transform the same code now to return 100*ABS(N2-AZ2)/(MIN(N2,AZ2)) as opposed to (N2+AZ2)/2

 

[Joe4]

The code frml = "=(" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0) & ")/2", is currently returning the form (N2+AZ2)/2 on column 64

How can transform the same code now to return 100*ABS(N2-AZ2)/(MIN(N2,AZ2)) as opposed to (N2+AZ2)/2

OK, I am going to give this one top you as an exercise (to see how well I have taught you!). The important thing to remember is that anything enclosed in double-quotes is literal text, and everything else is variables. You use the "&" to sew all the pieces together.
If you place this line after the code live where you are setting the value of "frml", it will return the value you are building to a message box. You can use this to see what you are building, and make the proper adjustments:

MsgBox frml

 

[mramono]

OK, I am going to give this one top you as an exercise (to see how well I have taught you!). The important thing to remember is that anything enclosed in double-quotes is literal text, and everything else is variables. You use the "&" to sew all the pieces together.
If you place this line after the code live where you are setting the value of "frml", it will return the value you are building to a message box. You can use this to see what you are building, and make the proper adjustments:

MsgBox frml

Thank you Joe4

You were exquisite in the teaching me, I understood few things now. by no means a graduate!

Hope you will feel proud of me, I managed to get these going as well:

The code frml = "=(" & Cells(rows, cols(i) + cloop).Address(0, 0) & "-" & Cells(rows, cols(j) + cloop).Address(0, 0) & ")/2", is currently returning the form (N2+AZ2)/2 on column 64

How can transform the same code now to return 100*ABS(N2-AZ2)/(MIN(N2,AZ2)) as opposed to (N2+AZ2)/2 (transformation done and tested)

I'm so happy, I don't know how to thank you. Everything is working like a clock watch. I have even managed to apply your code to my actual worksheet and its magic!

 

[Joe4]

You are welcome!
Glad I was able to help and teach you some new things along the way.