+ Reply to Thread
Results 1 to 5 of 5

Conditional formating for decimal places

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    Vilnius
    MS-Off Ver
    Excel 2007
    Posts
    5

    Conditional formating for decimal places

    Hello Everyone,

    Sorry for bothering you but I think this is my last resort.

    The thing is that I have various numbers and percentage which have about 12 digits after "," example: 33,4999084472656% or 12,4649963378906.
    Now the thing is that I need to compare these figures with updated once which differ relatively little.

    However in the presentation just rounded numbers are shown (lets say in the cell we have 33,4999084472656% but it is shown as 33.5%).

    The question is how to highlight them if the number has changed from 33,5 to 33,4 or 33,6 and so on? This however does not apply if any other number after the comma has changed except the very first one, which is seen during the overview.

    I've tried entering formula in CF =Round($A$1;1)<>ROUND($B1$1;1) though it seems excel doesn't like it.

    I would be grateful for your tips and help!

    Thank you in advance!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Conditional formating for decimal places

    Round wont help. try this

    =--TEXT(A1,"#.0")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Conditional formating for decimal places

    welcome to the forum, celphtitled. i think your decimal points are commas & your separators for formulas are semi-colons? just to let you know, most of us are using period & commas, so don't get confused. i don't really understand the question because of this:
    This however does not apply if any other number after the comma has changed except the very first one, which is seen during the overview.
    so if
    A1:
    33,4999084472656%
    B1:
    33,4499084472656%
    it should NOT be highlighted? if so, try:
    =ROUNDDOWN(A1,3)<>ROUNDDOWN(B1,3)

    if it should be highlighted because A1 rounds to 33.5 & B1 33.4, then your formula is correct. except that this scenario is dealing with percentages. 33.5% is not 1 decimal point. it's actually 0.335. that's 3 decimal points
    =ROUND(A1,3)<>ROUND(B1,3)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Conditional formating for decimal places

    Welcome to the forum

    Not sure if this is a typo or not?
    =Round($A$1;1)<>ROUND($B1$1;1)

    Should that be...
    =Round($A$1;1)<>ROUND($B$1;1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    Vilnius
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Conditional formating for decimal places

    Thank you all for your comments!

    Benishiryo your tip worked out perfectly, I disregarded that it was percentage that is why it did not work.

    Thank you again

+ 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. [SOLVED] Control decimal places used for conditional formating
    By RANDY LIPOSKY in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2013, 07:32 AM
  2. conditional decimal places?
    By naat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2013, 03:10 AM
  3. Conditional Number Format for varying decimal places
    By justme23 in forum Excel General
    Replies: 3
    Last Post: 04-30-2010, 03:31 PM
  4. conditional formatting for 0 in 2 decimal places
    By mingali in forum Excel General
    Replies: 11
    Last Post: 02-27-2010, 05:34 AM
  5. Help with formating cells (decimal places)
    By madhead4000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2006, 04:40 PM

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