Conditional formating [3rd decimal is 1, 0 or 9]

English
, , ,
#1

Hello,

I have a spreadsheet that calculates numbers with 5 decimal places and I’d like to mark numbers (with for ex. red) that have 3rd decimal 1, 0 or 9.
For example if my formula exerts:
8,55927 - it will be marked red
6,26007 - it will be marked red
8,49249 - it won’t be marked red (or it will be marked with some other color)
6,21734 - it won’t be marked red (or it will be marked with some other color)

Is there any way to do this or I’m out of luck?
I guess if it’s possible it would be with conditional formating.

Thanks in advance!

#3

Try this formula in some helper cells, and the try to applya it in the CF feature:

=MID(TEXT(MOD(A1*100;1);"0.00000");3;1)

where A1 is the location of the first number. (The result is an one character Text.) If the decimal numbers always are smaller than 10, the the formula can be simplier.

#2

Hallo
Select the range in question, for example A1:A100 and:
⇒ Format ⇒ Conditional Format ⇒ Condition:

Formula:  =NOT(ISNA(REGEX(A1;",\d\d[019]")))

⇒⇒ your red style

3 Likes
#4

Elegant solution.
I use dot as decimal separator, so, instead of ", I used "\..

#6

Thank you very much!
Works flawlessly.

#7

@LeroyG , @karolus :
I have tested both: REGEX(A1;",\d… and/or REGEX(A1;".\d… and substituted the alternately notation english <-> deutsch without doubts! English with decimal point and deutsch with decimal comma. All that works correctly. So there have been some strange going-ons :thinking:. I havn’t tested numerals with normal separations in thousand unities with switched commas and points.

#5

arithmetic solution
a) 4th decimal place
9_LO-CALC_cut digit integers_10000.ods (24.0 KB)
b) 3rd decimal place
000_LO-CALC_cut digit integers_1000.ods (24.0 KB)

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.