Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Rounding numbers in microsoft excel

  1. Dec 10, 2009 #1
    i need to round numbers in a spreadsheet 2 decimal points, in a way that:

    for a number with a.byX

    for x>5 i need to round upwards
    for x<5 i need to round downwards
    for x=5 i need to round up if y is odd and round down if y is even

    for example

    0.563~0.56
    0.566~0.57
    0.575~0.58
    0.565~0.56

    i tried using if, roundup, rounddown, floor,, cant seem to get it right
    any ideas
     
  2. jcsd
  3. Dec 10, 2009 #2

    Integral

    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Just set the cell format to show the number of digits you want. Setting the precision in the cell format does exactly what you need.
     
  4. Dec 10, 2009 #3
    no, i need it to round the numbers for me, i need them for calculations and i need them to be exacly the way i stated
     
  5. Dec 10, 2009 #4

    FredGarvin

    User Avatar
    Science Advisor

    There is always the ROUND function. You can also do a user defined function.
     
  6. Dec 10, 2009 #5
    but the round fuction always rounds 5 upwards,
    0.5->1
    1.5->2
    2.5->3
    3.5->4.....

    i need
    0.5->0
    1.5->2
    2.5->2
    3.5->4
    4.5->4

    always rounding to the even number,
    only that i need it to the 2nd decimal point no to the whole number
     
  7. Dec 10, 2009 #6
    As long as you're only dealing with rounding numbers with 3 decimal places down to 2 decimal places, this will work:

    =IF((RIGHT(A1,1)<>"5"),ROUND(A1,2),IF(ISODD(A1*100),ROUND(A1,2),TRUNC(A1,2)))

    Enter this formula in any cell except A1.
    Enter your number in cell A1.

    To make this work for other types of numbers, I would write a user defined function in VBA.
     
  8. Dec 10, 2009 #7
    Your example seems to be flawed. Shouldn't 0.566~0.56 , according to your specification?
     
  9. Dec 10, 2009 #8
    The example's not flawed, the explanation is a bit unclear though.

    "b" refers to the 1st digit to the right of the decimal point
    "y" refers to the 2nd
    "x" refers to the 3rd

    His rules state that for "x" > 5, round upward, therefore in 0.566,
    "x" is equal to 6 which is greater than 5, so round upward to 0.57
     
  10. Dec 10, 2009 #9
    I see. sorry. In hindsight I see that it was perfectly explained all along.
     
  11. Dec 10, 2009 #10
    I've already written VBA code for this.
    Let me know if you're interested.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Similar Discussions: Rounding numbers in microsoft excel
Loading...