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