Excel funtion question

Formulas 101

INSTRUCTIONS: 1) Populate column E to calculate Leads per Call (column C divided by column B), and double click the lower-right corner of the cell to apply the formula down to all rows 2) Drag the formula from cell E2 to F2. Is "Sales per Call" being calculated properly? Use the F2 shortcut to check which cells are being references by the formula, and try using the Trace Precedents option in the Formula tab to do the same 3) Return to the formula in column E and update your reference types (using the F4 shortcut) to make sure the calculation always reads from column B, then drag the formula to column F and apply to all rows

Logical Operators

INSTRUCTIONS: 1) Populate column D to return "PASS" if the score in column C is greater than or equal to 60, otherwise return "FAIL" 2) Populate column E to return a letter grade based on the score in column C, using the logic below: A = >=90 B = 80-89 C = 70-79 D = 60-69 F = <60 3) Populate column F to return "OUTLIER" if the score in column C is either <60 or >90, otherwise return "AVG" 4) Populate column G to return "Male Achiever" if Gender = M and the score in column C is >95, "Female Achiever" if Gender = F and the score in column C is >95, otherwise "None"

Stats Functions

INSTRUCTIONS: 1) Use SUMIF to populate cell H5, which calculates the Total Sales based on the product category in cell H2 2) Use SUMIFS formulas to populate Total Sales and Total Revenue in the table above, based on the store location in column G and the product type in cell H2 (HINT: practice using reference types) 3) Use a COUNTIFS formula to calculate Number of Product IDs by store location and product type

Lookup Functions

INSTRUCTIONS: 1) Use a VLOOKUP formula to populate the State Abbreviation field using data from the "State Abbreviations" tab 2) Use a VLOOKUP formula to populate Per Capita Income using data from the "State Income (2001-2004)" tab (HINT: you may need to add a new field) 3) Change the errors in the Per Capita Income column to display "NO INCOME DATA" rather than #N/A

State Abbreviations

State Income (2001-2004)

Text Functions

INSTRUCTIONS: 1) Use the LEFT function to populate the Product ID column, equal to the first six characters of the Product Key in column A 2) Turns out that some product IDs may be longer than 6 characters... In column C, update the Product ID formula to combine LEFT and SEARCH functions, allowing you to return ALL characters to the left of the first "-" 3) Populate the Product Category column in column D using MID and SEARCH, to return the two characters immediately following the first "-" in the Product Key 4) Populate Product Size using IF, ISNUMBER, and SEARCH functions, based on the logic below: If Key includes "SMALL", Size = Small If Key includes "MEDIUM", Size = Medium If Key includes "LARGE", Size = Large If Key includes "XL", Size = XL 5) Instead of using IF(ISNUMBER(SEARCH)), write a formula in column F to pull all characters from the right of the Product Key following the underscore ("_") (hint: use RIGHT, LEN, and SEARCH) 6) How can we extract the city name ("BOS", "NYC" or "CHI"), which follows the second dash in the Product Key? Start by creating a new version of the product key in column G which substitutes a pipe ("|") in place of the second dash ("-"), then write a function in column H to return the 3 characters after the pipe

Date & Time Functions

INSTRUCTIONS: 1) Use the TODAY and NOW functions to populate cells C3 and C4 2) Based on the current time, populate the Year, Month, Day, Hour, Minute, and Second in row 7 3) Use the WEEKDAY formula to calculate the current day of the week, then custom format the cell to show the full day name rather than an index number 4) Use the WORKDAY function to determine what date will fall 50 days from now, excluding weekends 5) Use the NETWORKDAYS function to count the number of workdays between the date in cell C2 and the current date 6) Use the EOMONTH function to determine the last day of the current month, the first day of the current month, and the first day of the current year 7) Drag the date in cell B19 down to row 30, and test filling the series by day, weekday, month, and year

Formula Formatting

INSTRUCTIONS: 1) Format the Revenue column as currency, with no decimal places, and use the format painter to apply the same formatting to columns C, D, and E 2) Format the Profit Margin column as a percent, with 1 decimal place 3) Use conditional formatting to add a color scale to the Profit Margin column (low values = red, high values = green) 4) Use conditional formatting to add directional arrow icons to the Revenue Change column, and edit to show an up arrow for values >200 and a down arrow for values <-200 5) Create a formula-based formatting rule to format the dates in column A as bold with dark red text and light red fill when the profit margin in column G is <40% 6) Add a formula-based formatting rule to highlight rows in columns A-F light yellow when the Top Product Category = "Kids"