+ Reply to Thread
Results 1 to 4 of 4

Round down to any number?

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Round down to any number?

    For some reason when i use round down function, it only rounds down properly if they are whole even numbers.

    If the value is exactly 23, round down gives me 22?

    If the value is exactly 24, round down gives me 24.

    Im trying to round down to any number for example.

    23.1 should round down to 23.
    Likewise 23.9 should round down to 23.

    Not sure what is going on here?

    I think i have attached the file. Formula values are in blue.
    Attached Files Attached Files

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

    Re: Round down to any number?

    value look "exactly" 23, but infact, it is 22.9999999999418000. To round it properly, just add a "very small" value (i.e, 6/100000000 of second (~ 10^-9) to change it:

    =ROUNDDOWN(J2+10^-9,0)
    Quang PT

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Round down to any number?

    Wow! I don't know what the ^ does, but this works perfectly. Thank you!!
    How did you know it wasn't exactly a whole number by the way?

    Thank you for your Help!

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

    Re: Round down to any number?

    Quote Originally Posted by Lostcar View Post
    Wow! I don't know what the ^ does, but this works perfectly. Thank you!!
    How did you know it wasn't exactly a whole number by the way?

    Thank you for your Help!
    Nice to hear it works.
    ^ means "exponential"
    10^1 = 10*1
    10^2 = 10*10 = 100
    10^-1 = 1/10
    10^-9 = 1/1,000,000,000
    To see the underlying value of cell, stay in that cell, move to formula bar, highlight the formula, the hit F9

+ 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 do I ROUND UP to a whole number
    By ericinarcata in forum Excel General
    Replies: 6
    Last Post: 04-08-2020, 03:23 PM
  2. [SOLVED] Trying to round down a number that has already been round up
    By louis_z in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-20-2017, 07:14 AM
  3. Replies: 2
    Last Post: 12-06-2014, 01:58 PM
  4. [SOLVED] Round number to nearest equal or lower number of a string
    By MikeMar! in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-20-2013, 01:26 PM
  5. Replies: 1
    Last Post: 06-28-2006, 12:26 PM
  6. [SOLVED] How can i round a number to closest tenth number?
    By rayne95 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-19-2006, 04:40 PM
  7. [SOLVED] How to make a number round up/down to a set number
    By David S in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-07-2005, 12:06 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