+ Reply to Thread
Results 1 to 8 of 8

Formula for rounding and number management not working

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Formula for rounding and number management not working

    I have this formula:

    ROUND(L19*(IF(C19<=2.083, ROUNDDOWN(C19,0), IF(C19<=2.5,C19=2.5, ROUNDUP(C19,0)),2)

    In it i am trying to get a value based on hours. I want any hourly time which results in 0.083 of an hour or less to be rounded to the nearest whole number, any number which falls between 0.084 and 0.583 to be rounded to the nearest .5 and anything above 0.583 to be rounded up to the nearest whole number.

    The formula above doesn't work as i get the error that there are too many arguments...i am assuming it has a problem with the if statement being multiplied against the L19.

    Thanks for your help.

  2. #2
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Formula for rounding and number management not working

    ok i sort of figured this out:

    ROUND((L19*(IF(C19<=2.083, ROUNDDOWN(C19,0), IF(C19<=2.583,C19=2.5, ROUNDUP(C19,0))))),2)

    but i need to figure out how to only look at the decimal places and not the actual number to the left of the decimal, such that the formula would look something like this:

    ROUND((L19*(IF(C19<=x.083, ROUNDDOWN(C19,0), IF(C19<=x.583,C19=x.5, ROUNDUP(C19,0))))),2)

    Also i noticed the formula fails when the value is equal to 2.5 (it seems to give a value of 1), and anything below 2.083 or between 2.5 and 2.583 gives nothing but a "-".

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Formula for rounding and number management not working

    Hi and welcome to the forum

    1st thing I need to ask is - are you working with real time, or just numbers? Real excel time is a decimal of 1 (day), such that 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 06:00 PM) is 0.75

    Can you upload a sample of what you have and what you want?
    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

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

    Re: Formula for rounding and number management not working

    Quote Originally Posted by dominatro View Post
    I have this formula:
    ROUND(L19*(IF(C19<=2.083, ROUNDDOWN(C19,0), IF(C19<=2.5,C19=2.5, ROUNDUP(C19,0)),2)

    In it i am trying to get a value based on hours. I want any hourly time which results in 0.083 of an hour or less to be rounded to the nearest whole number, any number which falls between 0.084 and 0.583 to be rounded to the nearest .5 and anything above 0.583 to be rounded up to the nearest whole number.
    First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

    Second, it is unclear why you use 2.xxx in your formula, if you simply want to ROUNDDOWN or ROUNDUP based on fractional hours. I will rely on your verbal description.

    Ostensibly, we would like to write:

    =ROUND(L19*(INT(C19)+IF(MOD(C19,1)>35/60,1,IF(MOD(C19,1)>5/60,0.5))),2)

    MOD(C19,1) returns the fraction of an hour. INT(C19) is the same as ROUNDDOWN(C19,0). Adding 1 is the same as ROUNDUP(C19,0).

    However, if L19 is 100 and C19 is =12+5/60, for example, that formula results in 1250 instead of 1200 as intended. (Obviously, 5/60 is not >5/60.)

    That is due to internal (binary floating-point) arithmetic anomalies. I could explain further, if you wish.

    The following formula is more reliable:

    =ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)
    Last edited by joeu2004; 07-17-2014 at 05:29 AM. Reason: errata: rewrote more reliable formula; errata: >35/60

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Formula for rounding and number management not working

    @FDibbins it is the fraction of an hour not a day and not real time.

    Quote Originally Posted by joeu2004 View Post
    First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).

    It is rounded to 2 but I could change it to three, although fractions is probably better.

    Second, it is unclear why you use 2.xxx in your formula, if you simply want to ROUNDDOWN or ROUNDUP based on fractional hours. I will rely on your verbal description.

    The 2 part is unimportant. This excel sheet is for invoice purposes. We offer 5 minute grace periods on our charging rate and we charge by the half hour. So, for example, if we work for 3 hours and 4 minutes we want to only charge for 3 hours. If we work for 3 hours and 10 minutes we want to charge for 3.5 hours and if we work for 3 hours and 38 minutes we want to charge for 4 hours.

    Ostensibly, we would like to write:

    =ROUND(L19*(INT(C19)+IF(MOD(C19,1)>35/60,1,IF(MOD(C19,1)>5/60,0.5))),2)

    MOD(C19,1) returns the fraction of an hour. INT(C19) is the same as ROUNDDOWN(C19,0). Adding 1 is the same as ROUNDUP(C19,0).

    However, if L19 is 100 and C19 is =12+5/60, for example, that formula results in 1250 instead of 1200 as intended. (Obviously, 5/60 is not >5/60.)

    That is due to internal (binary floating-point) arithmetic anomalies. I could explain further, if you wish.

    The following formula is more reliable:

    =ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)

    It looks like this formula could work! Thanks!

    Yup it works perfectly! Thank you!

    One more question, my C19 cell is currently formatted as a number but it will be easier to enter in formulas of simple math, such as "=4 + (19/60)" is it best to keep the number format or convert it to general? Number at least confines the number to a specified number of decimal places so it doesn't look messy, but still calculates the real value right?
    Last edited by dominatro; 07-17-2014 at 09:02 AM.

  6. #6
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Formula for rounding and number management not working

    nevermind i found a fraction option

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

    Re: Formula for rounding and number management not working

    Quote Originally Posted by joeu2004 View Post
    First, it appears that C19 is time in decimal hours. It is unclear whether C19 has been rounded explicitly to 3 decimal places. If not, as I assume, it is improper for you to use decimal fractions like 0.083 (really 5/60) and 0.583 (really 35/60).
    Quote Originally Posted by dominatro View Post
    It is rounded to 2 but I could change it to three, although fractions is probably better.
    Then why are you comparing with values with 3 decimal places?

    I suspect there is a misunderstanding.

    When I wrote "rounded explicitly", I meant: using the ROUND function. Using your later example, I mean =ROUND(4 + 19/60,2) instead of simply =4 + 19/60.

    I suspect you are referring to formatting as Number (or something) with 2 decimal places.

    Usually [1], formatting alone affects only the appearance of the value. It does not change the actual value. For example, =4 + 19/60 might appear as 4.32 when formatted with 2 decimal places; but its actual value is about 4.31666666666667 (and not even that exactly). So IF(C19=4.32,TRUE) returns FALSE, despite appearances.

    [1] Re: formatting does not change the actual value. Unless you set the "Precision as displayed" option, which I do not recommend.

    Quote Originally Posted by joeu2004 View Post
    The following formula is more reliable:
    =ROUND(L19*(INT(C19)+IF(C19>INT(C19)+35/60,1,IF(C19>INT(C19)+5/60,0.5))),2)
    Quote Originally Posted by dominatro View Post
    Yup it works perfectly!
    If you explicitly round C19 using the ROUND function, it would behoove you to explicitly round comparative values as well.

    Although the formula above might work by coincidence (because 5/60 and 35/60 are greater than their rounded counterparts), in general the following is more reliable:

    =ROUND(L19*(INT(C19)+IF(C19>ROUND(INT(C19)+35/60,2),1,
    IF(C19>ROUND(INT(C19)+5/60,2),0.5))),2)

    Change ROUND(...,2) to ROUND(...,3) if you change C19 and round explicitly to 3 decimal places.

    Quote Originally Posted by dominatro View Post
    One more question, my C19 cell is currently formatted as a number but it will be easier to enter in formulas of simple math, such as "=4 + (19/60)" is it best to keep the number format or convert it to general? Number at least confines the number to a specified number of decimal places so it doesn't look messy, but still calculates the real value right?
    As noted above, how you format the value does not change it. So whether you format as General or as Number with 2 or 3 decimal places is a quality issue. It is up to you entirely.

    Usually, I prefer not to format as General, especially for public documents like invoices, since we have no control over the appearance of the result.

    Quote Originally Posted by dominatro View Post
    nevermind i found a fraction option
    I presume you refer to a Fraction format like the Custom format "0 ??/60" without quotes.

    Again, keep in mind that the Fraction format only affects appearance, not the actual value.

    Testing for more than x 5/60 and x 35/60 probably works.

    But beware of testing for equal to x 5/60 or x 35/60.

    Usually, the result of arithmetic will not be identically equal, despite appearances. Unless, again, you take steps to explicitly round the arithmetic results appropriately. For example,
    --TEXT(C19+C20,"0 ??/60").
    Last edited by joeu2004; 07-17-2014 at 03:31 PM. Reason: cosmetic

  8. #8
    Registered User
    Join Date
    07-16-2014
    Location
    montreal, canada
    MS-Off Ver
    2016
    Posts
    39

    Re: Formula for rounding and number management not working

    I'm not sure i completely follow, but the originally proposed formula works exactly as intended in all tested cases where C19 is less than x + 5/60, C19 is equal to x + 5/60, and C19 is greater than x + 5/60, furthermore it works perfectly the same for the sane tests with x + 35/60.

    So thanks for your help with that.

    Also i understand now the difference between what is being calculated and what is being shown. I also noticed that the invoice i had had a data valuation which i modified to allow for decimal numbers. (it was giving me an alert everytime i put a number other than an integer into the cell)

+ 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. Formula total rounding to even number
    By PlanBVA in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-08-2008, 01:56 PM
  2. No Rounding to whole number formula or format?
    By brandoncartwrig in forum Excel General
    Replies: 4
    Last Post: 09-10-2007, 05:21 PM
  3. What is the formula for rounding a number down?. . .
    By feralhog in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-30-2006, 02:37 PM
  4. Number rounding within a formula
    By T.R. Young in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2005, 11:05 AM
  5. rounding a formula's number
    By robert.everson@sci-us.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2005, 04: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