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