why product(column(1:10)) gives error?

  • Thread starter Thread starter dindigul
  • Start date Start date
dindigul said:
{=product(row(1:10))} gives proper result, what is the problem with
column(1:10) ?
Click to expand...

1:10 is reference to a 10 row by 256 column range, equivalent to
A1:IV10. ROW(1:10) ignores columns and returns {1;2;3;4;5;6;7;8;9;10}.
COLUMN(1:10), on the other hand, ignores rows, so returns the same
result as COLUMN(A1:IV1) or COLUMN(1:1).

However, SUM(LOG10(COLUMN(1:1))) returns 506.933395, which means the
result would be on the order of 10^507, but Excel can only handle
numeric values up to 10^308. That means PRODUCT(COLUMN(1:10)) produces
a theoretical result outside Excel's numeric bounds. That's why Excel
returns #NUM!

If you want the product of the first 10 positive integers, stick with
PRODUCT(ROW(1:10)). For columns, you'd need to use
PRODUCT(COLUMN(A:J)), which isn't quite as clear.
 
Peo Sjoblom said:
COLUMN does not use numbers, it uses letters

=PRODUCT(COLUMN(A:J))
Click to expand...

Not so. COLUMN(1:10) happily returns {1,2,3,...,254,255,256}.

Both ROW and COLUMN accept any valid single-area range reference, but
they do different things with them. 1:10 is a valid single-area range
reference, so COLUMN does use it, but it does something different with
it than ROW. Similarly, ROW(A:A) is perfectly valid, but likely
doesn't do what most people would expect. And it provides delightful
nonorthogonality: ROW() always returns an array, and UNLIKE all other
array expressions, it happily returns arrays that span all 65536 rows
in Excel 2003. That is,

=SUMPRODUCT(ROW(A:A))

returns 2147516416 = 65537 * 65536 / 2.
 
Back
Top