Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Josette_N
 
Posts: n/a
Default 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! :-)
  #2   Report Post  
Posted to microsoft.public.excel.misc
pinmaster
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?


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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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
  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
Josette_N
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
Josette_N
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
pinmaster
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?


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

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496935

  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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
  #9   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?

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

  #10   Report Post  
Posted to microsoft.public.excel.misc
Stonehaven
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?


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. :(


--
Stonehaven
------------------------------------------------------------------------
Stonehaven's Profile: http://www.excelforum.com/member.php...o&userid=30179
View this thread: http://www.excelforum.com/showthread...hreadid=496935



  #11   Report Post  
Posted to microsoft.public.excel.misc
gerberdude
 
Posts: n/a
Default How do I stop numbers from rounding in Excel?


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.


--
gerberdude
------------------------------------------------------------------------
gerberdude's Profile: http://www.excelforum.com/member.php...o&userid=30754
View this thread: http://www.excelforum.com/showthread...hreadid=496935

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can Excel evaluate a series of numbers Chad Sellers Excel Discussion (Misc queries) 6 November 11th 05 11:42 AM
How do I sort letters before numbers in Excel? RiverGirl Excel Discussion (Misc queries) 4 May 27th 05 04:09 PM
stop excel from rounding a result Hazeldean Excel Worksheet Functions 3 May 13th 05 10:42 PM
How do I stop Excel from changing simple numbers to their 1/100th. Chris C Excel Discussion (Misc queries) 1 December 14th 04 08:14 PM


All times are GMT +1. The time now is 10:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"