Comparing a less than value (<100) to a normal value (90) in conditional formating

[margram]

Hi,

I'm working on a conditional formatting formula that compares a value that is a less than value (e.i <100) to to a normal value (e.i. 90). In simplest terms my formula is trying to bold a cell if the number in the less than value (100) is greater than 90. I'm essentially using the following, but it seems to be working intermittently…. =MID(A1,2,10)>"90"

My actual formula is this:
=AND(OR(AND($D$1="Keep",MID(A19,2,10)>"950"),AND($E$1="Keep",MID(A19,2,10)>"2000"),AND($F$1="Keep",MID(A19,2,10)>"950"),AND($G$1="Keep",MID(A19,2,10)>"2000"),AND($H$1="Keep",MID(A19,2,10)>15000),AND($I$1="Keep",MID(A19,2,10)>15000)),MID(A19,1,1)="<")

Ignoring the =Keep portion (in this scenario all D1-I1 have keep), this is trying to find if a less than value could potentially be bigger than an pre-determined value (950, 2000, 15000. in this case the lowest value (950)), however it does not work properly. It will bold everything with a less than sign over 150 up to 1000 and everything over 1500 up to 10000, and 15000 up to 100000, so on and so forth.

Strangely enough I have this same formula working for a bunch of other cells that have pre-determined numbers that aren't as high (0.1, 1, 10).

Any help would be much appreciated, thanks! Also if there is any easier way to do this that would be awesome!

 

[StephenCrump]

Your immediate problem is that you're comparing strings, rather than numbers. To give a simple example, evaluating ="10">"2" will return FALSE.

For transparency and robustness, I'd be inclined to build the values into Excel, rather than just the formula. Perhaps something like:

	A	B	C	D	E	F	G	H	I
1				Keep	Keep	Keep	Keep	Keep	Keep
2				950	2,000	950	2,000	15,000	15,000
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19	<0000005000xxxxxxx	TRUE
Sheet2

Cell Formulas
Range	Formula
B19	B19	=AND(LEFT(A19,1)="<",OR((D1:I1="Keep")*(MID(A19,2,10)+0>D2:I2)))

 

[margram]

Thanks, this is very helpful

 

[StephenCrump]

Thanks, this is very helpful

Great, thanks for posting back.