ISM3004- Scavenger Hunt Week 6 PDF

Preview

Summary

Professor Olson
Unit 1: Functions (06.01-06.09)
Unit 2: Pivot Tables (06.10-06.11)
Unit 3: Making it Pretty (06.12- 06.14)...

Description

Scavenger Hunt – Week 06 – Excel, part 2

Note: Invest the time and effort required to learn every Excel skills presented this week. Practice them using the sample spreadsheets. Don’t limit yourself to the items listed below. They are intended to draw extra attention to certain ideas. Don’t ignore other techniques and ideas in the lectures!

Unit 1: Functions 



What is the standard Excel syntax for functions? [06.01] o General  Manipulate Data  Numeric- sum, average, financial, etc.  Text- conversion, extract portions of a string, etc.  Logical- check conditions- IF, AND, OR, etc.  Syntax  =functionname(parameters/input) o Examples:  =SUM(A1:A5)  =PMT(0.05,10,1000)  PMT means payment Know how to create functions to… o Calculate sum of a range of cells  Sum  =SUM(1,2,3) o Equals 6  =SUM(B1,B2,B3)  =SUM(range) o (Top left corner cell address:Bottom right corner cell address)  Example (A1:A3) or (A1:C3) Total of range of cells  =COUNT()  How many values/cells are in the range (only cells that contain numbers) o Determine maximum or minimum value in a range of cells  =MAX()  Largest value in a set of values o Ignores logical values and text  =MIN()  Smallest value in a set of values o Ignores logical values and text o Compute average of a range of cells  =AVERAGE()  Gives the mean of the range o Configuring the Status Bar  Useful for quick data sanity checks  Highlight a range and get instant summary statistics  Sum  Count



 Average Right-click Excel status bar to configure (on bottom right corner of Excel)



Variations of the Paste Special capability – understand how to apply them in a spreadsheet [06.02] o Paste Values  Copy cells with formulas/functions  Standard Paste  Puts those calcutations in their new location  Problem if source data not available  Paste Special | Values  Computes the results and pastes those values (only pastes the answers/results, not the formula or formatting)  Copy cells with formulas/functions  Standard Paste  Pastes the formula and cell formatting  Problem if sophisticated formatting  Paste Special | Formulas  Pastes only the formula  Existing formatting, etc. in destination untouched  Suppose you’ve formatted a cell exactly the way you want. Now you want to replicate…  Standard Paste  Pastes the cell formatting and contents  Paste Special | Formats  Pastes only formatting  Data or formulas in destination untouched o Mathematical Operations  Apply a mathematical operation uniformly to all destination cells  Add/Subtract/Multiply/Divide o Example: Copy 5 for Correction; highlight range of quiz grades; paste specialadd  Helps with…  Scaling  Oversight  Error Correction  Transposition- changing the shape (column turns to row) (row turns to column)  Paste Special | Transpose



What CTRL keystroke can display a help dialog about a function’s arguments? [06.03] o 50+ Financial Functions  Accrued Interest  Depreciation  Discount Calculations  Future Value  Internal Rate of Return  Net Present Value  Payments











 Security Pricing  Yield Calculations Know how to use PMT() including setting the correct interest rate and number of periods o Loans  Calculating loan payments =PMT()  Hints:  Ctrl-A displays function arguments dialogue box  Cash in = positive numbers; Cash out = negative  Pay attention to interest periods! Annual, monthly o For monthly =PMT(B3/12,B4,B5)  Amortization Table Know how to use IF() to handle situations where your formula must handle two different situations. [06.05] o Pricing a Sale  Sometimes life is simple…  =A2*B2  But what if we offer discounts? o IF()  =IF(logical_test, [value_if_true], [value_if_false]) Know how to apply AND() and OR() functions in a spreadsheet o AND()  =AND(logical1, [logical2],…)  Everything must be true, or the whole thing is false o OR()  =OR(logical1,[logical2],…)  Only one of them has to be true in order for the whole result to be true How do you handle the situation where your formula must handle three different situations? [06.06] o Two Outcomes  Sometimes life is simple…  =IF(A5/61, “Pass”,”Fail”)  But what if there are more than two possible outcomes?  Good, Better, Best o Nested IF  Logical Test #1  True o Logical Test #2  True  Outcome 3 (Test 1= True: Test 2= True)  False  Outcome 2 (Test 1=True; Test 2=False)  False o Outcome 1 (Test 1=False) How are dates stored in Excel? Hours and minutes? What does that mean you can do with dates in Excel? [06.04] o Under the Hood

