# 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>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<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
---------------------------------</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