Cynthia said:
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.