Rounding

[Cynthia]

How can a get a number to round in the formula bar when it is already rounded
on the worksheet. Ex: Formula Bar says 16651107.5346 and the worksheet has
16651108 so I can total a column correctly?

Thanks!

Cynthia

 

[Glenn]

How can a get a number to round in the formula bar when it is already rounded
on the worksheet. Ex: Formula Bar says 16651107.5346 and the worksheet has
16651108 so I can total a column correctly?

Thanks!

Cynthia

The number on the worksheet is not rounded, it is exactly the same as the number
in the formula bar. It has been formatted to show no decimal places. Use the
ROUND function in another cell if you want the number rounded. So for example,
if you have 16651107.5346 in cell A1, put this in cell B1:

=ROUND(A1,0)

Then you will actually have 16651108 in B1.


Another option is "precision as displayed". According to help:

Precision as displayed - Permanently changes stored values in cells from full
precision (15 digits) to whatever format, including decimal places, is displayed.

Key word: PERMANENTLY

 

[Gary Brown]

What you see in the formula bar is the actual value in the cell.
What you see in the cell is the FORMATTED for viewing value.
To round the actual value, use the =Round( ) formula.
To round to the nearest whole # the formula would be something like...
=Round(A1,0)

 

[Joe User]

How can a get a number to round in the formula bar
when it is already rounded on the worksheet. Ex:
Formula Bar says 16651107.5346 and the worksheet
has 16651108 so I can total a column correctly?

Several ways you can solve your real problem ("total a column correctly").

1. Use =SUMPRODUCT(ROUND(A1:A100,0)) instead of =SUM(A1:A100)

2. If you get a #VALUE error with #1, use the array formula [*]
=SUM(IF(ISNUMBER(B5:B100),ROUND(B5:B100,0)))

[*] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, Excel will display curly braces around the
formula, viz. {=formula}. You cannot type the curly braces yourself; that is
just Excel's way of displaying an array formula. If you make a mistake,
select the cell, press F2, edit as needed, then press ctrl+shift+Enter.

3. Perhaps the real solution is to explicitly round the source of the
constant. Did you copy-and-paste-special-value from a cell was formatted as
Number with zero decimal places? If so, use =ROUND(formula,0) in the
original cell.

The following alternatives are deprecated....

4. Since you see a constant in the Formula Bar, I ass-u-me you have a
constant in the cell, which is formatted as Number with zero decimal places
or you copy-and-pasted-special-value from a cell that was. Right? Or did
you use F9 to evaluate the formula in the Formula Bar?

In either case, use =ROUND(16651107.5346,0) or =ROUND(formula,0) in the
cell. The first form might seem unusual; but it preserves the original value
for posterity.

5. Format the column or range of cells as Number with zero decimal places,
and set Precision As Displayed under Tools > Calculation (in Excel 2003).

I do not recommend this; it is very risky. Be sure to copy the Excel
file before you try this. It might solve the immediate problem with the
column. But it can have a pervasive and potentially undesirable effect on
the entire workbook. Constants may be changed irreversibly, which is why it
is useful to copy the Excel file first.

 

[Cynthia]

Thanks for all the advise!! It was very helpful!