Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Format a cell to ROUND off a number (Excel XP)

    I would like to alter my quote form to round off prices to the nearest half dollar. I know how to apply a formula to a cell to round off a number that gets put into that cell by formula, but that is not what I want to do here.

    In this case I have a column of cells on my quote form into which I manually enter a price. My price charts will give me pricing to the penny, and sometimes that looks absurd on a quoted item. For example, if a price comes up $ 847.63 cents, I would prefer to present it to the customer as $ 847.50.

    My keyboard has an integrated calculator that sends my calculations right to the cell. As such I have to retype the .63 as .50 every time this comes up........which is a lot.

    Can I FORMAT a cell to ROUND?

    Thanks,
    BH Davis

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format a cell to ROUND off a number (Excel XP)

    The basic answer no.

    Formatting only changes the way a number is displayed, not the stored number.

    If you wanted to change the presentation to whole dollars rather than 50 cents, you could format the cell to show zero decimal places. This would cause 847.63 to be displayed as 848.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format a cell to ROUND off a number (Excel XP)

    Thanks.........not the answer I wanted.......but now at least I know.

    BH

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Format a cell to ROUND off a number (Excel XP)

    Why not enter the price into one cell, and display or print it in a different cell that you Round to the correct precision?

    StuartR

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format a cell to ROUND off a number (Excel XP)

    I agree with Stuart. Your calculator can enter the numbers into (say the A column). So in the B column add the formula: =ROUNDDOWN(A1/0.5,0)*0.5

    This will cause the value to round down to the nearest half dollar. You can then hide the A column or copy and then paste values from B to A if you want to replace the old number with the value of the formula.
    Regards,
    Rudi

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format a cell to ROUND off a number (Excel XP)

    Thanks guys.........yes, a clever work around but there are space issues on the form that will prevent me from doing that.

    BH

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format a cell to ROUND off a number (Excel XP)

    If you can live with the amount being rounded toa whole number or a fixed number of decimal places, you can set the cell format, and tick the check box "Precision as displayed" in the Calculate tab of Tools | Options... With this setting (which operates at the workbook level and is stored with the workbook), numbers are stored the way they are displayed.

    For example, if a calculation yields 12.345 and the cell where it ends up is formatted as 0.0, the number will be displayed as 12.3.
    Normally, the value actually stored in the cell is 12.345 and this value is used in further calculations.
    But with "Precision as displayed" turned on, the value stored is 12.3, exactly as displayed ( the extra digits are lost irretrieveably), and 12.3 is used in further calculations.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format a cell to ROUND off a number (Excel XP)

    Thanks everyone. Seems like it's not going to happen. I could have it display to the nearest dime, but it would need to show a full dollars and 2 decimal cents to make sense. IE, $ 2.3 cannot substitue for $ 2.30.

    Thanks again.........all your attention and effort is always appreciated.

    BH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •