1 / 12

Helpful Excel functions/features for using the SigTerms software

Helpful Excel functions/features for using the SigTerms software. Chad Creighton April 29, 2008. When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered range. Microsoft Excel indicates the filtered items with blue.

alden
Télécharger la présentation

Helpful Excel functions/features for using the SigTerms software

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Helpful Excel functions/features for using the SigTerms software Chad Creighton April 29, 2008

  2. When you use the AutoFilter command, AutoFilter arrows appear to the right of the column labels in the filtered range. Microsoft Excel indicates the filtered items with blue. You use custom AutoFilter to display rows that meet complex criteria; for example, you might display rows that contain values within a specific range (e.g. p<0.01) Data->Filter->AutoFilter • Unfiltered range • Filtered range

  3. MATCH Worksheet function MATCH(lookup_value,lookup_array,match_type) • Lookup_value   is the value you use to find the value you want in a table. • Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. • Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. • Lookup_array   is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference. • Match_type   should be set to 0 for our purposes.

  4. INDEX Worksheet function INDEX(array,row_num,column_num) • array   is a contiguous range of cells from which you want to retrieve a value. • row_num   is the row in the array of the value that you want to retrieve. • Hint: use MATCH to get the row number. • column_num   is the column in the array of the value that you want to retrieve. • If the range has only one column, column_num should be 1.

  5. Example of using MATCH, INDEX, and Data Filter features Link a set of microRNA common names to a list of mirBase accession numbers

  6. Additional hints • After you join two columns of values using MATCH and INDEX, how to get just the values without the formulas? • Otherwise, if you close the second workbook, you lose the joined values in the first workbook • Copy the columns with the values, then paste “Values” in the same location using the Edit->Paste Special option.

More Related