Simple range match formula

• 11/26/2022 at 2:44 PM #204852
  robdav

  Hi

  This should be simple, but I can’t work out how to do it!

  I’m trying to compare one value to another. Sometimes they are the same but sometimes there can be a difference of one, two or three…. so I need a formula along the lines of the below code. However, if the value can be different by 3 then that should also allow a difference of 1, 2 or 3 not just 3 if that makes sense?

  Many thanks for any help.

  Rob

  testsize = 3 if ((value1 = (value2 - testsize)) or (value1 = (value2 + testsize))) then backgroundcolor("red",50) endif

  1 2 3 4 5

  testsize = 3   if ((value1 = (value2 - testsize)) or (value1 = (value2 + testsize))) then backgroundcolor("red",50) endif

   

  

  robdav

  Participant

  Topics: 65

  Replies: 116

  Been thanked: 6 times
  11/26/2022 at 5:37 PM #204858
  JS

  Hi @robdav

  Do you mean something like that…?

  Difference between values

  Value1 = 3 Value2 = 0 xDiff = ABS(Value1 - Value2) If xDiff=1 then BackGroundColor(255,0,0) ElsIf xDiff=2 then BackGroundColor(0,255,0) ElsIf xDiff=3 then BackGroundColor(0,0,255) EndIf Return

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  Value1 = 3 Value2 = 0   xDiff = ABS(Value1 - Value2)   If xDiff=1 then BackGroundColor(255,0,0) ElsIf xDiff=2 then BackGroundColor(0,255,0) ElsIf xDiff=3 then BackGroundColor(0,0,255) EndIf   Return

  1 user thanked author for this post.

  robdav

  

  JS

  Participant

  Topics: 22

  Replies: 1412

  Been thanked: 554 times
  11/26/2022 at 5:59 PM #204861
  robdav

  Hi @JS

  Not quite. Let me try another example.

  Let’s say value1 = 104

  I want to compare it to another value (value2), if I allow a difference of three then value2 could be

  101, 102, 103 – 104- 105, 106, 107

  or difference of 1 to value2 could be

  103 – 104 – 105

  Obviously both value1 and value2 are variables.

  Does that make more sense?

  Thanks, Rob

  

  robdav

  Participant

  Topics: 65

  Replies: 116

  Been thanked: 6 times
  11/26/2022 at 6:18 PM #204862
  JS

  Hi @robdav

  Try this…

  Testing two values

  Value1 = 104 Value2 = 106 TestValue = 3 For i = 1 to TestValue If Value2 = Value1 + i or Value2 = Value1 - i then BackGroundColor(255,0,0) Break EndIf Next Return

  1 2 3 4 5 6 7 8 9 10 11 12 13

  Value1 = 104 Value2 = 106   TestValue = 3   For i = 1 to TestValue If Value2 = Value1 + i or Value2 = Value1 - i then BackGroundColor(255,0,0) Break EndIf Next   Return

  1 user thanked author for this post.

  robdav

  

  JS

  Participant

  Topics: 22

  Replies: 1412

  Been thanked: 554 times
  11/26/2022 at 6:36 PM #204864
  robdav

  Thanks @JS, it looks like it should work, I will test it tomorrow and confirm.

  1 user thanked author for this post.

  JS

  

  robdav

  Participant

  Topics: 65

  Replies: 116

  Been thanked: 6 times
  11/26/2022 at 9:27 PM #204872
  JS

  Hi @robdav

  Use “For i=0 to TestValue”

  Then you will also test whether Value2 is equal to Value1…

  

  JS

  Participant

  Topics: 22

  Replies: 1412

  Been thanked: 554 times
• Author
  Posts