+ Reply to Thread
Results 1 to 5 of 5

Rounding when already an integer

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    Wellington, New Zealand
    MS-Off Ver
    2010
    Posts
    14

    Rounding when already an integer

    I have a column of prices, and want to round them down to the nearest lower $, however when I use ROUNDDOWN, if the price is already at 0c eg $164.00, it rounds it down to the next lowest integer (in this example $163). What formula can I wrap around my ROUNDDOWN so it leaves the number alone if it is already a whole number?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Rounding when already an integer

    If I understand correctly try something like
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Rounding when already an integer

    Hi Jo,

    I think your $164.00 must be a little less and you are simply displaying the closest integer. RoundDown will keep an number as it is, if it is already equal to that number. That is 164 rounds down to 164, it does not go down to 163 unless it isn't really 164 (but a little less).
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,668

    Re: Rounding when already an integer

    @Jo Ross.... Change your formula to something of the form =ROUNDDOWN(ROUND(A1,2),0).

    If you temporarily format the cell that appears to be 164.00 as Currency (or whatever) with 15 decimal places, I suspect that you will see that it is less than 164.00.

    It only appears to be 164.00 because your format rounds the appearance to only 2 decimal places.

    By explicitly rounding to 2 decimal places first, you will rounddown the value as it appears.

    Alternatively, if the value in A1 is calculated, it might be prudent to round the formula in A1.

    In general, if you expect a calculation to be accurate to some number of decimal places (2, in your case), you should explicit round to that number of decimal places.

    (Not to an arbitrarily larger number of decimal places, as some people suggest).

  5. #5
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Rounding when already an integer

    Quote Originally Posted by MarvinP View Post
    Hi Jo,

    I think your $164.00 must be a little less and you are simply displaying the closest integer. RoundDown will keep an number as it is, if it is already equal to that number. That is 164 rounds down to 164, it does not go down to 163 unless it isn't really 164 (but a little less).
    This is not how ROUNDOWN works for me, something must be off about your settings or value.

    Ex, value in A2, formula in B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A2= 164, formatted as currency, B2 = 164
    A2= 164.01, formatted as currency, B2 = 164
    A2= 163.99, formatted as currency, B2 = 163

    All of which is exactly what I expect.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

+ 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] Formula automatically rounding up integer
    By Bamagadbird in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 03-03-2014, 06:15 PM
  2. Replies: 2
    Last Post: 06-30-2013, 03:30 PM
  3. Rounding to a specific integer using formulas
    By doug@bunchesofbows in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2012, 12:31 PM
  4. Replies: 3
    Last Post: 10-27-2011, 05:20 PM
  5. Count the number of occurrences of an integer withing a larger integer
    By nnktran in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2010, 01:04 PM
  6. help with rounding up to whole integer
    By kjcramp in forum Excel General
    Replies: 3
    Last Post: 04-10-2006, 02:10 PM
  7. [SOLVED] Rounding to nearest integer
    By pattyh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-01-2005, 03:24 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