# intro to cleaning data

## Handy formulas

 Function Description Formula Get data or apply function to data from a different sheet =Sheet1!A1 Apply function to a column (shift-select cells, function must be in top cell) Control-d AVERAGE Calculates the average =AVERAGE(A2:A10) MEDIAN Finds the value (number) in the middle of a range. =MEDIAN(A2:A10) Calculate percent (A2 is the smaller number) =A2/B2*100 Calculate percent change (B2 is the new number, A2 is the old number) =((B2/A2)-1)*100 EXACT Check for duplicates. =EXACT(A2:A10) COUNTIF Counts the number of cells where a given value appears. (range, criteria) Criteria can be a number or text that appears in cells. This exmple uses a ZIP Code. =COUNTIF(A2:A10, 94705 ) TRIM Removes spaces from beginning and end of text. =TRIM(A2:A10) CLEAN Removes all non-printable characters from text. =CLEAN(A2:A10) CONCATENATE Joins text items into one text item — (&” “] adds a space between cells =CONCATENATE(A2&" ", B2) LOWER Converts text to lowercase. =LOWER(A2:A10) UPPER Converts text to uppercase. =UPPER(A2:A10) PROPER Capitalizes the first letter in each word of a text value. =PROPER(A2:A10) FIXED Formats a number as text with a fixed number of decimals. =FIXED(E2,1) MID Returns a specific number of characters from a text string starting at the position that you specify. Useful for converting nine-diget ZIP codes. (Cell, Start position, number of chars) =MID(A2, 1,5) RIGHT Returns the rightmost characters from a text value. =RIGHT(A2,5) LEFT Returns the leftmost characters from a text value. =LEFT(A2,4) REPLACE Replaces or add characters (For example, adding digits back to ZIP code). =REPLACE(A2,6,5,+"-3428") SUBSTITUTE Substitutes old text with new text. =SUBSTITUTE(A2:A10, ”Oregon”, “OR”)