Re: Excel function (Owen Brimijoin )

Subject: Re: Excel function
From:    Owen Brimijoin  <owen@xxxxxxxx>
Date:    Wed, 27 Jun 2012 10:20:27 +0100
List-Archive:<http://lists.mcgill.ca/scripts/wa.exe?LIST=AUDITORY>


This is a multi-part message in MIME format.
--------------070200060904010901070901
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Vinay,
Here's a quick and dirty solution (hopefully the text formatting looks ok):

Data1 Data2 Data 3 Pattern LookupVal Category
1            0            1         B                0            N/A
1            0            0         A                1               A
1            1            0         B                2               B
0            0            0         N/A            3              C
1            1            1         C
0            1            0         A
0            1            1         B

Assuming the cell "Data1" is at 'A1' in the spreadsheet, the formula 
under "Pattern" is:

=VLOOKUP(COUNTIF(A2:C2,"<>0"),E$2:F$5,2)

This counts up the number of nonzero entries in each row and looks up 
what letter to assign it from the table at the right-hand side.

best,
-Owen.

---------------------------------
W. Owen Brimijoin
Investigator Scientist
MRC Institute of Hearing Research
Glasgow, United Kingdom
owen@xxxxxxxx
---------------------------------


--------------070200060904010901070901
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <font face="Helvetica, Arial, sans-serif">Hi Vinay, <br>
      Here's a quick and dirty solution (hopefully the text formatting
      looks ok):<br>
      <br>
      Data1 Data2 Data 3 Pattern LookupVal Category<br>
      1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
      N/A<br>
      1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
      &nbsp;&nbsp; A<br>
      1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; B&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
      &nbsp;&nbsp; B<br>
      0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; N/A&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;
      C<br>
      1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; C&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <br>
      0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; A<br>
      0&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; B<br>
      <br>
      Assuming the cell "Data1" is at 'A1' in the spreadsheet, the
      formula under "Pattern" is: <br>
      <br>
      =VLOOKUP(COUNTIF(A2:C2,"&lt;&gt;0"),E$2:F$5,2)</font> <font
      face="Helvetica, Arial, sans-serif"><br>
      <br>
      This counts up the number of nonzero entries in each row and looks
      up what letter to assign it from the table at the right-hand side.
    </font> <font face="Helvetica, Arial, sans-serif"><br>
      <br>
      best, </font> <font face="Helvetica, Arial, sans-serif"><br>
      -Owen. <br>
      <br>
    </font>
    <pre class="moz-signature" cols="72"><font face="Helvetica, Arial, sans-serif">---------------------------------
W. Owen Brimijoin
Investigator Scientist
MRC Institute of Hearing Research
Glasgow, United Kingdom
<a class="moz-txt-link-abbreviated" href="mailto:owen@xxxxxxxx">owen@xxxxxxxx</a>
---------------------------------</font></pre>
  </body>
</html>

--------------070200060904010901070901--
This message came from the mail archive
/var/www/postings/2012/
maintained by:
DAn Ellis <dpwe@ee.columbia.edu>
Electrical Engineering Dept., Columbia University