Excel

Useful Microsoft Excel Functions & Formulas
Theresa A Scott, MS Department of Biostatistics Vanderbilt University
theresa.scott@vanderbilt.edu

This document contains a series of examples that illustrate some useful functions and formulas you can use in Microsoft Excel. It is meant to be an extension of my “Formulas & Functions in Microsoft Excel” lecture that is available on my website(http://boistat.mc.vanderbilt.edu/TheresaScott) under Current Teaching Material. General Instructions:  All of the demonstrated functions and formulas are calculated in a separate column from the column(s) containing the cells they reference – most often the directly adjacent column. Depending on the layout of your spreadsheet, this may require you to insert a new column between existing columns. The functions and formulas are demonstrated in only a few rows. You will have to copy and paste the function/formula down the appropriate column in your spreadsheet in order for it to calculate the result for every desired row.  A help file, which includes examples, can be accessed for any function by clicking the “Help on this function” link in the Function Wizard after you’ve highlighted thefunction of interest. Functions to be illustrated (listed by category):  Date and Time:  TODAY; NOW: returns the current date, the current date and time, respectively.  DATE: returns the number that represents the date given in Excel date-time code.  TIME: converts the hours, minutes, and seconds given as numbers to an Excel serial number (formatted with a time format).  DAY; MONTH; and YEAR:returns the day of the month (1 to 31), the month (1 to 12), and year from a date, respectively.  HOUR; MINUTE; and SECOND: returns the hour (0 to 23), minute (0 to 59), and second (0 to 59) from a time, respectively.  Engineering:  CONVERT: converts a number (or a time) from one measurement system to another.  Information:  COUNTBLANK: counts the number of blank cells in a specified range ofcells.  Logical:  AND: checks whether all of the arguments are TRUE and returns TRUE only if all arguments are TRUE (returns FALSE otherwise).  OR: checks whether any of the arguments are TRUE and returns FALSE only if all arguments are FALSE (returns TRUE otherwise).  IF: checks whether a condition is met, and returns one value if TRUE, another if FALSE.  Math:  COUNTIF: counts the number ofcells within a range that meet the given condition.  ROUND, ROUNDDOWN, and ROUNDUP: rounds a number to a specified number of digits, down (toward zero), and up (away from zero), respectively.  SUMIF: adds all the cells specified by a given condition, respectively.  Statistical:  COUNTA: counts the number of cells that are not empty.
Page 1



Text and Data:  CONCATENATE: joins severaltext strings into one text string.  LEFT; RIGHT: returns the first, last (respectively) character or characters in a text string, based on the number of characters you specify.  LEN: returns the number of characters in a text string.  LOWER; UPPER: converts a text string to lowercase, uppercase, respectively.  PROPER: capitalizes the first letter in a text string and any other letters in thetext that follow any character other than a letter. Converts all other letters to lowercase letters.  SEARCH: returns the number of the character at which a specific character or text string is first found, beginning with a specific starting number. Often used to determine the location of a character or text string within another text string.  TEXT: converts a value to text in a specific numberformat.  TRIM: removes all the spaces from a text string except for single spaces between words. Often used on text you have received from another application that may have irregular spacing (ie, leading or trailing spaces).

Additional functions not illustrated but often useful (listed by category):1  Date and Time:  NETWORKDAYS: returns the number of whole workdays between two dates. ...