Re: [libreoffice-users] VLOOKUP/Data Pilot? problem

At 20:04 19/02/2013 -0700, John Meyer wrote:
> I'm doing QA in my office and I have a sheet filled with Employee 
> IDs and a simple pass/fail score, like so:
>
> EmpID           Pass/Fail
> 111111             Pass
> 111112             Pass
> 111111             Fail
> 111113             Fail
>
> What I want to do, is in a separate area of the sheet, have the following:
>
> EmpID                   %
> 111111                  50
> 111112                 100
> 111113                  0
>
> Where percentage is a ratio of the number of passes vs the number of 
> all scored results.
>
> I have a column of all possible IDs.  My question is would it be 
> easier to do this via a VLOOKUP or a Data Pilot.  Either way is okay 
> as long as I can get the results.

I'd use neither, I think.  Suppose your data above is in columns A, 
B, and C.  In D2, enter:
=SUMPRODUCT(A$2:A$5=C2;B$2:B$5="Pass")/COUNTIF(A$2:A$5;C2)
and fill this down the column.

With this version, you will need to format column D as Percent.  If 
you preferred, you could add *100 to the formula and format the 
column as Number.

I trust this helps.

Brian Barker


--
For unsubscribe instructions e-mail to: users+help@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