+ Reply to Thread
Results 1 to 3 of 3

ROUND 5 up/down according to the next digit

  1. #1
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    ROUND 5 up/down according to the next digit

    ROUND will round the numbers 1-4 down and 5-9 up.

    So, for instance, if I have 4.2253 at cell A1 with the formula =ROUND(A1,2), the result would be 4.2300. However, I want the function to look at the next digit ("3") if the digit that determines the rounding ("5") is 5. So "3" rounds "5" down resulting in 4.2200 instead.

    Here are a couple examples of what I mean:

    number > result using ROUND with num_digits=2 > result I desire

    4.2210 > 4.2200 > 4.2200
    4.2260 > 4.2300 > 4.2300
    4.2251 > 4.2300 > 4.2200 (logic: if 0.00X is 5 look at 0.000X and round 1-4 down 5-9 up)
    4.2256 > 4.2300 > 4.2300 ("6" rounds "5" up to 6 which rounds "2" up to 3)
    4.2255 > 4.2300 > 4.2300 ("2nd 5" rounds "1st 5" up to 6 which rounds "2" up to 3)

    Is there a formula for this? I've been trying to figure this out for hours lol, please help.
    Last edited by wharles62; 04-19-2020 at 01:07 PM.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,899

    Re: ROUND up/down at 0.005

    Try this

    =ROUND(A1-0.0005,2)

  3. #3
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Re: ROUND up/down at 0.005

    Brilliant! 4.2251-0.0005 makes 4.2246, then rounds "4" down as usual to 4.2200. Likewise, 4.2256-0.0005 makes 4.2251 then rounds "5" up to 4.2300.

    Works like a charm Thank You

+ 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] How to PRESENT round-down, but not perform round-down function?
    By superlative in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2019, 12:06 PM
  2. [SOLVED] Custom formulas that will round up when a condition is met or round down
    By cinstanl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2016, 01:32 PM
  3. Replies: 2
    Last Post: 12-06-2014, 01:58 PM
  4. [SOLVED] How to round to nearest .01 and round down if thousands place is .005
    By dredre609 in forum Excel General
    Replies: 5
    Last Post: 09-29-2014, 11:47 AM
  5. [SOLVED] Conditional Round up or Round up Function
    By cdmterence in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-18-2013, 06:03 PM
  6. Counting to a specified numberin round, then add one to round
    By McG_84 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-10-2013, 04:20 PM
  7. [SOLVED] How do I ROUND() round off decimals of a column dataset?
    By Højrup in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2005, 07:06 AM

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