On Sep 25, 2:03 pm, Erich Neuwirth <erich.neuwi...@univie.ac.at> wrote:
> Here is another hint that it is probably a rendering problem.
We have conclusive proof it is not a rendering problem. Look at the example of "A1 + 1" and "A1 - 1". One gives the correct result and one does not. If it was a rendering issue, they would both give correct results.
My bet is they have two internal ways of storing the value, one for values less than or (sometimes) equal to 65,535 and one for values greater than or (sometimes) equal to 65,535.
That is, they have no consistent rule for which representation to set/ use when the value is equal to 65,535.
> On Sep 25, 2:03 pm, Erich Neuwirth <erich.neuwi...@univie.ac.at> > wrote:
> > Here is another hint that it is probably a rendering problem.
> We have conclusive proof it is not a rendering problem. Look at the > example of "A1 + 1" and "A1 - 1". One gives the correct result and one > does not. If it was a rendering issue, they would both give correct > results.
> My bet is they have two internal ways of storing the value, one for > values less than or (sometimes) equal to 65,535 and one for values > greater than or (sometimes) equal to 65,535.
> That is, they have no consistent rule for which representation to set/ > use when the value is equal to 65,535.
> DS
rewriting the formual mathematically seems to get different result. The problem may be due ot MSF is trying to swich 10 based floating number to binary more "efficiently".
A1=(=850*77) A2=(=850*0.1) A3=(=A1+A2)=65535 This gets the correct number
A1=849*77.1+77=65534.900000 A2=0.1 A3=(=A1+A2)=100000 This is incorrect.
MS must be offseting the number at certain point get better results.
> On Sep 25, 2:03 pm, Erich Neuwirth <erich.neuwi...@univie.ac.at> > wrote:
> > Here is another hint that it is probably a rendering problem.
> We have conclusive proof it is not a rendering problem. Look at the > example of "A1 + 1" and "A1 - 1". One gives the correct result and one > does not. If it was a rendering issue, they would both give correct > results.
> My bet is they have two internal ways of storing the value, one for > values less than or (sometimes) equal to 65,535 and one for values > greater than or (sometimes) equal to 65,535.
> That is, they have no consistent rule for which representation to set/ > use when the value is equal to 65,535.
> DS
If it isn't a rendering problem why does the date show correctly for day number 65535, even when it displays 100000 when formatted as a number?
> <doug...@gmail.com> wrote... > ... >>If it isn't a rendering problem why does the date show correctly for >>day number 65535, even when it displays 100000 when formatted as a >>number?
> It's precisely because it's treated differently in different calculations > that it can't be ONLY a rendering problem.
AFAIK it's not treated differently in different calculations. In all cases the correct value is used for a formula. It's just the same rendering bug shows up when rendering the results from those formula. See my reply to you in the other thread.
Funny, but as you look at real value you have 77,1000000000012 instead od 77,1 where you get good value.
I put 77,1111 in first row then 77,1110 in following to get values every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get 77,1105000000001 instead of 77,1105... then 77,1096000000002. 77,1087000000003 and so one...
> Funny, but as you look at real value you have 77,1000000000012 instead > od 77,1 where you get good value.
> I put 77,1111 in first row then 77,1110 in following to get values > every 0,0001 but step from 77,1106 to 77,1105 is weird becuase I get > 77,1105000000001 instead of 77,1105... then 77,1096000000002. > 77,1087000000003 and so one...
On Sep 26, 1:25 am, grant <grant....@ntlworld.com> wrote:
> The numerical result is actually > 0.009999999999990910.
year, that's why you need to check inside an epsilon region for equalness (the = sign should be replaced by a function, which tests something like x \in [y - machine-epsilon, y + machine-epsilon] this happens everywhere where you do floating point calculations on a CPU.
a good and quick solution would be to calculate everything in cents (i.e. integers!) and no commas at all.
> Oddly, I also multiplied 2 * 10.2 * 3212.5 and got 100000, so it is > not just when you multiply 2 numbers that equal 65535, it is when > certain combinations of number add up to 65535...
> Also tried 2 * 10.2 * 642.5 * 5 and it is 100000
OTOH, when I tried 5 * 10.2 * 642.5 * 2 I got the correct answer, 65535.
On Sep 23, 8:55 am, "Dana DeLouis" <ddelo...@bellsouth.net> wrote:
> There appears to be more of them. For example: > =5.1*12850 > =10.2*6425 > =20.4*3212.5 > =40.8*1606.25 > =77.1*850 > =154.2*425 > =212.5*308.4 > =308.4*212.5 > =425*154.2 > ..etc
Is this only happen for multiplication and for number with fractions?
it has to do with rounding of the 16th bit, which is just outside Excel's display of 15 digits. Hence, at the same 16 digit precision, numbers like the following display this issue:
> On Sep 23, 3:20 am, "Molham Serry" <mse...@contact.com.eg> wrote: >> Simply when you try to multiply 850 by 77.1 excel display the result to >> be >> 100000 !!!
> "Michael C" <m...@nospam.com> wrote... > ><michael.e.br...@gmail.com> wrote in message > ... > >>Complete and utter BS. As pointed out, the Excel representation > >>holds absolutely nothing in common with either the computers native > >>FP result, nor with the IEEE standard for floating point.
> >Are you saying excel doesn't use the FP processor? How then did the > >previous intel floating point bug appear in excel?
> Excel uses the FPU for INTERMEDIATE calculations, but the final value > of any cell formula gets passed through some, er, MSFT value-added > processing before it becomes the cell's value. This is how > nonparenthesized expressions are implicitly rounded to 15 decimal > digits. Also, and quite unfortunately, some built-in function calls > perform additional and usually unnecessary postprocessing of FPU > results, e.g., MOD(2^30-1,3), which returns #NUM! because MOD can't > handle arguments that produce quotients comfortably within 32-bit > integer or 64-bit double precision bounds even though this is well > within the FPU's capabilities.
> I don't think this is the case for 850*77.1. I suspect some bright > spark on the Excel development team decided to rewrite their machine > representation-to-human representation internal number formatting > function, and they screwed it up somehow.
> What really concerns me is what Dana DeLouis pointed out: with A1 > containing =850*77.1, =A1+1 returns 100001 while =A1-1 returns 65534. > That's VERY, VERY BAD because it implies the value in A1 isn't just > fed directly to the FPU stack. It's subjected to, er, value-added > preprocessing which seems to differ depending on what the precise > operation is. What I'm most worried about, given Dana's other > examples, is whether someone thought they were doing Excel users a > favor by treating increment (+1), decrement (-1), idempotent > operations (+0, *1, ^1), binary left shift (*2) and binary right shift > (/2) as special cases in the formula parser. MSFT *DID* rewrite parts > of the formula parser in XL2007, which is how they lifted the 7 nested > function call limit etc. I'd bet they rewrote too much of it.
If you take a look at the numbers in Octal, you can see what's going on with adding and subtracting 1. Even with 64 bit machines, everythings still based on octal.
JoelKatz wrote: > On Sep 25, 2:03 pm, Erich Neuwirth <erich.neuwi...@univie.ac.at> > wrote:
>> Here is another hint that it is probably a rendering problem.
> We have conclusive proof it is not a rendering problem. Look at the > example of "A1 + 1" and "A1 - 1". One gives the correct result and one > does not. If it was a rendering issue, they would both give correct > results.
> My bet is they have two internal ways of storing the value, one for > values less than or (sometimes) equal to 65,535 and one for values > greater than or (sometimes) equal to 65,535.
> That is, they have no consistent rule for which representation to set/ > use when the value is equal to 65,535.
<michael.e.br...@gmail.com> wrote: > On Sep 24, 10:11 pm, mega...@gmail.com wrote:
> > This is simple rounding error. Down at the hardware level of PCs, > > when working with floating point numbers, you are only guaranteed a > > number of significant digits (varies depending on the size of the data > > type). In this case, your numbers are good up to 7 digits (NNNNN.NN), > > which fits with the double data type in C.
> Complete and utter BS. As pointed out, the Excel representation holds > absolutely nothing in common with either the computers native FP > result, nor with the IEEE standard for floating point. > -- > MB
> > > > Simply when you try to multiply 850 by 77.1 excel display the result to be > > > > 100000 !!!
The bug is from the FPU, excel just converts its own algorithm to transfer it to text wich strips the data from a double.
And the 65565 bug is probally caused by an optimisation that causes excel to use the smallest data type possible to store data (thus saving a LOT of memory space), but this time they must have messed it up.
are numbers exhibiting this problematic behavior. So if a floating point calculation has such a result, (which is not visible because Excel does not display enough decimal digits) then you get 10000 or 10001 displayed.
What also is interesting that in these cases MOD(...,1) gives -2^(-35)... displayed in decimal as -0.00000000002910383046 (with 20 decimal digits) MOD(...,1) never should return negative values. ROUND(...,1) in these cases also produce the wrong result (10000 or 10001), the this is persistent. Any further calculation using the result of applying the ROUND function will be wrong.
So if the display engine used ROUND and/or MOD to create the displayed value, that would be consistent with all the errors reported so far.
|