Dates are just numbers  1/1/1900 is assigned the number 1  Add 1 for each day after that  Hours and minutes are fractions of a day  One day = 24 hours  One hour = 1/24 = 0.0416666667  Noon = halfway through the day = 0.5 Date Math  Compute time differences  Week later = add 7  Year later = add 365  Examples…  Billing is “Net30” -> add 30 to billing date to get payment due date  Period employed = (termination date) – (hiring date) Sorting  Numbers  Lower came earlier in time Date Functions  =DATE(yr,mo,day)  =YEAR(datevalue)  =MONTH(datevalue)  =DAY(datevalue) 

o

o

o



What the purpose of VLOOKUP? What are the three required parameters? [06.07] o Lookups  Retrieve data from a table, given…  Value to find  Table- skip the headings (U2:X10) not (U1:X10)  Offset- column #  Lookup Type: Closest (TRUE) or Exact (FALSE) *Optional  Vlookups  =VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])  Example: =vlookup(“David”, U2:X10,2,FALSE)  Example (Closest Match): =vlookup(“822.33M”,A2:B11,2)  Example: =vlookup(“Ashley”,U2:X28,2,FALSE) o What happens if the lookup value is not found in the table?  Problem…no match  =vlookup(“Adam”,U2:X28,1) o Returns #N/A o Cannot deal with the situation where the lookup value occurs before the first value in your lookup table o How can you use another type of function inside your VLOOKUP to protect against bogus results when your lookup value is not found?  Solution…IFERROR()  =IFERROR(childfunction(),”errorvalue”)

o o



If the function inside of IFERROR() works normally, then IFERROR() returns the value of that child function But… if the function inside of the IFERROR() results in some kind of error, then the specified “error value” is returned

Text functions – understand what each of the following text functions does (what does it return) and be able to use it in a spreadsheet. [06.08] o Over 30 Text Functions  CONCATENATE()- joins several text items into one text item  FIND()- finds one text value within another (case-sensitive)  LEFT()- returns the leftmost characters from a text value  LEN()- returns the number of characters in a text string  LOWER()- converts text to lowercase  MID()- returns to a specific number of characters from a text string starting at the position you specify  PROPER()- capitalizes the first letter in each word of a text value  RIGHT()- returns the rightmost characters from a text value  SEARCH()- finds one text value within another (not case-sensitive)  SUBSTITUTE()- substitutes new text for old text in a text string  TEXT()- formats a number and converts it to text  TRIM()- removes spaces from text  UPPER()- coverts text to uppercase o TRIM()  Hint: know why you’d use this together with VLOOKUP or comparison operations  Removes excess spaces from text  No leading spaces  No trailing spaces  Only one space between each pair of words  Reduce match/lookup errors  Helps with data from external sources o =TRIM() at work…Example #1  Customer list in A4:A11- there are really only two customers, but their names are typed inconsistently- extra blanks here and there o =TRIM() at work… Example #2  Customer list in A4:E10- correctly formatted names- no extra blanks, sorted by CustName  List of sales in G4:J54- the customer names are a mess- typed inconsistenly with extra blanks here and there  Goal: create VLOOKUP in J4:J54 to show the sales representative responsible for each sale o FIND() and SEARCH()  Hint: they’re identical in syntax and function, but with one key difference  =FIND(tgt,maintext)  Returns the position of “tgt” inside of “maintext”  Case-sensitive  Returns #VALUE if target isn’t found o Optional parameter: start  =FIND(tgt,maintext,start0



