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


Oh ! I can see my mistake ... thank you very much for help me

Regards,

Jorge Rodríguez

----- Mensaje original -----
De: "david_lynch" <david_lynch@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
Ala                    18
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
Cart        18       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+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


-- 
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.