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.str...@tiscali.it
PEC: carlo.str...@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.str...@tiscali.it
PEC: carlo.str...@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+unsubscr...@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
