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

margram

New Member
Joined
Jul 6, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDEFGHI
1KeepKeepKeepKeepKeepKeep
29502,0009502,00015,00015,000
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19<0000005000xxxxxxxTRUE
Sheet2
Cell Formulas
RangeFormula
B19B19=AND(LEFT(A19,1)="<",OR((D1:I1="Keep")*(MID(A19,2,10)+0>D2:I2)))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,753
Messages
5,597,925
Members
414,191
Latest member
debbhatta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top