Apache OpenOffice (AOO) Bugzilla – Issue 56936
problem with OFFSET() function
Last modified: 2005-10-30 00:08:00 UTC
When I use the Offset() function inside a SUM() function, it results in an error message. I have many Excel97 spreadsheets using the offset function, generally inside another function. Used in an IF() function, it seems to work okay. But when I loaded an Excel97 sheet with the formula =-SUM(H$6:OFFSET(H147;-1;0)) in E147 Yesterday it gave me an "Error 508" message in that and all dependent cells. Today, the same formula in the same place gave me an "#N/A" error message. I use this type of formula so that I can move its row anywhere I want it in the table and it will always sum all rows above it. Anyway, I guess this exposes two problems: 1. Why is OFFSET() not working inside SUM() the same way it works inside IF()? 2. Why does it produce one error one day, and a different error the next day (in the same cell of the same UNEDITED spreadsheet)?
After editing the formula to see how OFFSET() would work inside the SUMIF() and SUBTOTAL() functions (which both returned a "#NAME?" error), when I returned the formula to it's original state (as written above), it too now returns a "#NAME?" error.
(I had also saved it as an .ods file instead of .xls.)
It is neither a problem with the function SUM nor with the function OFFSET. The problem is, that your formula uses a range, where one of the references is calculated. OOo is not able to use this. You must calculate the whole range. The issue for the corresponding feature request is issue 4904. *** This issue has been marked as a duplicate of 4904 ***
closing duplicate