Apache OpenOffice (AOO) Bugzilla – Issue 114293
DSUM error 504 due to second parameter (field number)
Last modified: 2015-09-11 19:01:12 UTC
When COLUMN() function is used as the second parameter, DSUM() gives error 504. If I cast the return value of COLUMN() to a number using N(), DSUM() works ok. If I put a simpler argument to COLUMN() instead of a range, DSUM() works ok. If that function is precalculated on another cell (F3), DSUM() works ok. ---A----------B--- -C-D-E- ---F---- 1 Customer Total Customer Formula used on cell to the left 2 John 10 John 3 Peter 20 1 COLUMN(A1:B1) 4 Err:504 DSUM( A1:B3; COLUMN(A1:B1) +1; F1:F2 ) 5 10 DSUM( A1:B3; N(COLUMN(A1:B1))+1; F1:F2 ) 6 10 DSUM( A1:B3; COLUMN(A1) +1; F1:F2 ) 7 10 DSUM( A1:B3; F3 ) Related issues 58183 77107
Created attachment 71549 [details] DSUM() error 504
Confirmed with Rev. 1525015 Win 7.
TYPE(COLUMN(A1:B1)) = 64, an array formula. TYPE(COLUMN(A1:B1) + 1) = 64, an array formula. TYPE(N(COLUMN(A1:B1))) = 1, a number. TYPE(another cell with precalculated value) = 8, a scalar formula (had you pressed Shift + Ctrl + Enter instead of just Enter when calculating that cell, it would be 64 for array formula). You are expecting {1, 2} to be used to choose 1 column. How could that ever work? According to ODFF, DCOUNT() expects a string for the column name, or an integer counting left to right starting at 1. Array formulas are undefined. Maybe an implicit conversion using an implied intersection could make sense, but your formula isn't in columns A or B, and that doesn't work even if you put it there (is that a bug?). What does Excel do? This should probably be RESOLVED NOT_AN_ISSUE.
Unbelievably it works on Excel 2007: COLUMNS(COLUMN(A1:B1)) = 2, yet TYPE(COLUMN(A1:B1)) = 1 and using COLUMN(A1:B1) in DSUM()'s parameter 2 takes the first value, ie. 1, no matter where the formula is placed. Not sure what Excel does; selecting 2 horizontally adjacent cells and typing =COLUMN(A1:B1) Ctrl+Shift+Enter gives 1 and 2, typing =TYPE(COLUMN(A1:B1)) pressing Ctrl+Shift+Enter gives 64 and 64, so what, does it automatically convert array formulas to scalars when going into 1 cell instead of several?