Answering my own Q: It was because I was using text fields, so whenever the 'number' was larger but the string contained lower denomination integers, it would be evaluated as a smaller number.
There remains a problem because some records need to appear as text. Is there a way to have a text field but evaluate 'numeric' values as numbers?
GetAsNumber ( text )
That works, also I found you could use Evaluate(text expression).
Is one likely better than the other??
It depends on what data you might get in that field.
Things to keep in mind:
GetAsNumber ( text ) will return "25" in each of the following instances:
These records which need to appear as text, can you give an example of what the values might be?
Also, in your conditional formatting, do you want the blue fill ONLY when both Rx and Req are numbers and RX<Req?
Evaluate will work just as well.
It is a powerful function with many twists and turns.
It should produce the desired output in most mathematical and logical operations.
Also it seems it has quite some smarts.
Let's take two parameters both stored as text
a = "2"
b = "4 + 4 - 2"
Evaluate ( a + b ) = 444 evaluates b as number 442 + 2 = 444
Evaluate ( a & b ) = 26 evaluates b as its own calculation 2 & 6 = 26
Probably more elegant to use Evaluate instead of 2x GetAsNumber in this case.
Let's say you have:
Rx = "25"
Req = "2 but NO MORE THAN 6"
GetAsNumber (Rx) would give you 25.
GetAsNumber (Req) would give you 26.
25 would be less than 26, so the cell would be turned blue using your conditional formatting formula. That seems dangerous.
It would probably be wise to compare only valid Rx numbers to valid Req numbers, and if that is what you want, then you could use something like this:
GetAsText (GetAsNumber (Rx)) = Rx and GetAsText (GetAsNumber (Req)) = Req and Rx < Req
In the above example, the pieces would evaluate to:
GetAsText (GetAsNumber (Rx)) would be "25"
Rx would be "25"
Rx would be recognized as a valid number.
GetAsText (GetAsNumber (Req)) would be "26"
Req would be "2 but NO MORE THAN 6"
Req would be recognized as NOT being a valid number.
So as a result, the condition would recognize that one of the values wasn't a number and the condition would evaluate as false, the cell would not turn blue, and you wouldn't be comparing apples to oranges. This would also correctly handle cases where Rx = 25 and someone entered Req = 025, for example.
From what I can see from your initial follow up to your post, you want to only compare numeric values to numeric values. By comparing in the bolded way above, I think your conditional formatting would be more "trustable".
I agree, I'd need to guard against 'GetAsNumber' sifting numbers from what should remain a text string. Although I don't have text records containing numbers, it's possible, so not worth the risk.
However, as mentioned above, would Evaluate(text expression) not do the job without tripping?
Would your conditional expression then become:
Evaluate (Calcs::Rx ) < Evaluate (Calcs::Req)
You'd still get some things turning blue that shouldn't turn blue, and you might still be comparing a number to some text. If Rx contained 50 and Req contained "Hello", you'd be comparing 50 to Hello. But you know your data better than I do. Maybe that wouldn't be a problem, maybe it would.