Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
Hi, Am 30.11.2014 um 23:37 schrieb Carlo Strata: ... so this is an Excel problem because it behaves differently with respect to its own help...?!!! Not really. Excel states, that you must apply the first parameter and you must not leave it empty. (It´s required.) If you follow this, everything is fine. And it´s the same thing in LibreOffice. There is no specification for the behaviour of the function, in case it´s not used according to the rules. You can expect Excel and LibreOffice behave the same, if you use functions according to their definition and the instructions given in the documentation. You cannot expect identical behaviour, in case you violate the instructions. -- Your Problem is not an Excel problem neither a LibreOffice problem, it´s a users problem. ;-) Cheers, Stefan -- LibreOffice - Die Freiheit nehm' ich mir! -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
Hi, Am 30.11.2014 um 23:37 schrieb Carlo Strata: ... so this is an Excel problem because it behaves differently with respect to its own help...?!!! Not really. Excel states, that you must apply the first parameter and you must not leave it empty. (It´s required.) If you follow this, everything is fine. And it´s the same thing in LibreOffice. There is no specification for the behaviour of the function, in case it´s not used according to the rules. You can expect Excel and LibreOffice behave the same, if you use functions according to their definition and the instructions given in the documentation. You cannot expect identical behaviour, in case you violate the instructions. -- Your Problem is not an Excel problem neither a LibreOffice problem, it´s a users problem. ;-) Cheers, Stefan -- 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
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.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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
At 14:04 01/12/2014 +0100, Carlo Strata wrote: 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 lose 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? Excel needs comma separators instead of semi-colons, of course - but the equivalent formula yields 3 in Excel 2010. If you change the test to 11 to force it to evaluate the LOOKUP(), it gives a #VALUE! error because peace is a text value, not an array. If you substitute an array here, it gives #N/A. In either of these latter cases, if you ask it to show calculation steps, it has evaluated the inequality to FALSE, identified the LOOKUP as the next thing to evaluate, and says The next evaluation will result in an error. 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
Am 01.12.2014 um 14:58 schrieb Brian Barker: Excel needs comma separators instead of semi-colons, of course very much depending on locale, of course ;-) Stefan :-D -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
Thank you very much Brian. Have a nice evening, 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 01/12/2014 14:58, Brian Barker ha scritto: At 14:04 01/12/2014 +0100, Carlo Strata wrote: 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 lose 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? Excel needs comma separators instead of semi-colons, of course - but the equivalent formula yields 3 in Excel 2010. If you change the test to 11 to force it to evaluate the LOOKUP(), it gives a #VALUE! error because peace is a text value, not an array. If you substitute an array here, it gives #N/A. In either of these latter cases, if you ask it to show calculation steps, it has evaluated the inequality to FALSE, identified the LOOKUP as the next thing to evaluate, and says The next evaluation will result in an error. 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
At 17:00 01/12/2014 +0100, Stefan Weigel wrote: Am 01.12.2014 um 14:58 schrieb Brian Barker: Excel needs comma separators instead of semi-colons, of course very much depending on locale, of course ;-) Aaargh! Sorry: I hadn't realised that - though it's obvious when you think about it ... Thanks for setting me right. 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
On 1-12-2014 14:58, Brian Barker wrote: At 14:04 01/12/2014 +0100, Carlo Strata wrote: 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 lose 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? Excel needs comma separators instead of semi-colons, of course - but the equivalent formula yields 3 in Excel 2010. If you change the test to 11 to force it to evaluate the LOOKUP(), it gives a #VALUE! error because peace is a text value, not an array. If you substitute an array here, it gives #N/A. In either of these latter cases, if you ask it to show calculation steps, it has evaluated the inequality to FALSE, identified the LOOKUP as the next thing to evaluate, and says The next evaluation will result in an error. Brian Barker Excel does not need comma separators, this is only true for the English version? If you have an Italian or Dutch, than a ';' is used as separator. In fact, the 'List separator' that is defined in the regional setting of Windows is used as separator. -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
On 1-12-2014 09:18, Stefan Weigel wrote: Hi, Am 30.11.2014 um 23:37 schrieb Carlo Strata: ... so this is an Excel problem because it behaves differently with respect to its own help...?!!! Not really. Excel states, that you must apply the first parameter and you must not leave it empty. (It´s required.) If you follow this, everything is fine. And it´s the same thing in LibreOffice. There is no specification for the behaviour of the function, in case it´s not used according to the rules. You can expect Excel and LibreOffice behave the same, if you use functions according to their definition and the instructions given in the documentation. You cannot expect identical behaviour, in case you violate the instructions. +1 -- Your Problem is not an Excel problem neither a LibreOffice problem, it´s a users problem. ;-) +1 Cheers, Stefan ;-) -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
On 1-12-2014 20:14, Luuk wrote: On 1-12-2014 14:58, Brian Barker wrote: At 14:04 01/12/2014 +0100, Carlo Strata wrote: 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 lose 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? Excel needs comma separators instead of semi-colons, of course - but the equivalent formula yields 3 in Excel 2010. If you change the test to 11 to force it to evaluate the LOOKUP(), it gives a #VALUE! error because peace is a text value, not an array. If you substitute an array here, it gives #N/A. In either of these latter cases, if you ask it to show calculation steps, it has evaluated the inequality to FALSE, identified the LOOKUP as the next thing to evaluate, and says The next evaluation will result in an error. Brian Barker Excel does not need comma separators, this is only true for the English version? If you have an Italian or Dutch, than a ';' is used as separator. In fact, the 'List separator' that is defined in the regional setting of Windows is used as separator. I should read the complete thread before posting a reaction... ;-) -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
On 30-11-2014 12:16, Carlo Strata wrote: Hi Everyone, Brian understood exactly what I meant. I.e.: =LOOKUP(;A5) Have a nice sunday, Carlo according to Microsoft, this firt value is 'REQUIRED' VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) lookup_value(*required*) The value you want to lookup. The value you want to look up must be in the first column of the range of cells you specify in table-array . For example, if table-array spans cells B2:D7, then your lookup_value must be in column B. See the graphic below. Lookup_value can be a value or a reference to a cell. or, in the Vector form: LOOKUP(lookup_value, lookup_vector, [result_vector]) The LOOKUP function vector form syntax has the following arguments: lookup_value*Required*. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. see: http://goo.gl/SqmmS9 -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
... so this is an Excel problem because it behaves differently with respect to its own help...?!!! In any case this Excel bug yields some documents with some uncompleted/uncorrected formulas that don't generate errors in the corresponding cells... with the concrete risk that this become a standard behavior and/or some additional work in migrating to LO, for example. Isn't it? 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 17:49, Luuk ha scritto: On 30-11-2014 12:16, Carlo Strata wrote: Hi Everyone, Brian understood exactly what I meant. I.e.: =LOOKUP(;A5) Have a nice sunday, Carlo according to Microsoft, this firt value is 'REQUIRED' VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) lookup_value(*required*) The value you want to lookup. The value you want to look up must be in the first column of the range of cells you specify in table-array . For example, if table-array spans cells B2:D7, then your lookup_value must be in column B. See the graphic below. Lookup_value can be a value or a reference to a cell. or, in the Vector form: LOOKUP(lookup_value, lookup_vector, [result_vector]) The LOOKUP function vector form syntax has the following arguments: lookup_value*Required*. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. see: http://goo.gl/SqmmS9 -- 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
Re: [libreoffice-users] [Calc] The LOOKUP() function (Calc vs. Excel)
On 29-11-2014 16:28, Carlo Strata wrote: Hi Everyone, 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. This is a Vendor Lockin trouble? http://en.wikipedia.org/wiki/Vendor_lock-in What do you think about? Is this a known issue? The work environment is: - Microsoft Windows 8.1, 64 bit, Ita gui, daily full updated; - LibreOffice 4.3.5.1, win32, Ita gui and local help; - Microsoft Office 2013 Professional Plus trial version (I had installed and then uninstalled in my notebook some weeks ago). Have All a nice weekend, Carlo 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 (LOOKUP, in Dutch ZOEKEN) -- 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
