How do I stop numbers from rounding in Excel?

G

Guest

Using MS Excel 2003 Professional Edition...
I've created a time and attendance worksheet where people can enter their
hours in increments of .25 for 15-minute intervals. I can't format the cells
to sum numbers without rounding it to the next highest number.

This causes problems such as:
2.50 + 7.25 = 9.80 (when it should read 9.75)

I know that I could add decimal points to the format (7.250) to correct the
problem, but this makes the sheet messy and cluttered, and my boss doesn't
like it. He wants it to read 8, not 8.000 when it's just a single number.

Can anyone assist? Thanks for your time! :)
 
D

Dave Peterson

If your cell is formatted as General, try widening the column or reducing the
font size.
 
G

Guest

Hi pinmaster,

I've tried formatting my cells as General, and as text, but it still rounds
up for some reason.

Any other suggestions would be appreciated
 
G

Guest

Hi Dave,

Thanks for your reply. I've tried the General and the Text formating in the
sheet, and it still rounds up. As for other formatting with the decimal
places at .000, the problem there lies with the sheet. I really can't widen
the columns any more than they are, and reducing the font size makes it hard
to read.

There has to be some function or other alternative out there that I'm
missing. Any other suggestions would be appreciated... thanks!
 
P

pinmaster

Try Dave's suggestion of widening your column, excel will round up
numbers if the column is too narrow.

Regards
JG
 
D

Dave Peterson

I can only think of those ways--change the format away from General, widen the
column or reduce the font size. Maybe someone else will chime in with an
alternative.
 
P

Pete

You could possibly do it by converting it to text with higher number of
decimal places and trimming away the trailing zeroes, but other parts
of the spreadsheet would then have to be modified if they rely on it
being a number.

If the quarter hours were converted to proper hh:mm format they would
display correctly, but again formulae elsewhere would probably have to
be amended.

Pete
 
S

Stonehaven

I know what you mean... It gets really annoying and kinda messes up the
look of the worksheet when you have to widen the column. I was hoping
there would be option to turn the rounding associated with the cell
width, off. :(
 
G

gerberdude

The problem with rounding numbers with the INT function is that if you
try and use them then for another function it uses the integer instead
of the underlying number.

I have found that a great solution to this problem is to have your
calculations performed on a separate sheet, with all of the decimals
that you need.

Then set up another sheet just like it that displays all of the data
from the cells using the INT function. This way your equations
maintain their accuracy while the sheet that you are viewing is clean
and easy to read.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top