Compare five values
 

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.

Compare five values
 

How about if 4 of the numbers are within 0.1 :

1.000
1.001
1.002
1.003
99.99

or if all 5 numbers are within 0.1:

1.000
1.001
1.002
1.003
1.004
--
Gary''s Student - gsnu200750


"chemtyra" wrote:

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.

Compare five values
 

Hello,
sorry yes, I forgot to specify that at least three of the numbers must be
within 0.1. If there are more than 3, I suppose I would like the 3 closest
highlighted but that is not critical. Thank you
Tyra

"Gary''s Student" wrote:

How about if 4 of the numbers are within 0.1 :

1.000
1.001
1.002
1.003
99.99

or if all 5 numbers are within 0.1:

1.000
1.001
1.002
1.003
1.004
--
Gary''s Student - gsnu200750


"chemtyra" wrote:

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.

Compare five values
 

This is going to be in a worksheet with many clusters of five numbers. I
tried setting it up as a and/or function, but I exceeded the 1024 character
limit in the formula. I figured I need to program a loop to do this but don't
really know where to start. I appreciate any help you can give me.

"chemtyra" wrote:

Hello,
sorry yes, I forgot to specify that at least three of the numbers must be
within 0.1. If there are more than 3, I suppose I would like the 3 closest
highlighted but that is not critical. Thank you
Tyra

"Gary''s Student" wrote:

How about if 4 of the numbers are within 0.1 :

1.000
1.001
1.002
1.003
99.99

or if all 5 numbers are within 0.1:

1.000
1.001
1.002
1.003
1.004
--
Gary''s Student - gsnu200750


"chemtyra" wrote:

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.

Compare five values
 

This is just an example. Consider five cells (A1 thru A5)
We must example the triplets in these cells. Fortunately there are only 10
triplets in a group of five cells:

1,2,3
1,3,4
2,3,4
1,2,4
1,4,5
2,4,5
3,4,5
1,3,5
2,3,5
1,2,5

The following code examines each of the triplets to see if the values are
"close together". As soon as a good triplet is found, the cells are marked
and the routine finishes. If no good triplet is found, no cells are marked:

Sub tyre()
' gsnuxx
Range("A1:A5").Interior.ColorIndex = xlNone
i = Array(1, 1, 2, 1, 1, 2, 3, 1, 2, 1)
j = Array(2, 3, 3, 2, 4, 4, 4, 3, 3, 2)
k = Array(3, 4, 4, 4, 5, 5, 5, 5, 5, 5)
For l = 0 To 9
v1 = Range("A" & i(l)).Value
v2 = Range("A" & j(l)).Value
v3 = Range("A" & k(l)).Value
d1 = Abs(v1 - v2)
d2 = Abs(v1 - v3)
d3 = Abs(v2 - v3)
m = Application.WorksheetFunction.Max(d1, d2, d3)
If m < 0.1 Then
MsgBox (l)
Cells(i(l), "A").Interior.ColorIndex = 36
Cells(j(l), "A").Interior.ColorIndex = 36
Cells(k(l), "A").Interior.ColorIndex = 36
Exit Sub
End If
Next
End Sub
--
Gary''s Student - gsnu200750


"chemtyra" wrote:

This is going to be in a worksheet with many clusters of five numbers. I
tried setting it up as a and/or function, but I exceeded the 1024 character
limit in the formula. I figured I need to program a loop to do this but don't
really know where to start. I appreciate any help you can give me.

"chemtyra" wrote:

Hello,
sorry yes, I forgot to specify that at least three of the numbers must be
within 0.1. If there are more than 3, I suppose I would like the 3 closest
highlighted but that is not critical. Thank you
Tyra

"Gary''s Student" wrote:

How about if 4 of the numbers are within 0.1 :

1.000
1.001
1.002
1.003
99.99

or if all 5 numbers are within 0.1:

1.000
1.001
1.002
1.003
1.004
--
Gary''s Student - gsnu200750


"chemtyra" wrote:

Hello,
I have an excel file that will be made up of clusters of five numbers. I
want a macro to check if 3 of those 5 numbers are within 0.1 of each other.
If yes highlight those three numbers green. If not highlight all five numbers
red.
Thank you for your help.