+ Reply to Thread
Results 1 to 11 of 11

FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

  1. #1
    Registered User
    Join Date
    04-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    5

    Lightbulb FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Hello team - I am a noob and would love some help with a formula It will be used to determine which cells to format, under Conditional Formatting Rule..
    Problem:
    1. I have received a column of data in rows [General format with no specific number format].
    2. My task is to approve the data based on set requirements.
    3. The only requirement is for this data to consist of 3 decimal places.
    example:
    1.221
    1.189
    1.22
    1.24
    1.254
    1.269
    Desired Result:
    1.221
    1.189

    1.22
    1.24

    1.254
    1.269

    Any help would be greatly appreciated

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    For green:

    =MOD(A1*1000,10)<>0

    red: set as defaul colour, or use below:

    =MOD(A1*1000,10)=0
    Quang PT

  3. #3
    Registered User
    Join Date
    04-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    5

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Thanks for your reply
    Unfortunately it did not work for me :/
    See result Screenshot 2021-04-29 121751.png

  4. #4
    Registered User
    Join Date
    04-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    5

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Hi, can anybody help me with a formula?
    I've attached a sample sheet which shows what outcome I'm hoping to achieve.
    Basically, all numbers need to be 3 decimal places or more, to comply and be formatted a particular way (green)
    If not, they do not comply and need to be formatted a different way (red).
    Any help with this would be greatly appreciated
    Rowan
    Attached Files Attached Files

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    It works for me.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    5

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Thanks for testing the example sheet
    Yes, I agree the formula works for that however, for some reason, the formula is not working on my raw data :/
    Would you mind trying the attached sheet?
    Thanks in advance!
    Rowan
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    If x = 1.24000531974683, MOD(x*1000,10) = 0.00531974683 <> 0.

    If the OP's data were text, the best test would be =MID(A1,FIND(".",A1)+3,1)<>"". If the OP's data is numeric, then only fractional values which would be 0 on 1/1000 place AND ALL lower magnitude decimal places would be .0, .25, .5 and .75. ALL OTHER fractional parts with 0 in the 1/1000 place have nonzero values in lower magnitude decimal places.

    What you probably meant was =INT(MOD(A1*1000,10)).

  8. #8
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Your numbers are text. Try

    CORRECTED

    B2 conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by hrlngrv; 05-07-2021 at 01:18 AM. Reason: correction

  9. #9
    Registered User
    Join Date
    04-28-2021
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    5

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Quote Originally Posted by hrlngrv View Post
    Your numbers are text. Try

    CORRECTED

    B2 conditional formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hi there, thank you for your help!
    I am receiving this Excel error message when using this formula:
    'You may not use reference operators (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria.'
    It simply won't allow me to use it :/
    A bit more background: These sheets are exported data from 3D models in which the metadata is required to conform to pre-determined standards.
    This is part of an audit process which is then reported back to the modelling teams for updating of their models.

    I think it's acceptable that, for each export, I re-format these particular cells so that they become NUMBERS with 3 Decimal places.
    The values are often a combo of small and large and while I could simply eyeball the values and pick/select the ones which comply/don't comply, there are some enormous models, so this is quite time consuming.
    It would be useful if the values containing 3 decimals or more, turned green and the other's turned red, via conditional formatting.
    I'm sure Excel can do this I'd love some more help please..
    Rowan
    Last edited by auditor.non.general; 05-07-2021 at 02:22 AM.

  10. #10
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    Clarification: presumably 1.234 would have exactly 3 decimal places while 1.2345 would not; however, if an entry appeared as 1.23, should it be considered the same as 1.230? If 1.25 and 1.250 should be considered different values and only the latter having exactly 3 decimal places, then you can't convert these from text to numeric.

    You could define a name like x referring to the formula ={1;2;3;4;5;6;7;8;9}. Then change conditional formatting formulas to use x rather than the literal array. Here's an example.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,188

    Re: FORMULA - Conditional Formatting 'good' values with 3 decimal places NOT round, trunc

    If all your data can be exported as NUMBERS then a possibility is converting the text to numbers:

    =ROUND(B2*1,3)

    There is degree of "artificiality" about this as 1.22 (TEXT) is still 1.220 as a number so having text as "1.220" achieves little (in my view) as the modelling must (?) use numeric values.
    Last edited by JohnTopley; 05-07-2021 at 03:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting - Decimal Places
    By Rimmers UK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-16-2018, 04:04 AM
  2. [SOLVED] Round all values in a column to two decimal places
    By The Phil in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-09-2015, 03:09 PM
  3. [SOLVED] How to round off all the values with 0.000999 into 3 decimal places?
    By raimy haidar in forum Excel General
    Replies: 8
    Last Post: 02-12-2015, 02:59 AM
  4. [SOLVED] Have formula results/textbox to round value at 0 decimal places
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2015, 09:58 AM
  5. Replies: 1
    Last Post: 01-14-2014, 07:39 AM
  6. conditional formatting for 0 in 2 decimal places
    By mingali in forum Excel General
    Replies: 11
    Last Post: 02-27-2010, 05:34 AM
  7. Round to two decimal places
    By golfnutte in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2007, 05:48 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1