Conditional formatting

We have a spread sheet with a column that contains numbers in the format of 0000000.000. 7 numbers dot 3 numbers. some columns erroneously contain excess digits. For example 00000000.000 (8 numbers dot 3 numbers) or 7 numbers dot four. Is there any way to use conditional formatting-Hightlight cell rules to expose which cells contain excessive digits.
 
Thanks
 
 
 
Lee
BrazileeAsked:
Who is Participating?
 
sah18Commented:
This doesn't use conditional formatting, but it will give you the information you're looking for.
You can add these formulas into two new empty columns in your spreadsheet:

This formula will return a True if the number of digits to the left of the decimal (for the value in A1) is more than 7 digits:
=LEN(MID(A1,1,FIND(".",A1)-1))>7

This second formula will return a True if the number of digits to the right of the decimal (for the value in A1) is more than 3:
=LEN(MID(A1,FIND(".",A1)+1,LEN(A1)))>3

Place these on row 1 and copy down.

You may also be able to use these to apply conditional formatting, but I didn't give that a try.

Let me know if this helps, or if you have any questions on how to use these.


0
 
Richard DanekeTrainerCommented:
Yes.   Add a new rule to highlights cells greater than the maximum of 7 digits (9999999.999).
0
 
Michael FowlerSolutions ConsultantCommented:

For the examples with greater than 4 numbers after the decimal point, instead of using conditional formatting just select the row and format the cells to the format.

This article gives a good run down on the options available when formatting numbers

http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx

No formatting will help where the number of digits before the decimal point is greater that 7 to use conditional formatting
1/ Select the columns
2/ Select the Home tab
3/ Select Conditional Formatting on the Styles Panel
4/ Select Highlight cells rules
5/ Select more rules
        Format Only Cells That Contain
        Cell Value
        greater than or equal to
        100000000

Michael
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
BrazileeAuthor Commented:
Hello Sah18;

This looks to be the right track. If my column starts at B2 would i modify the formula like this.
=LEN(MID(B2,2,FIND(".",B2)-2))>7?

I tried this and it returns a result of "false" which I guess its supposed to. If I modify
the the cell to exceed the limit, it returns a "value" result. is this correct?

thank you
0
 
Richard DanekeTrainerCommented:
The formulas do work and can be used in the conditional formatting rules.
Details are provided in the attached example.
 ConditionalForatting.xlsx
0
 
Richard DanekeTrainerCommented:
In the example, the conditional formatting is applied in column A.
The two formulas are shown in column B and C for example use only.  They can be deleted and the conditional formatting will still work.
My first answer did not look close enough to see that you need a upper limit and a decimal count test.
0
 
sah18Commented:
Hi Lee,

Yes, just replace A1 with whatever your cell is that has the value you wish to test (B2, if it is in the 2nd row, 2nd column).  "value", though, means you have something wrong in the formula, so you may want to make certain you don't have any typos there.

If you're able to, feel free to attach a copy of your actual spreadsheet (or an abbreviated version of it), and I can check what you have entered for your formulas.  I could also see then if something else unexpected is tripping up the formula.

Take care,
sah18
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.