Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
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. "RC" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
On Thu, 10 Dec 2009 08:54:01 -0800, RC wrote:
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
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. "FrankWood" wrote: 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. "RC" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
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. -- David Biddulph "RC" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula error
"RC" wrote:
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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Error | Excel Discussion (Misc queries) | |||
Formula Error #value | Excel Worksheet Functions | |||
Formula Error | Excel Worksheet Functions | |||
Formula Error | New Users to Excel | |||
How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions |