Rounding numbers

[kebba31]

Is there an easy way to round numbers to the nearest whole number while eliminating the other digits (sorry wrong term-I know) hidden in the cell. i.e. If a cell shows a number as $506.86 but the actual number in the cell "behind the scene" is 506.857026, can I amend this to reflect what's actually shown. The rest of the numbers are messing up my total. I already decreased decimal to reflect two digist. I hope this makes sense to someone. Thanks.

 

[Rick Rothstein]

The value in a cell doesn't change when you format the cell... the only thing that changes is how that value is displayed. So, if you want to use the displayed value in you calculation, you cannot reference the cell inside your formula directly (because it contains the value with all the digits); rather, you need to reference the rounded value instead. So, assuming A1 contains your 506.857026 value, you would need to refer to this inside your formula using ROUND(A1,2) instead.

 

[kebba31]

So entering the formual as (A1,2) means that your are telling Excel to calculate just the first two digits after the decimal within the cell. Am I understanding you correctly? So if the number in the cell shows 506.86 and the complete number is 506.857026, would it will just calculate 506.85 or would it round that up to 506.86?

 

[Rick Rothstein]

So entering the formual as (A1,2) means that your are telling Excel to calculate just the first two digits after the decimal within the cell.

No, not as (A1,2), but as ROUND(A1,2)... you must use the ROUND function.

So if the number in the cell shows 506.86 and the complete number is 506.857026, would it will just calculate 506.85 or would it round that up to 506.86?

The ROUND function takes the value in the cell (not what is displayed in the cell) and rounds it like you would expect... if the number being rounded away is 0 through 4... it is dropped; if it is 5 through 9, the number in front of it is bumped up by one.

 

[kebba31]

Thank you. So it calculates as shown because that's what is in the cell but rounded so the calculation should be correct and the error we're experiencing must be a typo somewhere. Thanks again. I understand.

 

[Scott Huish]

The op asked to round to the nearest whole number not to 2 decimal places:

=ROUND(A1,0)

 

[Rick Rothstein]

The op asked to round to the nearest whole number....

I know that is what he said in the opening sentence, but then he went on to say...

"If a cell shows a number as $506.86 but the actual number in the
cell 'behind the scene' is 506.857026, can I amend this to reflect
what's actually shown?" (emphasis added by me).

I took this to mean he meant whole number of cents, not whole number of dollars.