Excel beta - another bug

Ps. I don't own the beta.

Harlan Grove wrote:
>
> Someone else please confirm this.
>
> A:A filled with the formula =ROW()^2.
>
> C1:
> =SUMPRODUCT(ROW(A:A)^2)
>
> C2:
> =SUM(A:A)
>
> C3: [array formula]
> =SUMSQ(ROW(A:A))
>
> C1:C3 all appear to show the same value, 3.84307717958020E+17. And the
> next 3 formulas all return true.
>
> C4:
> =C1=C2
>
> C5:
> =C2=C3
>
> C6:
> =C1=C3
>
> But the following formulas aren't consistent with this.
>
> C7:
> =STDEV(C1:C3) returns 8589934592.0
>
> C8:
> =DEVSQ(C1:C3) returns 12288
>
> FWIW, the exact result, per Mathematica, should be
> 3.843077179582709760E+17, so the XL12 result is affected by truncation
> error at the 12th decimal digit. On the other hand, in XL11, with the
> following formula entered into A1:AF32768
>
> =((COLUMN()-1)*32768+ROW())^2
>
> each of the following formulas return 3.84307717958226E+17
>
> AH1:
> =SUMPRODUCT(A1:AF32768)
>
> AH2:
> =SUM(A1:AF32768)
>
> AH3: [array formula]
> =SUMSQ((COLUMN(A:AF)-1)*32768+ROW(1:32768))
>
> And both the following formulas return 0.
>
> AH4:
> =STDEV(AH1:AH3)
>
> AH5:
> =DEVSQ(AH1:AH3)
>
> The XL11 result is closer to the actual value than the XL12 answer, so
> it appears something has changed between XL11 and XL12, which is to be
> expected since XL12 would need a new iterator to span the larger
> worksheet grid. Unfortunately, it seems this new iterator degrades
> XL12's numeric accuracy relative to XL11.
>
> Can anyone confirm whether XL12 forces precision to 15 decimal digits
> in all intermediate calculations? FWIW, the gawk script
>
> BEGIN { for (k = 1; k <= 2^20; ++k) s += k; printf("%.15e\n", s) }
>
> produces the same result as XL12, and the gawk script accumulates the
> sum in the variable s, which only provides 64-bit double precision
> instead of the FPU's 80-bit extended precision.

--

Dave Peterson

who cares excel is roadkill

http://news.com.com/Google+Spreadshe...l?tag=nefd.top


Dave Peterson wrote:
> Ps. I don't own the beta.
>
> Harlan Grove wrote:
> >
> > Someone else please confirm this.
> >
> > A:A filled with the formula =ROW()^2.
> >
> > C1:
> > =SUMPRODUCT(ROW(A:A)^2)
> >
> > C2:
> > =SUM(A:A)
> >
> > C3: [array formula]
> > =SUMSQ(ROW(A:A))
> >
> > C1:C3 all appear to show the same value, 3.84307717958020E+17. And the
> > next 3 formulas all return true.
> >
> > C4:
> > =C1=C2
> >
> > C5:
> > =C2=C3
> >
> > C6:
> > =C1=C3
> >
> > But the following formulas aren't consistent with this.
> >
> > C7:
> > =STDEV(C1:C3) returns 8589934592.0
> >
> > C8:
> > =DEVSQ(C1:C3) returns 12288
> >
> > FWIW, the exact result, per Mathematica, should be
> > 3.843077179582709760E+17, so the XL12 result is affected by truncation
> > error at the 12th decimal digit. On the other hand, in XL11, with the
> > following formula entered into A1:AF32768
> >
> > =((COLUMN()-1)*32768+ROW())^2
> >
> > each of the following formulas return 3.84307717958226E+17
> >
> > AH1:
> > =SUMPRODUCT(A1:AF32768)
> >
> > AH2:
> > =SUM(A1:AF32768)
> >
> > AH3: [array formula]
> > =SUMSQ((COLUMN(A:AF)-1)*32768+ROW(1:32768))
> >
> > And both the following formulas return 0.
> >
> > AH4:
> > =STDEV(AH1:AH3)
> >
> > AH5:
> > =DEVSQ(AH1:AH3)
> >
> > The XL11 result is closer to the actual value than the XL12 answer, so
> > it appears something has changed between XL11 and XL12, which is to be
> > expected since XL12 would need a new iterator to span the larger
> > worksheet grid. Unfortunately, it seems this new iterator degrades
> > XL12's numeric accuracy relative to XL11.
> >
> > Can anyone confirm whether XL12 forces precision to 15 decimal digits
> > in all intermediate calculations? FWIW, the gawk script
> >
> > BEGIN { for (k = 1; k <= 2^20; ++k) s += k; printf("%.15e\n", s) }
> >
> > produces the same result as XL12, and the gawk script accumulates the
> > sum in the variable s, which only provides 64-bit double precision
> > instead of the FPU's 80-bit extended precision.
>
> --
>
> Dave Peterson