Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


From what I remember, in Excel the formula does not yield an error or something like that, but a numerical result!!!

The original formula is (I translate the functions from Italian to English named ones):
=IF(ISERROR(LOOKUP("+";E7));D7-D7*E7%;(D7-D7*MID(E7;1;LOOKUP("+";E7)-1)%)-(D7-D7*MID(E7;1;LOOKUP("+";E7)-1)%)*MID(E7;LOOKUP("+";E7)+1;LEN(E7)-LOOKUP(;E7))%)
where:
- D7 contains final user price (full price);
- E7 contains the %discount (i.e. either "24" or "23+5");
- F7 contains the upper big formula;
- the trouble is in the last "LOOKUP(;E7)".

I suppose (!) there is an addition to the two behaviors ("Err:511" and "#N/A", respectively from LO and Excel) described by Luuk: Excel evaluate only the part it calculate and not the entire formula or loose the internal "#N/A" in calculations. I cannot check this because I have not Excel anymore on my notebook...

In other words, if you use a formula like this:
=IF(1=1;3;LOOKUP(;"peace"))

LO check the entire formula and answer "Err:511" and Excel? Someone may check for this?

In any case I agree with Stefan, this is not an Excel problem neither a LibreOffice problem, it´s a users problem. :-)

Thank you very much,

Carlo

p.s. The fixed formula has a plus sign on the first parameter, that is: "LOOKUP("+";E7)", like other ones.


--
ing. Carlo Strata
-
via Botticelli 1/4
30031 Dolo - VE
Italia - Italy
-
tel. +39.041.822.0665
cell. +39.347.85.69.824
Skype carlo.strata
Google carlo.strata.69
-
carlo.strata@tiscali.it
PEC: carlo.strata@ingpec.eu

Il 30/11/2014 12:16, Carlo Strata ha scritto:
Hi Everyone,

Brian understood exactly what I meant. I.e.:
=LOOKUP(;A5)

Have a nice sunday,

Carlo

--
ing. Carlo Strata
-
via Botticelli 1/4
30031 Dolo - VE
Italia - Italy
-
tel. +39.041.822.0665
cell. +39.347.85.69.824
Skype carlo.strata
Google carlo.strata.69
-
carlo.strata@tiscali.it
PEC: carlo.strata@ingpec.eu

Il 30/11/2014 11:48, Brian Barker ha scritto:
At 20:07 29/11/2014 +0100, Luuk Noname wrote:
On 29-11-2014 16:28, Carlo Strata wrote:
I recently deal with a supplier catalog/price list spreadsheet xlsx file. That file use the "LOOKUP()" function (in Italy "RICERCA()") in many places. In one of those occurrences, the first LOOKUP() parameter (Search Criterion) was missed, but on Microsoft Excel this was ok (no errors in the cell), in LibreOffice was ko (Err:511, missing variable/parameter)! I think this difference was yielded by different assumed values of the missing parameter and/or different validation rules. So compliance depends not only on file format, but also on function behaviour/validation on different program.

I do not see a difference, besides a language difference (i have Dutch version of Excel, en LO in English) (in Excel is see '#N/B', when LOOKUP did not found, in LO is see '#N/A')

I uploaded my testfile here:
http://wikisend.com/download/402228/zoeken.xlsx

I don't think the questioner is referring to the situation in your example, where the cell referred to by the first parameter is empty, but that where the LOOKUP() reference itself contains no first parameter - in other words, where its opening parenthesis is followed immediately by the separator character. In that case, the behaviour does appear to be different: LibreOffice treats it as faulty formula, whereas Microsoft Excel evaluates the (slightly deficient) formula and returns #N/A.

Brian Barker





--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.