o

o

o Search begins at character specified by “start” =SEARCH(tgt,maintext)  Returns the position of “tgt” inside of “maintext”  NOT Case-sensitive  Returns #VALUE if target isn’t found o Optional parameter: start  =FIND(tgt,maintext,start) o Search begins at character specified by “start”

Extracting a Substring [06.09]  =LEFT(text,numchars)  =RIGHT(text,numchars)  =MID(text,startchar,numchars) =CONCATENATE()  Join one or more text strings into one text string  =CONCATENATE(text1,[text2],…)

Unit 2: Pivot Tables 









Why does one use a pivot table? [06.10] o Why Pivot Tables?  Summarize data  Count  Total  Maximum  Minimum  Average  Group by categories How do you create a pivot table? o Creating a Pivot Table  Click any cell in the range to be analyzed  Insert Ribbon Bar | Pivot Table o Ex. 1- Grades  Summarize data  Count- how many students in each year?  Maximum- what was the highest grade for each year?  Minimum- and what was the lowest?  Average- and the average?  Group by numeric grade score Suppose your pivot table is providing a “COUNT” summary statistic. How do you change that to something else, like “SUM”? o Right click when you drag to “Values” box How do you group the data in your pivot table rows? What steps in the GUI? o Click on cell in range click mouse pad with two fingers-> group o PivotTable Analysis -> Group Selection -> Group Try the practice examples. [06.11] o Example 1- QB Recruiting  Step 1- understand the data!

Step 2- build some pivot tables  (1) Total yards gained?  (2) How many attempts?  (3) Average yards per attempt?  (4) How many completions?  (5) Average yards per completion? Example 2- Bank Accounts  Step 1- understand the data!  Step 2- build some pivot tables  (1) Number of new accounts opened each week by branch  (2) Which branch had the greatest dollar volume of new IRA accounts openedperformance award for that branch  (3) What account type does the Mall branch open most often?  (4) “Big” accounts are those over $25,000. Show the number of “small” and “big” accounts opened at each branch 

o

Unit 3: Making it Pretty 









Why does one use Protected Cells? [06.12] o Protected Cells  Prevent unauthorized changes  Reduce user errors What is the procedure for using Protected Cells? o Protected Cells  Procedure…  (1) Format unprotected cells as “Unlocked” (Cells: Format Cells)  (2) Review Ribbon Bar- “Protect Sheet” (Just click Ok)  (3) Set options How is conditional formatting different from normal cell formatting? Why would you use it? [06.13] o Conditional Formatting means Excel programmatically changes the formatting as the data changes o Normal Cell Formatting is when you physically click highlight, etc. What is the procedure for configuring a range of cells to have conditional formatting? o Conditional Formatting  Contents of cell affects its formatting… live!  Home Ribbon Bar  Rules-based highlighting  Top/Bottom  Data Bars  Color Scales  Icons What 4 options do you have for “scaling” a printout? How are they different? In what situations would you use each of them? [06.14] o Print Menu  Paper handling  Scaling  (1) No Scaling (Print Sheets at their actual size)  (2) Fit Sheet on One Page (Shrink the printout so that it fits on one page)





 (3) Fit All Columns on One Page (Shrink the printout so that is one page wide)  (4) Fit All Rows on One Page (Shrink the printout so that it is one page high) How do you configure Excel to print the same row headers at the top of every page in a multi-page printout? To print the same column headers at the left of every page? o Page Layout  Scaling  Headers & Footers  Print Area  A1:C13: Print Area; Set Print Area  Print Titles  Repeat Rows & Columns (Like Freeze Panes) o Print the same row headers headers at the top of every page in a multi-page printout; To print the same column headers at the left of every page?  Page Layout tab; Choose Print Titles option; Select desired rows that will be repeated How do you setup page headers and footers? (different from column and rows headers) o Page Layout tab; Print Title option; Select Header and Footer tab  Headers- Custom Header and put Ok  Footers- Custom Footer and put Ok

*Turn Freeze Panes back on when you upload the project...