Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming | |||
Compare Listbox values with Collection values | Excel Programming | |||
compare values between workbooks and copy values | Excel Programming |