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