Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Excel won't stop rounding numbers. Please help

First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Excel won't stop rounding numbers. Please help

I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.


Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Excel won't stop rounding numbers. Please help

If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson


--

Dave Peterson


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

Dave, you were right. I did the F2, F9 thing to the 6.33% cell and it
returned 0.0633299284984677 and the 2937 is actually 2937. However; I'm
still not understanding why 0.0633299284984677 * 2937would give me the
rounded number if that cell isn't actually rounded. I'm so lame! :)




Dave Peterson wrote:
If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson


--

Dave Peterson


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Excel won't stop rounding numbers. Please help

I put this in a cell:
=0.0633299284984677 * 2937

And it came out to 186 exactly.

In Windows Calculator, it came out to:
185.9999999999996349

Since excel only 15 significant digits, 186 is the best it can do.

wrote:

Dave, you were right. I did the F2, F9 thing to the 6.33% cell and it
returned 0.0633299284984677 and the 2937 is actually 2937. However; I'm
still not understanding why 0.0633299284984677 * 2937would give me the
rounded number if that cell isn't actually rounded. I'm so lame! :)

Dave Peterson wrote:
If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

Ohhhhh... That is making complete sence now.

Thanks again for all the help Dave. I really appreciate it.


Dave Peterson wrote:
I put this in a cell:
=0.0633299284984677 * 2937

And it came out to 186 exactly.

In Windows Calculator, it came out to:
185.9999999999996349

Since excel only 15 significant digits, 186 is the best it can do.

wrote:

Dave, you were right. I did the F2, F9 thing to the 6.33% cell and it
returned 0.0633299284984677 and the 2937 is actually 2937. However; I'm
still not understanding why 0.0633299284984677 * 2937would give me the
rounded number if that cell isn't actually rounded. I'm so lame! :)

Dave Peterson wrote:
If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

Ohhhhh... That is making complete sence now.

Thanks again for all the help Dave. I really appreciate it.


Dave Peterson wrote:
I put this in a cell:
=0.0633299284984677 * 2937

And it came out to 186 exactly.

In Windows Calculator, it came out to:
185.9999999999996349

Since excel only 15 significant digits, 186 is the best it can do.

wrote:

Dave, you were right. I did the F2, F9 thing to the 6.33% cell and it
returned 0.0633299284984677 and the 2937 is actually 2937. However; I'm
still not understanding why 0.0633299284984677 * 2937would give me the
rounded number if that cell isn't actually rounded. I'm so lame! :)

Dave Peterson wrote:
If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Excel won't stop rounding numbers. Please help

Ohhhhh... That is making complete sence now.

Thanks again for all the help Dave. I really appreciate it.


Dave Peterson wrote:
I put this in a cell:
=0.0633299284984677 * 2937

And it came out to 186 exactly.

In Windows Calculator, it came out to:
185.9999999999996349

Since excel only 15 significant digits, 186 is the best it can do.

wrote:

Dave, you were right. I did the F2, F9 thing to the 6.33% cell and it
returned 0.0633299284984677 and the 2937 is actually 2937. However; I'm
still not understanding why 0.0633299284984677 * 2937would give me the
rounded number if that cell isn't actually rounded. I'm so lame! :)

Dave Peterson wrote:
If you see 186.00 in that cell, then I'm gonna guess that the numbers you said
you multiplied:

6.33% * 2937

Weren't really those numbers. I'm betting that these are rounded in the
display--not the real value, just the display.

If you go back to each of those cells and do the same F2|F9 thingy, you'll see
that at least one of those values isn't exactly 6.33% or exactly 2937.

As for the =b2-c2 showing the formula--not the results. It sounds like that
cell that held the formula was formatted as Text.

Try changing the format of that cell to General
Then hit F2 (to edit)
and then hit enter (to force excel to notice your change).

wrote:

I just tried the suggestions:

When I hit F2 then F9 I see 186.00 in the number bar.

I also set FormatCellsNumberNumber to 2 decimals again, and did not
have ToolsOptionsEdit Fixed decimal places checked at all. I tried to
check it and it didn't help. Dang.

I'm wondering if my excel, is just really buggy. Yesterday I typed a
simple formula into a cell =B2-C2 and instead of calculating it, it
just displayed the formula. I hade to copy the format from another row,
then do it again to have it caculate. I tried copying the format from a
cell that wasn't rounding into my cell that wouldn't stop rounding and
unfortunatley it did not work. All this after years of not having any
of these problems with excel.

Gord Dibben wrote:
I have a question.

Where have you set the number format to 2 decimal places?

Should be in FormatCellsNumberNumber and set for 2 DP.

Maybe you are set for 0 DP

Did you perhaps go into ToolsOptionsEdit and set "Fixed Decimal Places" to 2?

Normally that would give you 12.34 when you entered 1234 but in a formula the FD
places does not kick in.


Gord Dibben MS Excel MVP

On Sat, 16 Dec 2006 11:08:37 -0600, Dave Peterson
wrote:

I've never seen a bug like this in excel.

If you select the cell
then hit F2 (to edit that cell)
followed by F9 (to convert it to a value)
What do you see in the formulabar?

And hit escape to discard those chanes (or edit|Undo if you hit enter).

I'd double check the numberformat and the columnwidth once more.

wrote:

Thanks for the response Dave.

Sorry for the confusion, I meant to put 6.33% * 2937 NOT 2097. Arg!

My columns are wide enough to display the two decimal places, and I'm
currently in General. I'm really starting to think its just a bug in
excel, which is driving me nuts.

Please, if anyone has any other suggestions, let me know. Once I
acutally figure this out, this sheet is going to save me a lot of time.
Let me know if you need any other info.

Dave Peterson wrote:
First, when I multiply 6.33% * 2097, I get 132.7401.

Are you sure you're looking at the correct formula?

==
And some other comments.

If you format the cell as general and make the column wide enough, then you
should see all the decimals. If the column is too narrow to show all the
decimal places, excel will round the display (not the actual value, though).

If you format the cell as number with 2 decimal places and shrink the column to
only show only the whole part of the answer, you should see ######'s. If you
widen the column, you'll see everything.

wrote:

My wife and I are decent on excel but couldn't figure this one out...

Here is an example of one of my rows:

I'm trying to calculate from within a cell a percentage (6.33%) times
an integer (2,097). What Excel is returning is 186, when it should be
returning 185.91. Also 98.11 rounds down to 98. Any ideas to get Excel
to stop rounding my numbers?

I have the cell set to two decimal places, I've tried many formats
general, number accounting and they all do the same thing.

Thanks in adavance!

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


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
FedEx tracking numbers in Excel Adam J Excel Discussion (Misc queries) 0 July 24th 06 07:34 PM
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 05:59 PM.

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"