formula error

  • Thread starter Thread starter RC
  • Start date Start date
R

RC

something basic as : =(cell)*(cell) or 4,109 * 43.86. Should equate to
180,221; however displays as 180,200.

formula error flag is in upper left..but I can find nothing...any suggestions?

Thanks all
 
Is this happening on more than one formula, using more than those particular
cells?

Perhaps try this in a different (new blank) workbook and see if it it
related to just this one file. It may be a corrupt file, or maybe a
formatting problem with the data.
 
something basic as : =(cell)*(cell) or 4,109 * 43.86. Should equate to
180,221; however displays as 180,200.

formula error flag is in upper left..but I can find nothing...any suggestions?

Thanks all
Click to expand...

Since neither of the examples you gave are legitimate Excel formulas, I can't
really say why you are seeing the formula error flag.

However, the usual cause for a formula result not being what you expect is due
to confusion about what a cell is "displaying" and what the cell actually
"contains".

The cell display depends, in part, on the formatting of the cell. However,
calculations are always done using the actual contents of the cell.

If you
enter the formula =4109*43.86 in some cell
ensure it is formatted as General
widen the cell so as to see all the digits
The result will be 180220.74

If this doesn't help, then please be more specific about exactly what you are
doing including:
the contents of the cell (the contents of the **formula bar** when you
select the cell, not what you see in the cell itself)

the contents of any precedents if the cells contain formulas
the real formulas you are using -- not representations
the formatting of the different cells involved.

--ron
 
I think you need to format the cell to be a number, not "General" Right
click on the cell, then go to format cell, click on the "Number" tab then
select either number or accounting. Specify how many decimal points you want
& if you want a comma seperator. That will allow rounding to work.
 
Display your cell contents with more decimal places so that you can see what
the cell contents *really* are.

4108.5 will display as 4109 if shown with zero decimal places.
43.855 will display as 43.86 if shown with 2 decimal places.
Multiply those, and you get 180178.3 so 180200 is well within your range of
uncertainty.
 
RC said:
something basic as : =(cell)*(cell) or 4,109 * 43.86. Should equate to
180,221; however displays as 180,200.
formula error flag is in upper left..but I can find nothing...any
suggestions?
Click to expand...

First, ignore the "error flag". It is usually misleading, usually
indicating only that the formula in that cell is "inconsistent" with
formulas in surrounding cells. That might not be an error at all. I always
disable "error checking".

As for your problem....

As you know, 4109*43.76 is 180,220.74 if you use a calculator (or do it by
hand), and it is displayed as 180,220.7 in Excel using a General format. So
when you say that you expect to see 180,211, that must be the result of some
explicit format, e.g. Number with zero decimal places.

But formatting affects only the appearance of values. It does not change
the actual value in the cell, unless you set the option "Precision as
displayed" (not recommended).

Ergo, the root cause of the problem is probably that either 4109 or 43.86 or
both are not actually what they appear to be. As an experiment, format the
cells as Number with 11 decimal places.

A value that appears as 4109 can actually be any value between about 4108.5
and 4108.49...9, and a value that appears as 43.86 can actually be any value
between about 43.855 and 43.8649...9. The product of many combinations of
those numbers can result in a value that appears as 180,200, which can
actually be any value between about 180199.5 and 180220.49...9.

To ensure that you use numbers with the intended precision, you should use
the ROUND prolifically, albeit prudently. At a minimum:

=ROUND(ROUND(A1,0)*ROUND(A2,2), 0)

where A1 appears to be 4109, and A2 appears to be 43.86. Even better, use
ROUND in the formulas in A1 and A2, and compute:

=ROUND(A1*A2, 0)

Alternatively, you might consider setting the calculation option "Precision
as displayed". I do not recommend it because of its pervasive nature.
However, if you choose to experiment with PAD, be sure to make a back-up
copy of your Excel file first. Setting PAD can result in unintended changes
to constant, which is irreversible.
 
Back
Top