If statements

EXCEL TRAINING!

1. 
   IF STATEMENTS
   
   1. 
      =IF([formula],[value if true],[value if false])
      
      1. 
         =IF(D2<12,1,0) returns 1 if D2 is greater than 12, 0 if D2 is not greater than 12
         
      2. 
         If D2 is not a number, or equals 12, or is less than 12, the value will be zero
         
      3. 
         If D2 has an error, the output will also be an error
         
   2. 
      =SUMIF sums together numbers by matching cells to broader categories
      
      1. 
         =SUMIF(B:B,B2,C:C) outputs the sum of all values in column C where the value in column B is the same as B2
         
      2. 
         =C2/SUMIF(B:B,B2,C:C) tells us the percentage of the SUMIFed values from column C that cell C2 represents.
         
   3. 
      =AVERAGEIF gives the average for a specified category
      
      1. 
         =AVERAGEIF(B:B,B2,C:C) outputs the average for all values in column C where the value in column B is the same as B2
         

2. 
   PIVOT TABLES
   
   1. 
      Pivot tables will change your life
      
   2. 
      To create: Select columns. Insert > Pivot Table
      
   3. 
      Use rows to tally up averages & counts
      
      1. 
         Rows = email, values = count of recipients
         
      2. 
         Add contrib_amt to the list of values
         
   4. 
      Adjust values metrics by clicking on item and using “Value Field Settings”
      
      1. 
         Change contrib_amt to average instead of count
         
      2. 
         Add a values column sum of contrib_amt
         
   5. 
      Use rows and columns to create crosstabs
      
      1. 
         Rows = email, columns = contrib_amt, values = count of recipients
         
   6. 
      Add additional data to the pivot table
      
      1. 
         Go back to pivot table, use Options > Change Data Source to add column F
         
   7. 
      Nested rows within a pivot table
      
      1. 
         Add Morning_Donation to rows
         
      2. 
         Move Morning_Donation higher in the heirarchy
         
   8. 
      Refresh data in a pivot table
      
      1. 
         Go back to main data sheet and alter Morning_Donation to have text values rather than binary values
         
      2. 
         Go back to pivot table, use Options > Refresh
         
   9. 
      Copy-pasting from a pivot table – generally best to use “paste as values”
      
   10. 
       Pivot charts – the new thing changing my life, and now yours
       

3. 
   VLOOKUP
   
   1. 
      VLOOKUP will also change your life
      
   2. 
      You need four pieces of information for the VLOOKUP command
      
      1. 
         =VLOOKUP([1],[2],[3],[4])
         
         1. 
            The name of the cell you are using as a key (i.e. B2)
            
         2. 
            The table of cells you are looking things up in (i.e. A1:B4 – but make sure the first column matches your key)
            
         3. 
            The column number of the data you want to display
            
         4. 
            Whether you will accept close matches (TRUE) or only exact matches (FALSE)
            
      2. 
         =VLOOKUP(B2,'Subject Lines'!A1:B4,2,FALSE)
         
   3. 
      Watch out for shifting cell references!
      
      1. 
         Try these options instead
         
         1. 
            =VLOOKUP(B2,'Subject Lines'!$A$1:$B$4,2,FALSE)
            
         2. 
            =VLOOKUP(B2,'Subject Lines'!A:B,2,FALSE)
            
   4. 
      There is also HLOOKUP, which functions similarly but does a horizontal lookup instead of a vertical lookup.
      
   5. 
      What to do if you get the dreaded “#N/A”: use an IF and ISNA statements
      
      1. 
         =IF(ISNA(VLOOKUP(L2,Sheet2!L:L,1,FALSE)),0,VLOOKUP(L2,Sheet2!L:L,1,FALSE))
         

4. 
   LEFT, RIGHT, CONCATENATE
   
   1. 
      Easy commands that help truncate & remix text
      
   2. 
      =LEFT(B2,6) will return the first six characters in cell B6
      
   3. 
      =RIGHT(B2,6) will return the last six characters in cell B6
      
   4. 
      =RIGHT(LEFT(B2,6),4) will return only the last four characters out of the first six characters of B2 – in other words, the characters in slots 3,4,5,6
      
   5. 
      =CONCATENATE(A2,D2) smashes the contents of those two cells together
      
   6. 
      =CONCATENATE(A2," - ",D2) returns A2 and C2 put together, but with spaces and a dash in between)
      
   7. 
      =CONCATENATE(A2," - ",LEFT(B2,6)) returns A2, a space and a dash, and the left six characters of B2
      

5. 
   CONDITIONAL FORMATTING
   
   1. 
      Fun stuff to play around with, and makes your tables much easier to interpret
      
   2. 
      Found in the Home ribbon (Conditional Formatting button)
      
   3. 
      Favorites:
      
      1. 
         Color scales, shade according to each cell’s relative rank within a series
         
      2. 
         Greater than / less than / equal to
         
      3. 
         Above average
         
   4. 
      Conditional formatting can be especially useful when you have data that will change/update and you want to see particular values quickly and easily
      
   5. 
      Modify/delete rules with “Manage Rules” menu option
      
      1. 
         This is also how you rank rules, if the same cell has multiple rules
         

6. 
   FILTER AND SORT
   
   1. 
      Select all columns in your dataset – if you leave columns out, they will get out of order relative to the other data
      
   2. 
      Use the Sort button in the Data ribbon to create tiered sorts
      
   3. 
      Alternatively, click Data > Filter
      
   4. 
      Click on the little arrows and sort by whatever column you like, or choose specific categories to display.
      
   5. 
      Caution: When data gets hidden, it doesn’t sort.
      

7. 
   OTHER FUN FUNCTIONS & FORMULAS
   
   1. 
      Text to Columns
      
      1. 
         Splits text apart either using specific characters (like a space, a tab, a comma, etc) or delimited by length
         
   2. 
      LEN is the Length function, and returns the number of characters in a given cell
      
   3. 
      PERCENTRANK delivers the percentile of an individual value within an array.
      
      1. 
         =PERCENTRANK(C:C,C2) gives the percentile rank of C2 among all values in column C.
         
   4. 
      AVERAGE, MAX, MIN, MEDIAN, MODE
      
      1. 
         Pretty self-explanatory, these functions give you descriptive statistics for any range of cells you want
         
   5. 
      Name manager – give a particular block of cells a name so that you can keep referring to it, rather than
      
   6. 
      RAND delivers a random number between 0 and 1 – handy for picking a sample
      
      1. 
         Caution: random numbers will regenerate, so copy-paste-as-values
         
      2. 
         Pick a winner by generating a random number of all entrants and the going with the highest (or lowest)
         

8. 
   CHARTS
   
   1. 
      In the Insert ribbon there is a section for adding various types of charts
      
   2. 
      Alternatively, use Pivot Charts!
      
   3. 
      Excel is sometimes bad at anticipating how you want to display your data
      
   4. 
      Figure out the types of charts you use the most and learn how to trick Excel into making them for you quickly.
      
   5. 
      Modify the displayed data by clicking into the chart and then changing the dimensions of the colored boxes that appear around your data
      
   6. 
      Change the formatting by right-clicking on any element of the chart and choosing “format [element name]”
      
   7. 
      Change the max/min and units of the axes using “format axis”