 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007why product(column(1:10)) gives error?
{=product(row(1:10))} gives proper result, what is the problem with column(1:10) ? COLUMN does not use numbers, it uses letters =PRODUCT(COLUMN(A:J))  SignatureRegards, Peo Sjoblom > {=product(row(1:10))} gives proper result, what is the problem with > column(1:10) ? "Peo Sjoblom" <terr...@mvps.org> wrote... >COLUMN does not use numbers, it uses letters > >=PRODUCT(COLUMN(A:J)) 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. "dindigul" <padhy...@gmail.com> wrote... >{=product(row(1:10))} gives proper result, what is the problem with >column(1:10) ? 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.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.