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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

BH

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

BH

Reply | Quote

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.

Reply | Quote

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

Reply | Quote