| Summary: | empty/missing argument (two consecutive parameter separators) not accepted by Excel for some functions | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | raal <raal> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | erack, h3734236 |
| Priority: | medium | ||
| Version: | Inherited From OOo | ||
| Hardware: | All | ||
| OS: | All | ||
| URL: | https://support.office.com/en-us/article/FVSCHEDULE-function-bec29522-bd87-4082-bab9-a241f3fb251d#feedbackText | ||
| Whiteboard: | target:5.3.0 | ||
| Crash report or crash signature: | Regression By: | ||
|
Description
raal
2016-07-04 13:53:08 UTC
With the missing/omitted arguments mess my current bet is that functions in Excel that originate from their Analysis Add-In pack (as opposed to core functions) could not cope with missing/omitted arguments the way other Excel functions did, hence deliver #N/A if such is used. Just a guess.. Well as said on the office/excel site: the second argument *is* required, so it can't be left out. But can left blank, ie. {}. That's why confirmed.
----The values in schedule can be numbers or blank cells; any other value produces the #VALUE! error value for FVSCHEDULE. Blank cells are taken as zeros (no interest)---
Please don't confuse a blank argument with references to blank cells, a reference always results in an argument, reference to empty cell usually in 0 in numeric context. An entirely omitted parameter (e.g. FOO taking two parameters given only FOO(a) one parameter) is different from a present parameter but omitted argument (i.e. left blank), e.g. FOO(a,) is given two parameters but with an omitted argument for the second. Many functions in Excel can have a present parameter with omitted argument even if the parameter is required and the omitted argument is usually (not always) substituted with 0, for example POWER(1,) results in 1 This function here (FVSCHEDULE) and quite some can not.. My assumption now is that those are the functions which for early Excel versions were implemented in Add-In packs. But as said, that's just a guess for now. So, that assumption does not hold true. Some Add-In functions do support omitted arguments. Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4f322f8c6edfceae31e5c61ee2506996083637bf tdf#100759 pass empty Any for omitted missing argument to Add-In functions It will be available in 5.3.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. @raal: With that commit Add-In functions accept a missing argument only for parameters where explicitly supported or optional parameters. The existing test cases for YEARS and WEEKS (LO only) and FVSCHEDULE and ODDLPRICE (ODFF and Excel) had to be adapted with that change. Those two functions now behave as in Excel. |