Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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 "thaenn" wrote in message ... |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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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? 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
On Fri, 27 Oct 2006 06:24:01 -0700, 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? 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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! "Ron Rosenfeld" wrote: On Fri, 27 Oct 2006 06:24:01 -0700, 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? 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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 "thaenn" wrote in message ... | 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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "thaenn" wrote in message ... 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! "Ron Rosenfeld" wrote: On Fri, 27 Oct 2006 06:24:01 -0700, 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? 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
On Fri, 27 Oct 2006 07:18:02 -0700, thaenn
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
ERRORS IN A DIVISON FORMULA
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 "Ron Rosenfeld" wrote in message ... | On Fri, 27 Oct 2006 07:18:02 -0700, thaenn | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Errors in "vlookups" formula | Excel Worksheet Functions | |||
How do i make a sum formula ignore #div/0! errors in the range | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |