ERRORS IN A DIVISON FORMULA

[Guest]

i have a formula in "cell K25" that is dividing 15,000 (Cell D25) by 3.01
(Cell K24) and I am getting a result of 4,978.13.

This is close to the correct answer, but not exact. The correct answer
should be 4,983.39.

The formula is written as follows: =D25/K24

Any Suggestions?

 

[Niek Otten]

If you change the format of K24 to General and make the column wide enough, you'll probably notice that its value is 3.013179394.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|i have a formula in "cell K25" that is dividing 15,000 (Cell D25) by 3.01
| (Cell K24) and I am getting a result of 4,978.13.
|
| This is close to the correct answer, but not exact. The correct answer
| should be 4,983.39.
|
| The formula is written as follows: =D25/K24
|
| Any Suggestions?

 

[Ken Johnson]

i have a formula in "cell K25" that is dividing 15,000 (Cell D25) by 3.01
(Cell K24) and I am getting a result of 4,978.13.

This is close to the correct answer, but not exact. The correct answer
should be 4,983.39.

The formula is written as follows: =D25/K24

Any Suggestions?

Looks like the actual value in the K24 cell is 3.009999 but it is
formated as Number with 2 decimal places. If you reformat K24 to
general or increase it number of decimal places to 6 then you will more
than likely see its value displayed as 3.00999, not 3.010000.

Ken Johnson

 

[Guest]

Alternatively, you could go to

Tools-> Options -> Calculation and select PRECISION AS DISPLAYED

if that's really what you want.

 

[Ken Johnson]

It would help if I used the correct wrong answer, then I would have
calculated the same true value in K24 as Niek:-\

Ken Johnson

 

[Ron Rosenfeld]

i have a formula in "cell K25" that is dividing 15,000 (Cell D25) by 3.01
(Cell K24) and I am getting a result of 4,978.13.

This is close to the correct answer, but not exact. The correct answer
should be 4,983.39.

The formula is written as follows: =D25/K24

Any Suggestions?

Your formula is not using the values you think it is.

If you look at the actual values in the precedent cells, you will see that they
are not exactly 15,000 or 3.01.

If you want to get an answer based on the rounded values in those cells, then
you must explicitly round them.

e.g. =ROUND(D25,2)/ROUND(K24,2)


--ron

 

[Guest]

Barb,
Is there an advantage or disadvantage to using "precision as displayed" vs.
entering "round" into the formula?
Thanks for your help!

 

[Guest]

Ron,
Is there an advantage or disadvantage to using "round" in the formula vs.
checking "precision as displayed" that was also suggested?
Thanks for your help!

 

[Niek Otten]

In financial transaction applications I find this option very useful.
But be careful; it only works for cells that are explicitly formatted.
If your cell is formatted as general, but just wide enough to display "3.01" it retains its original precision, like 3.051 or
3.0149
In engineering or financial research applications it probably does more harm than it helps.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Barb,
| Is there an advantage or disadvantage to using "precision as displayed" vs.
| entering "round" into the formula?
| Thanks for your help!
|
| "Barb Reinhardt" wrote:
|
| > Alternatively, you could go to
| >
| > Tools-> Options -> Calculation and select PRECISION AS DISPLAYED
| >
| > if that's really what you want.
| >
| > "thaenn" wrote:
| >
| > > i have a formula in "cell K25" that is dividing 15,000 (Cell D25) by 3.01
| > > (Cell K24) and I am getting a result of 4,978.13.
| > >
| > > This is close to the correct answer, but not exact. The correct answer
| > > should be 4,983.39.
| > >
| > > The formula is written as follows: =D25/K24
| > >
| > > Any Suggestions?

 

[Bernard Liengme]

The 'precision as displayed' method will affect all opened files. A user
(even yourself) who does not know this option is set on may be mislead by
the data displayed. The ROUND method applies only to the cells using it and
those referring to them
best wishes

 

[Ron Rosenfeld]

Ron,
Is there an advantage or disadvantage to using "round" in the formula vs.
checking "precision as displayed" that was also suggested?
Thanks for your help!

The Precision as Displayed will affect all sheets in the open workbook and will
cause all cells to permanently lose accuracy.

This may or may not be OK depending on your requirements.

But if, for example, you have a cell on some other worksheet only showing 1
decimal place when you need it to show 3 or 4, and you select precision as
displayed, you have permanently lost that precision. If, in addition, that
value is a precedent of cells on other worksheets, you may wind up with less
precision than you expect.

On the other hand, using the ROUND function requires more work, but it will
only apply to the cells to which you apply it. Only those cells, and their
dependents, will be affected by the decreased precision.


--ron

 

[Niek Otten]

I realize this a controversial subject, but in my opinion you should always show what is calculated with and vice versa. The whole
idea of spreadsheets was automating the calculations on the backside of your cigar box, wasn't it?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| On Fri, 27 Oct 2006 07:18:02 -0700, thaenn <[email protected]>
| wrote:
|
| >Ron,
| >Is there an advantage or disadvantage to using "round" in the formula vs.
| >checking "precision as displayed" that was also suggested?
| >Thanks for your help!
|
| The Precision as Displayed will affect all sheets in the open workbook and will
| cause all cells to permanently lose accuracy.
|
| This may or may not be OK depending on your requirements.
|
| But if, for example, you have a cell on some other worksheet only showing 1
| decimal place when you need it to show 3 or 4, and you select precision as
| displayed, you have permanently lost that precision. If, in addition, that
| value is a precedent of cells on other worksheets, you may wind up with less
| precision than you expect.
|
| On the other hand, using the ROUND function requires more work, but it will
| only apply to the cells to which you apply it. Only those cells, and their
| dependents, will be affected by the decreased precision.
|
|
| --ron