Conditional format with multiple criteria

[michaelbr]

I have a spreadsheet with 3 colums, column A has the values where I want to set the conditional formatting formula, B has the criteria min, C has criteria max, the trouble is either min max can have or not values, and column A can be a number or text, I was planning to use IF to check, but after a while, got too complicate, I’m pretty sure there’s a better/simpler way to do it. As example,

column A     column B       column C
5                    4                 10
3                                           7
4                    1                    
normal
negative

so the conditions would be

1. if the value in column A is between min max, then nothing change
2. if the value is outside (either higher or lower), highlight value in column A
3. if the value in column A is a text, then must be contained in a list (in this case normal and negative), otherwise highlight value of column A
4. if min/max has only 1 value, value in column A must be inside the value of min/max, for ex.: if A is 3, min “”, max=5, then no changes, but if the max is 2, then highlight value of column A.
   I hope not making if too confusing, my head already turn 3 times!!

[schiavinatto]

Attached file, see Conditional Formatting

michaelbr_ask_en.ods (24.7 KB)

[michaelbr]

Thanks for your quick reply.
ps: I forgot to include 1 additional condition, which is if both min/max are blank, then no changes on corresponding column A (tried several hours and couldn’t figure out how to implement it).

[schiavinatto]

michaelbr2_ask_en.ods (24.8 KB)

[michaelbr]

Thanks again for your help.