Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)

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