Re: [libreoffice-users] It would [not!] be a bug
Hi: Thank you by your help Regards, Jorge Rodríguez El lun, 24-06-2013 a las 08:30 +0100, david_lynch escribió: On 23/06/2013 23:30, Brian Barker wrote: At 12:08 23/06/2013 -0600, Jorge Rodríguez wrote: ... this other chart No. 2: Oficina Ventas Ala18 Here 16 Punta 3 Total Resultado 37 I've used VLookUp function to get data from chart No 2 to this other chart: Function: =BUSCARV(A3;'Tabla dinámica_Hoja2_1'.$A$11:$B$15;2) Show Should Show Office Sells Sells Ala 18 18 Punta 3 3 Here 16 16 Cart18 0 Lim 16 0 As you can see, the chart is order by office but I got 18 and 16 in Cart and Lim but It should show 0 in both. The LO program get datas from Ala and Here for Cart and Lim. What happens? There is no bug - but two mistakes. As you know, if you omit the optional fourth parameter in the VLOOKUP() function, the data is assumed to be sorted. But as the help text makes clear, a value is always returned in this case - corresponding to the the last value smaller than the criterion. In your case, Ala is next before Cart and Here is next before Lim. To prevent this, you need to add FALSE or 0 as the fourth parameter to VLOOKUP(). Second, there is no magic process by which Calc will guess that you wish for a zero value when no match is found: there is simply no value to return! The value #N/A will be returned instead. If you want zero values here, you need to test for this error value and replace it with zero. You can do this using the ISNA() function, using something like: =IF(ISNA(VLOOKUP(Xn;array;2;0));0;VLOOKUP(Xn;array;2;0)) or, more simply, (new in LO 4): =IFNA(VLOOKUP(Xn;array;2;0);0) I trust this helps. Brian Barker -- Atentamente, Jorge Rodríguez -- 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] It would [not!] be a bug
On 23/06/2013 23:30, Brian Barker wrote: At 12:08 23/06/2013 -0600, Jorge Rodríguez wrote: ... this other chart No. 2: Oficina Ventas Ala18 Here 16 Punta 3 Total Resultado 37 I've used VLookUp function to get data from chart No 2 to this other chart: Function: =BUSCARV(A3;'Tabla dinámica_Hoja2_1'.$A$11:$B$15;2) Show Should Show Office Sells Sells Ala 18 18 Punta 3 3 Here 16 16 Cart18 0 Lim 16 0 As you can see, the chart is order by office but I got 18 and 16 in Cart and Lim but It should show 0 in both. The LO program get datas from Ala and Here for Cart and Lim. What happens? There is no bug - but two mistakes. As you know, if you omit the optional fourth parameter in the VLOOKUP() function, the data is assumed to be sorted. But as the help text makes clear, a value is always returned in this case - corresponding to the the last value smaller than the criterion. In your case, Ala is next before Cart and Here is next before Lim. To prevent this, you need to add FALSE or 0 as the fourth parameter to VLOOKUP(). Second, there is no magic process by which Calc will guess that you wish for a zero value when no match is found: there is simply no value to return! The value #N/A will be returned instead. If you want zero values here, you need to test for this error value and replace it with zero. You can do this using the ISNA() function, using something like: =IF(ISNA(VLOOKUP(Xn;array;2;0));0;VLOOKUP(Xn;array;2;0)) or, more simply, (new in LO 4): =IFNA(VLOOKUP(Xn;array;2;0);0) I trust this helps. 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] It would [not!] be a bug
Oh ! I can see my mistake ... thank you very much for help me Regards, Jorge Rodríguez - Mensaje original - De: david_lynch david_ly...@blueyonder.co.uk Para: users@global.libreoffice.org Enviados: Lunes, 24 de Junio 2013 1:30:25 Asunto: Re: [libreoffice-users] It would [not!] be a bug On 23/06/2013 23:30, Brian Barker wrote: At 12:08 23/06/2013 -0600, Jorge Rodríguez wrote: ... this other chart No. 2: Oficina Ventas Ala18 Here 16 Punta 3 Total Resultado 37 I've used VLookUp function to get data from chart No 2 to this other chart: Function: =BUSCARV(A3;'Tabla dinámica_Hoja2_1'.$A$11:$B$15;2) Show Should Show Office Sells Sells Ala 18 18 Punta 3 3 Here 16 16 Cart18 0 Lim 16 0 As you can see, the chart is order by office but I got 18 and 16 in Cart and Lim but It should show 0 in both. The LO program get datas from Ala and Here for Cart and Lim. What happens? There is no bug - but two mistakes. As you know, if you omit the optional fourth parameter in the VLOOKUP() function, the data is assumed to be sorted. But as the help text makes clear, a value is always returned in this case - corresponding to the the last value smaller than the criterion. In your case, Ala is next before Cart and Here is next before Lim. To prevent this, you need to add FALSE or 0 as the fourth parameter to VLOOKUP(). Second, there is no magic process by which Calc will guess that you wish for a zero value when no match is found: there is simply no value to return! The value #N/A will be returned instead. If you want zero values here, you need to test for this error value and replace it with zero. You can do this using the ISNA() function, using something like: =IF(ISNA(VLOOKUP(Xn;array;2;0));0;VLOOKUP(Xn;array;2;0)) or, more simply, (new in LO 4): =IFNA(VLOOKUP(Xn;array;2;0);0) I trust this helps. 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 -- 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] It would [not!] be a bug
At 12:08 23/06/2013 -0600, Jorge Rodríguez wrote: ... this other chart No. 2: Oficina Ventas Ala18 Here 16 Punta 3 Total Resultado 37 I've used VLookUp function to get data from chart No 2 to this other chart: Function: =BUSCARV(A3;'Tabla dinámica_Hoja2_1'.$A$11:$B$15;2) Show Should Show Office Sells Sells Ala 18 18 Punta 3 3 Here 16 16 Cart18 0 Lim 16 0 As you can see, the chart is order by office but I got 18 and 16 in Cart and Lim but It should show 0 in both. The LO program get datas from Ala and Here for Cart and Lim. What happens? There is no bug - but two mistakes. As you know, if you omit the optional fourth parameter in the VLOOKUP() function, the data is assumed to be sorted. But as the help text makes clear, a value is always returned in this case - corresponding to the the last value smaller than the criterion. In your case, Ala is next before Cart and Here is next before Lim. To prevent this, you need to add FALSE or 0 as the fourth parameter to VLOOKUP(). Second, there is no magic process by which Calc will guess that you wish for a zero value when no match is found: there is simply no value to return! The value #N/A will be returned instead. If you want zero values here, you need to test for this error value and replace it with zero. You can do this using the ISNA() function, using something like: =IF(ISNA(VLOOKUP(Xn;array;2;0));0;VLOOKUP(Xn;array;2;0)) I trust this helps. 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
