How do I stop numbers from rounding in Excel?

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! :-)

Try formatting your cells as "General"

HTH
JG

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

Josette_N wrote:
>
> 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! :-)

--

Dave Peterson

In fact, you could use:

Format|Cells|Alignment tab|check shrink to fit.


Josette_N wrote:
>
> 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! :-)

--

Dave Peterson

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

"pinmaster" wrote:

>
> Try formatting your cells as "General"
>
> HTH
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=496935
>
>

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!

"Dave Peterson" wrote:

> If your cell is formatted as General, try widening the column or reducing the
> font size.
>
> Josette_N wrote:
> >
> > 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! :-)
>
> --
>
> Dave Peterson
>

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

Regards
JG

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.

Josette_N wrote:
>
> 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!
>
> "Dave Peterson" wrote:
>
> > If your cell is formatted as General, try widening the column or reducing the
> > font size.
> >
> > Josette_N wrote:
> > >
> > > 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! :-)
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

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.

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.