Vlookup and CounIF family

• I think mastering the vlookup/hlookup functions is critical for this class
• Getting approximate table data with the VLOOKUP function
  • v- vertical
  • h -horizontal
  • look up a value in a table
  • =vlookup(value to lookup, table, column of table to return)
  • =hlookup(value to lookup, table, row of table to return)
  • The numerical data in the table must be in ascending order.
  • The first data item should be smaller than the lowest value you will look up.
  • He also shows how to use the function interface
  • This is my "standard" use of vlookup.
• Getting exact table data with the VLOOKUP function
  • This exercise shows a much larger table.
  • He is looking for exact data match (in text)
  • He also shows looking at multiple columns.
  • I really would rather give the exact table boundaries, not the column method he uses.
  • Note the use of 0 or FALSE for an exact match.
• Using the COUNTIF family of functions.
  • COUNTIF, SUMIF, AVERAGEIF, ...
  • Count if something matches a criteria
  • =countif(range, item)
  • =sumif(range, item, range to sum)
  • =averageif(range, item, range to average)

Payday!

• Start with this file
• The Payday sheet contains
  • This represents the payroll for a company for one week.
  • Column A contains employee names
  • Column B contains the hours worked
  • Column C contains the employee status code
    • H : Hourly
    • S : Salary
    • E : Exempt
  • Column D contains the rate of pay, hourly rate for H and E employees and annual salary for S employees.
• The Assumptions sheet contains
  • All members of the company are members of the coffee club. The weekly donation is determined by the coffee club table in cells B3:C9
  • The Overtime table E2:F7 determines the overtime rate for employees by status.
  • The tax table determines the tax rate for employees by weekly salary. This table is in cells A12:F13
• Begin by calculating the regular hours worked in column E
  • If the hours worked is less than or equal to 40, this is the number.
  • If the hours worked is greater than 40, then the number is 40.
• Calculate the overtime hours in column F
  • This is just the hours worked minus the regular hours.
• Calculate the overtime rate in column G
  • This is a vlookup in the Overtime Table times the hourly rate.
• Calculate the Total Pay in column I
  • If it is a salary employee it is rate/52
  • Otherwise it is Rate * regular hours + overtime Pay
• Calculate the Federal Tax Rate in Column J
  • Look up the Total pay in the Tax Table.
• Calculate the Federal Tax in column K
  • This is just the tax rate * total pay
• We really should calculate state tax, in PA it is a flat 3.07%
• And Washington Township is 1%.
• Calculate the coffee club fee in column l
  • This is a lookup in the coffee club table based on the total pay.
• Calculate the Final Pay in column M
  • Total Pay (I) - Tax (K) - coffee club (L)
• Now use the countif family of functions to complete the statistics table.
  • Count the number of employees with the stat "H", "E" and "S" in P6:r6
  • (countif)
  • Sum up the hours, overtime hours, overtime pay and total salary in the other fields.
  • (sumif)
• 
• 
• 
•