Description: This is a tabletop character sheet. The following formula is used to match character class in a preset list: {=SI(R6 > 0, MAX(RECHERCHEH($E$2:$E$5, Classes.$C$1:$AI$48, Classes.$A12, 0)))} Where $E$2:$E$5 are the classes the character specialize in, Classes.$C$1:$AI$48 is the class preset list, Classes.$A12 is the line to be crossed with classes' name. As we use a matrix formula, MAX is used to filter the 0-4 list of numbers. Only 0's and 3's are exepected. But when the formula matches nothing, a strange value, -1.8E+308 is yielded instead of 0. I see no way to achieve this result. This bug is not reproduced in Apache OOo 4.1.3 (portable version). Steps to Reproduce: Use a VLOOKUP in a matrix formula. Actual Results: -1.8E+308 Reproducible: Didn't try User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
Created attachment 128527 [details] spreadsheet presenting the bug Here is a copy of my default character sheet with the minimal content.
Reproducible also with: Version: 5.3.0.0.alpha1+ Build ID: 32bdc5097013e7efd9c85e1b8df697880e66e925 CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; Layout Engine: new; TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-11-04_23:30:30 Locale: es-ES (es_ES); Calc: CL Disabling OpenCL and/or Software interpreter doesn't affect the issue. In any case the formula I think it's not right. Classes.$A12 it's a text not a number. Maybe ROW(Classes.$A12) it's how to do.
Investigating.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e694c747954078442d47d3d7bd1d4db283b96ff Resolves: tdf#103734 propagate error from matrix to MIN()/MAX() It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/41883 for 5-4 https://gerrit.libreoffice.org/41886 for 5-3
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=09b4b50ef5bfe1b7d963ae70071d4e8ce20f361d&h=libreoffice-5-4 Resolves: tdf#103734 propagate error from matrix to MIN()/MAX() It will be available in 5.4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=149f28e9a5d66db18ffb36547b2ba394c303fc4d&h=libreoffice-5-3 Resolves: tdf#103734 propagate error from matrix to MIN()/MAX() It will be available in 5.3.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/17c2bd87e93fadb502e5f7e25696e1736e625d18 tdf#103734: sc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.