+ Reply to Thread
Results 1 to 5 of 5

Rounding the number produced by an evaluated expression to the nearest number in CF

  1. #1
    Registered User
    Join Date
    01-15-2012
    Location
    Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    38

    Rounding the number produced by an evaluated expression to the nearest number in CF

    Hi, me again

    I am doing conditional formatting on a cell which shows a percentage from a simple calculation

    Please Login or Register  to view this content.
    B136 is a maximum score possible, F142 is a target %age rate (currently 88%) and C136 is the actual score achieved.

    I want to attribute C136 GREEN if the actual score (C136) is >= the evaluated %age score and RED is < the evaluated %age score

    I'm trying to fit in the ROUNDUP expression with for no decimal places but failing miserably.

    My problem is when the truncated actual score is nearly at the 88% rate but actial value is fractionally less as in

    HTML Code: 
    because the %age is actually 87.98646 so the cell highlights with a RED fill rather than a GREEN

    How do I include the ROUNDUP function to no decimal places in my conditional formatting expression?

    Please Login or Register  to view this content.
    thaaks

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Rounding the number produced by an evaluated expression to the nearest number in CF

    use the CEILING function.

    =C136 >= CEILING((B136 * $F$142),1)
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Rounding the number produced by an evaluated expression to the nearest number in CF

    Just FYI:

    FLOOR rounds down, and CEILING rounds up.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Rounding the number produced by an evaluated expression to the nearest number in CF

    88% = 0.88 so to round to the nearest percentage point you need to round to 2 decimal places, so try

    =C136>= ROUND(B136*$F$142,2)
    Audere est facere

  5. #5
    Registered User
    Join Date
    01-15-2012
    Location
    Hampshire
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Rounding the number produced by an evaluated expression to the nearest number in CF

    Thanks everyone - the expression that worked for me was

    Please Login or Register  to view this content.
    My F142 hidden cell is formatted as a percentage to no decimal places so the zero argument works - also I needed an extra pair of brackets daddylonglegs - but all your help helped me if you see what I mean. Thanks again everyone

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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