Excel Expert Session C (2018)
1. Enter the formula: =E4+1 into cell B5 2. AutoFill cells B5 and E4 to the end of the range. - You now have a dynamic project plan that will adjust the end date of the project based upon your work estimates and the number of resources that you assign to each task
Lesson 3-10: Use date offsets to manage projects using the scheduling equation: *Add a formula to show the start date for the Pour Footings task one day after the foundations are dug.
1. Enter the formula: *=B4+C4/D4-1* into cell E4. - Note that, because of the rules of precedence, this is the equivalent of *=B4+(C4/D4)-1* - One day is subtracted because a job of one day's duration will begin and end on the same day
Lesson 3-10: Use date offsets to manage projects using the scheduling equation: *Calculate the End Date for the first task.*
In order to understand project management, you need to first understand the scheduling equation: *Time = Work/Units*, as in, if takes 1 man ten days to dig a hole then: *10 days = 10 days/1 Man*, so it would take 2 men: *5 days = 10 days/2 Men* - In this lesson, you'll learn how to create formulas that will enable task lengths to dynamically re-scale as either estimate work estimates (e.g. days) times or resources (e.g Men) change.
Lesson 3-10: Use date offsets to manage projects using the scheduling equation: *In order to understand project management, you need to first understand the scheduling equation....*
1. Change the value in cell D4 to 1. [make sure the equation *=B4+(C4/D4)-1* is in E4] - Notice that the task now takes three days to complete. 2. Revise your estimate of the work to 8 Man-Days and assign two men to the task. - This time, the task takes four days to complete.
Lesson 3-10: Use date offsets to manage projects using the scheduling equation: *Test the scheduling equation. by taking two men off the job. Then by increasing the work estimate to 8 days of work, and 2 men*
5. 1. Type =D into cell C4. The DATE function is the first in the drop-down list: 6. Press the <Tab> key to enter the formula into the cell. 7. Click the Insert Function button on the left of the formula bar: - The Function Arguments dialog is displayed. 8. Complete the dialog as follows: *YEAR(A4) MONTH(A4)+3 DAY(A4)*
Lesson 3-11: Use the DATE function to offset days, months and years (Uses file- Service Schedule-1):*Put a DATE formula in cell C4 that will calculate a date that is three months later than the 20-day inspection date*
9. 1. Type =D into cell D4. The DATE function is the first in the drop-down list: 10. Press the <Tab> key to enter the formula into the cell. 11. Click the Insert Function button on the left of the formula bar: - The Function Arguments dialog is displayed. 12. Complete the dialog as follows: *YEAR(A4)+1 MONTH(A4) DAY(A4)*
Lesson 3-11: Use the DATE function to offset days, months and years (Uses file- Service Schedule-1):*Put a DATE formula in cell D4 that will calculate a date that is twelve months later than the 3-month inspection date*
1. Type =D into cell B4. The DATE function is the first in the drop-down list: 2. Press the <Tab> key to enter the formula into the cell. 3. Click the Insert Function button on the left of the formula bar: - The Function Arguments dialog is displayed. 4. Complete the dialog as follows: *YEAR(A4) MONTH(A4) DAY(A4)+20*
Lesson 3-11: Use the DATE function to offset days, months and years (Uses file- Service Schedule-1):*Put a formula in cell B4 that will calculate the date for the 20-day inspection based upon the date in cell A4.*
3. Select cells B8:C12. 4. Right-click the selected cells and click Format Cells... from the shortcut menu. 5. Click General in the Category list. 6. Click the OK button. - Notice that all of the time values begin with zero. This is because no date is associated with them. Understanding this is very important when creating time formulas. - Notice that the time in cell B8 is 0.333333. This is because at 08:00 approximately 33.333333% of the day has elapsed, as eight hours is approximately 33.333333% of 24.
Lesson 3-12: Enter time values and perform basic time calculations (Uses file-Time Sheet-1): *Change the formatting to General for the hours worked*
7. Add the formula =C8-B8 to cell D8 - The result shows that Frank worked for 37.5% of the day on Monday.
Lesson 3-12: Enter time values and perform basic time calculations (Uses file-Time Sheet-1): Add a formula to cell D8 that will calculate the number of hours worked.
8. Select cells B8:D12. 9. Right-click the selected cells and click Format Cells... from the shortcut menu. 10. From the Custom category, set the format to: hh:mm 11. Click the OK button. The number of hours and minutes worked is now shown in cell D8
Lesson 3-12: Enter time values and perform basic time calculations (Uses file-Time Sheet-1): Format cells B8:D12 to show times in the 24-hour format.
1. Add a start time of 08:00 and finish time 17:00 for Monday 2. Add a start time of 10:00 AM and finish time 6:00 PM for Tuesday. - AM/PM notation is preferred by some users, but is more error prone than the recommended 24-hour notation. - Make sure that you leave a space between the time and the AM/PM indicator, otherwise Excel will interpret the value as text rather than time.
Lesson 3-12: Enter time values and perform basic time calculations (Uses file-Time Sheet-1): Set up the worksheet
The time 08:00 but does not represent any date In this lesson, you'll focus on entering time values into a worksheet without any associated date and learn how to avoid the mistakes commonly made when working with time values
Lesson 3-12: Enter time values and perform basic time calculations:*the date serial number 0.3333 represents*
Notice the problem in cell D13 (total hours worked is zero) 1 Add a SUM finction to D13: =SUM(D8:D12) 2. Notice that this also returns an incorrect value (14) -Remember that the date serial numbers in *cells D8:D12 contain the percentage of each 24-hour day* that was worked. When they are added together they will add up to more than one. Because cell D13 is formatted to show times, it will ignore the whole number part of the date/time serial number, believing that this represents a date 1. Click on *cell D13.* 2. Click: *Home->Number->Dialog Launcher.* - The Format Cells dialog appears 3. Click the *Custom* category. a. Notice that the current format is hh:mm.
Lesson 3-13: Perform time calculations that span midnight (file-Time Sheet-2): *C. Display the value in cell D13 correctly by adjusting the format: Correct the value in D13 to properly display the total of hours worked: Steps 1-2 of 5*
4. Manually type in the new custom format: *[h]:mm.* a. The *square brackets *around the *[h] *means that where there is a *whole number *in the date serial number it should be regarded as *time data.* b. For example, the date serial number *2.0 *will be interpreted as meaning *48 hours* and *not* *midnight on 2nd January 1900*. The serial number *1.5* will be interpreted as *36 hours* and *not midday on 1st January 1900.* 5. Click *OK. *The worksheet now displays correctly:
Lesson 3-13: Perform time calculations that span midnight (file-Time Sheet-2): *C. Display the value in cell D13 correctly by adjusting the format: Correct the value in D13 to properly display the total of hours worked: Steps 4-5 of 5*
1. AutoFill the formula in cell D8 to cells D9:D12 -Notice the problem in D10 and D11, as Excel cannot display a time value that is negative 2. Change the formula in cell D8 to the following: =IF(C8>B8, C8-B8, C8+1-B8) 3. Autofill down to cell D12 a. The times are now correctly calculated:
Lesson 3-13: Perform time calculations that span midnight: B. Correct the formulas in cells D8:D12 so that time is correctly calculated, even when the times span midnight.
1. Type the formula: =D8x24x$B$5 into cell E8 a. The reason we *must multiply by 24* is because the values in D8:D12 represent the *percentage of each day* that was worked. b. An *absolute reference* must be added for *B5* (the hourly rate of $15.00) in order to allow the range *E8:E12* to be *Autofilled. * 2. *AutoFill* the formula to cells *E9:E12.*
Lesson 3-14 (file-Time Sheet-3) : Understand common time functions and convert date serial numbers to decimal values: A. Add formulas to cells E8:E12 to calculate earnings.
1. Type Current Date and Time into cell A15. 2. Click on cell B15. 3. Type =NOW 4. Press the <Tab> key twice. 5. If necessary, widen columns A and B so that they are wide enough to see all of their contents.
Lesson 3-14 (file-Time Sheet-3) : Understand common time functions and convert date serial numbers to decimal values: Add a NOW function to cell B15 to display the current date Time Sheet-3 and time.
1. Type *Current Time* into cell *A16. 2. Click in cell *B16.* 3. Click: *Formulas->Function Library->Date&Time->TIME.* a. The *Function Arguments *dialog is displayed
Lesson 3-14 (file-Time Sheet-3) : Understand common time functions and convert date serial numbers to decimal values: C. Add a TIME function to cell B16 to display the current time: *1ST three steps (up to function arguments dialog)*
4. Enter the following values for each argument: a. *HOUR(NOW())* b. *MINUTE(NOW())* c. *SECOND(NOW())* 5. Click the *OK* button
Lesson 3-14 (file-Time Sheet-3) : Understand common time functions and convert date serial numbers to decimal values: C. Add a TIME function to cell B16 to display the current time: *Function Arguments (step 4)*
D. Re-calculate the worksheet to update the current time. 1. *Click: Formulas->Calculation->Calculate Sheet*
Lesson 3-14 (file-Time Sheet-3): Understand common time functions and convert date serial numbers to decimal values: *D. Re-calculate the worksheet to update the current time*
4. *Paste the function* (previously copied) into the *Value_if_true* and *Value_if_false *text boxes 5. Set the *Logical test* argument to: *HOUR(B6)>=9* 6. *Edit* the *Value_if_true argument *so that it *offsets * the previous train time by *one hour *rather than ten minutes: a. Should read as follows: *TIME(HOUR(B6)+1,MINUTE(B6),SECOND(B6))* 7. Click the *OK *button.
Lesson 3-15: Use the Time function to offset hours, minutes and seconds (Train Timetable-1): B. *Change the formula in cell C6, so that train arrivals after 09:00 are every hour, instead of every 10 minutes: Steps 4-7*
1. Click in cell C6. 2. Click: *Formulas->Function Library->Date & Time->Time.* a. The *Function Arguments* dialog appears 3. Enter the following values for each argument: *Hour: HOUR(B6) .... Minute: MINUTE(B6)+10 .... Second: SECOND(B6)* 4. Click the *OK* button. The correct time of the next train appears in cell *C6.* 5. *AutoFill* the formula in cell *C6* to cells *D6:K6.*
Lesson 3-15: Use the Time function to offset hours, minutes and seconds (file-Train Timetable-1): *A. Put a formula in cells C6:K6 that will calculate the arrival times of subsequent trains, with trains running every 10 minutes. *
1. *Copy the function* from cell *C6 excluding the = sign* by clicking in cell C6, selecting the function in the formula bar (but not the = sign) a. Should read as: *TIME(HOUR(B6),MINUTE(B6)+10,SECOND(B6)* 2. Click cell *C6* again and then press the *<Delete>* key to remove the existing formula from the cell. 3. Click: *Formulas->Function Library->Logical->IF.* a. The Function arguments dialog is displayed
Lesson 3-15: Use the Time function to offset hours, minutes and seconds (file-Train Timetable-1): B. *Change the formula in cell C6, so that train arrivals after 09:00 are every hour, instead of every 10 minutes: 1ST Three Steps *
You'll construct a train timetable that will run a service every 10 minutes before 09:00 and every hour thereafter. - The formulas in this worksheet will be constructed so that the *entire timetable* can be *automatically updated* by simply *changing the First Train times* in column B. - This will work because all of the *subsequent journeys* will be *defined as offsets* from the *first train's arrival time* using the *TIME function*
Lesson 3-15: Use the Time function to offset hours, minutes and seconds: What is the objective of this lesson?
1. Click in cell E10. 2. Click: Formulas->Function Library->Logical->IF. 3. Enter the following values into the dialog (see text above for explanation): Logical test: OR(B10>5,AND(C10>D10,B10>2)) Value_if_true: C10*0.02,0 Value if false: 0 4. Click the OK button. 5. AutoFill the formula from cell E10 to the end of the range.
Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria:
To create a function that will calculate the bonus due to employees, based on the following criteria: - A bonus of 2% of sales will be paid to employees who meet the following criteria: A bonus will be paid if: If Sales > Target AND Years Service > 2 OR Years Service > 5
Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria: What is the overall objective of this lesson?
1. Automatic: Whenever you change the value in a cell, all values that reference that cell are automatically recalculated. This is the default. 2. Automatic except for data tables: This is similar to Automatic, but tables will only be recalculated when one of the values within a table is changed (you learned about tables in: Session One: Tables, and Ranges). 3. Manual: Calculation will only take place when the <F9> key is pressed
Lesson 3-17: Understand calculation options (manual and automatic) It is possible to change the calculation mode used by Excel. The three modes available are:
1. The formula in column D: *=C8/(1-$D$5)* ... calculates the selling price based upon the gross profit stated in cell D5. 2 Change the gross profit to 25% - The worksheet recalculates to show the new selling prices
Lesson 3-17: Understand calculation options (manual and automatic): (file-Classic Watches-1):*Demonstrate automatic calculation mode.*
1 Change the calculation mode to manual. *Click: Formulas->Calculation->Calculation Options->Manual.* 2 Change the gross profit to 30%. - This time nothing happens because the worksheet will only recalculate when you explicitly request a recalculation.
Lesson 3-17: Understand calculation options (manual and automatic): (file-Classic Watches-1):*Demonstrate manual calculation mode.*
It would be cleaner to round all of the prices up (or down) to the nearest five dollars. This can easily be achieved using the *MROUND (multiple round) function.* 1. Change the formula in cell D8 from: =C8/(1-$D$5) TO *=MROUND(C8/(1-$D$5),5)* 2. AutoFill the formula to the end of the range
Lesson 3-18: Concatenate strings using the concatenation operator (&) (uses file Classic Watches-2): Round the values in cells D8:D17 to the nearest five dollars
1. Type Classified Ad into cell F7 2. To apply the Heading 3 style, select cell F7 and then click: *Home->Styles->Style Gallery->Titles and Headings->Heading 3* - Cell styles were covered in depth in Session 4 of the Essential Skills Classic Wa book in this series.
Lesson 3-18: Concatenate strings using the concatenation operator (&) (uses the file Classic Watches-2): *Create new heading in cell F7 with the title Classified Ad, apply the Heading 3 Style*.
Cells *F8:F17* will contain text to be included in a classified ad listing in the local newspaper. You want to show the *description of the watch, the year of manufacture and the selling price.* . In Cell F8, type the following *=A8 & "("& B8 & ") - $" & D8* - The only thing that isn't correct is the comma in the cash price of the watch ($16430 should be $16,430) see Lesson 3-20 to solve this problem. 2. AutoFill the formula to the end of the range.
Lesson 3-18: Concatenate strings using the concatenation operator (&) (uses the file Classic Watches-2): *Use the concatenation operator to place the classified ad into cell F8 in the format: Breitling Duograph 18K (1948) -$16,430*
characters
Lesson 3-18: Concatenate strings using the concatenation operator (&): *In the world of computers, letters, numbers, spaces, punctuation marks and other symbols are referred to as ...*
*joins two strings together. For example:* 4 & 2 = 42 Concatenation is rarely used with numbers. It is more likely that you may wish to concatenate Title, First Name and Last Name cells to produce a full name. For example: *= "Mr" & "John" & "Smith" = "MrJohnSmith".*
Lesson 3-18: Concatenate strings using the concatenation operator (&): *What function does the concatenation operator serve?*
*strings* Abc 123 John Smith Strings may be of any length, from a single character to thousands of words
Lesson 3-18: Concatenate strings using the concatenation operator (&): *When several characters are grouped together (perhaps to spell out words), they are referred to as a ...*
To do this, you would concatenate a string containing only a space between each word. Here's how it's done: = "Mr" & " " & "John" & " " & "Smith" = "Mr John Smith"
Lesson 3-18: Concatenate strings using the concatenation operator (&): How do you create spaces between words in concatenated string>
Positive format; Negative format; Zero format; Text format.
Lesson 3-19: Use the TEXT function to format numerical values as strings: *Create custom number formats: Name the 4 possible parts to a number format:*
1. Delete the contents of cells F8:F17 2. Click in cell F8. 3. Click: *Formulas->Function Library->Text->CONCAT.* 4. Populate the dialog as follows: TEXT1: A8 TEXT2:" (" TEXT3: B8 TEXT4: "( " TEXT5: TEXT(D8, "$#,#0") - Note that each string is enclosed in *double quotation* marks to denote *text* - This format string (in TEXT5) means "show a leading dollar sign, show a comma after thousands and show only whole numbers (no decimal places)". 5. Click the OK button 6. Autofill to the end of the range.
Lesson 3-19: Use the TEXT function to format numerical values as strings (uses the file Classic Watches-3): *Use the CONCAT function to place the classified ad into cell F8 in the format: Breitling Duograph 18K (1948) - $16,430* (note the comma separator)
Zeroes mean display significant zeroes. For example, 0.00 means display at least one leading zero and two decimal places
Lesson 3-19: Use the TEXT function to format numerical values as strings: *Describe the Zero format:*
12,341,234.50 - adds comma seperator for thousands and millions - adds two trailing zeroes
Lesson 3-19: Use the TEXT function to format numerical values as strings: *How would the custum string: #,#0.00 format be applied to the number 12341234.5*
The hash symbol (#) is mainly used to add comma separators to thousands and millions. For example, #, # would create a comma for thousands and millions and zero decimal places. Therefore #,# -> 1234.56 ->1,236
Lesson 3-19: Use the TEXT function to format numerical values as strings:: *What is the hashtag symbol (#) used for:*
1. Click on cell B17. - observe the simple formula: =B15xB4 2. Click: *Formulas->Formula Auditing->Evaluate Formula* - The Evaluate Formula dialog appears, B15 is the first step. 3. Click the *Step In* button. This will show the formula behind cell B15. 4. Click the *Step Out* button. The value in cell B15 now replaces the cell reference: 46 5. Click the *Evaluate* button. The value in cell B4 now replaces the cell reference: 6. Click the *Evaluate* button again. You can now see the result of the evaluation: 7. Click the *Close* button
Lesson 3-1: Understand precedence rules and use the Evaluate feature: *Evaluate the formula in cell B17*
Observe the formula in cell B23: *=B15xB4-B15xB4xB5-B4xB15xB6-B4xB15xB7* - It could also have been written with parentheses like this: *=(B15xB4)-(B15xB4)x(B5-B4)x(B15xB6)-(B4xB15xB7)* -Tip: Use parentheses to make formulas more readable
Lesson 3-1: Understand precedence rules and use the Evaluate feature: *Evaluate the formula in cell B23.*
1. Parenthesis (brackets) 2. Exponent 3. Multiply and Divide 4. Add and Subtract - P.E.M.A
Lesson 3-1: Understand precedence rules and use the Evaluate feature: *List Excel's precedence rules order*:
- The leftmost three digits represent the EAN. - The *LEFT* function extracts a given number of characters from the *left part of a string* 1. Click in cell F4. 2. Click: Formulas->Function Library->Text->LEFT. 3. Complete the dialog as follows: Text: E4 Num_Chars: 3 4. Click the Ok button
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions (uses file Best Selling Books-1): *Use a LEFT function to extract the EAN from the ISBN code.*
1. Click in cell G4. 2. Click: Formulas->Function Library->Text->MID. 3. Complete the dialog as follows: Text: E4 Start_num: 5 Num_chars: 1 4. Click the Ok button
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions (uses file Best Selling Books-1): *Use a MID function to extract the Group from the ISBN code.*
The rightmost single digit represents the Check Digit. The *RIGHT* function extracts a given number of digits from the *right part of a string*. 1. Click in cell I4. 2. Click: Formulas->Function Library->Text->RIGHT. 3. Complete the dialog as follows: Text: E4 Num_Chars: 1 4. Click the Ok button
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions (uses file Best Selling Books-1): *Use a RIGHT function to extract the Check Digit from the ISBN code*
1. Text to Columns 2. Flash-Fill Both are covered in Session 2 of the Essential Skills Series
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions: *This lesson covers the use of the LEFT, RIGHT and MID functions to extract fixed width data. What are the other two built-in features that Excel has to complete this task?*
1. Click in cell 4. 2. Click: Formulas->Function Library->Text->MID. 3. Complete the dialog as follows: Text: E4 Start_num: 6 Num_chars: 8 4. Click the Ok button
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions (uses file Best Selling Books-1): *Use a MID function to extract the Publisher and Title code from the ISBN code*
1. Click in cell C4. 2. Click Formulas->Text->FIND 3. Complete the dialog as follows: Find_text: ( Within_text: B4 4. Click the OK button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a FIND function into cell C4 to find the first occurrence of an opening bracket within the telephone number.*
1. Click in cell D4. 2. Click Formulas->Text->FIND 3. Complete the dialog as follows: Find_text: ) Within_text: B4 4. Click the OK button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a FIND function into cell D4 to find the first occurrence of a closing bracket within the telephone number.*
1. Click in cell E4. 2. Click Formulas->Text->FIND 3. Complete the dialog as follows: Find_text: "-" Within_text: B4 4. Click the OK button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a FIND function into cell E4 to find the first occurrence Phone Book of a hyphen within the telephone number.*
1. Click in cell G4. 2. Click: Formulas->Text->G4. 3. The correct arguments are Text: B4 Num_Chars: C4-1 - Note that C4-1 is used for the number of characters to avoid returning the opening bracket. This could result in a trailing space (see sidebar for a way to remove trailing spaces). 4. Click the Ok button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a LEFT function into cell G4 to extract the country code*
1. Click in cell F4. 2. Click: Formulas->Text->LEN. 3. Click cell B4 for the single argument for this function. 4. Click the OK button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a LEN function into cell F4 to find the total number of characters in the telephone number*
1. Click in cell H4. 2. Click: Formulas->Function Library->Text->MID. 3. Complete the dialog as follows: Text: B4 Start_num: D4+1 Num_chars: E4+D4+1 4. Click the Ok button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a MID function into cell H4 to extract the area code*
1. Click in cell I4. 2. Click: Formulas->Function Library->Text->MID. 3. Complete the dialog as follows: Text: B4 Start_num: D4+1 Num_chars: F4-E4 4. Click the Ok button
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions (uses file Phone Book-1): *Insert a MID function into cell I4 to extract the phone number.*
1. Click in cell B6 on the Invoice worksheet. 2. Click: Formulas->Function Library->Lookup & Reference->VLOOKUP. - The VLOOKUP Function Arguments dialog appears. It can be seen that the VLOOKUP function has three required arguments (shown in bold face) and one optional argument:
Lesson 3-22: Use a VLOOKUP function for an exact lookup (file-Invoice-1): Insert a VLOOKUP function into cell B6 on the Invoice worksheet to find the description that matches the Code in cell A6 on the Stock worksheet.*Part 1: Bring up The VLOOKUP function dialog*
3. Set the *Lookup_value* argument to: *A6* - This is the cell on the Invoice worksheet that provides the value to be searched for in column A of the Stock worksheet 4. Set the *Col_index_num* argument to: 2 - Counting from *left to right*, the Col_index_num argument is the *column that contains the value you want to return*. In this case, it is the Description column, so you want to return column 2. 5. Set the *Table_array* argument to: Stock. - VLOOKUP always searches the *left-most column* of the range, table or name. 6. Set the *Range_lookup* argument to: *FALSE* - Beginners often overlook this *vital argument* because it is optional. If it is left blank, VLOOKUP will return an *inexact match.* Later, in Lesson 3-25: you'll find why that might be useful. 7. Click OK
Lesson 3-22: Use a VLOOKUP function for an exact lookup (file-Invoice-1): Insert a VLOOKUP function into cell B6 on the Invoice worksheet to find the description that matches the Code in cell A6 on the Stock worksheet.*Part 2: Fill out the arguments VLOOKUP function dialog*
1. Click on the stock worksheet. 2. Click inside the range A5:G15 3. *Insert->Tables->Table* 4. Click *OK*, to create the table 5. Click: *Table Tools->Design->Properties* 6. In the *Table Name* box type: *Stock* 7. Hit *Enter* to save the table name - The table name used in VLOOKUP must match otherwise it will not work.
Lesson 3-22: Use a VLOOKUP function for an exact lookup (file-Invoice-1):*Convert the range A5:G15 on the Stock worksheet into a table named: Stock*
When provided with a stock code, the VLOOKUP function can *scan all of the codes in given column* (typically A) until a *match) is found and then *return a value* from the same row for any of the other columns
Lesson 3-22: Use a VLOOKUP function for an exact lookup (file-Invoice-1):*How does the VLOOKUP function operate*
The maximum wind speed for each hurricane category is shown at the bottom of the list: A28:B32 - By using a SWITCH function (instead of the VLOOKUP), there will be no danger of a user editing the contents of cells A28:B32 causing the worksheet to become inaccurate
Lesson 3-23: Use the SWITCH function (file-Atlantic Hurricanes-1): *Observing the file given, why might we use the SWITCH function here?*
1. Click in cell E4. 2. Click: *Formulas->Function Library->Logical->SWITCH.* - The Function Arguments dialog appears. 3. Click in the Expression box. 4. Click once on cell D4 5. In the *Value1* box enter the value: *1* 6. In the *Result1* box enter the value *95* 7. *Continue* to define pairs of values for category 2 to 5 hurricanes. - The SWITCH function allows you to add up to 127 Value/Result pairs. 8. In the *final* Default_or_value6 box, type: *"Error"* This text will be displayed in the event that a category cell is out of *range or empty.*
Lesson 3-23: Use the SWITCH function (file-Atlantic Hurricanes-1): *Use the SWITCH function to calculate the maximum wind speed for each hurricane. *
When you need to lookup values from a *dynamically changing* table (e.g inventory values are updated frequently) *VLOOKUP* is always the best approach. The SWICTH function (new for Excel 2016) may be a better solution when you have to lookup data that is *not expected to change* (or at least rarely changes). This type of data is often called *static data* by IT professionals.
Lesson 3-23: Use the SWITCH function: *What is the SWITCH function best used for? When is it better to use VLOOKUP, instead?*
1. Click in cell B6. Look at the formula in the formula bar: =VLOOKUP(A6,Stock,5,FALSE) 2. *Click* just to the *right* of the equals sign in the formula bar. 3. Type *IFERROR(* - Notice the tip that has appeared. The entire VLOOKUP function is now being used as the value argument for the IFERROR function. 4. Click to the *extreme right* of the formula in the formula bar and *add a comma:* - You are now ready to define what will be displayed in place of an error message 5. Type "". This is an *empty string* and tells Excel to keep the cell *blank* when an error is returned 6. *<Enter>* 7. AutoFill the formulas in cells B6:C6 to cells B7:C8
Lesson 3-24: Use an IFERROR function to suppress error messages(file-Invoice-2): Wrap each VLOOKUP function with an IFERROR function to return a blank space when an error is encountered
Returns the value of your choice whenever a formula returns an error.
Lesson 3-24: Use an IFERROR function to suppress error messages: What is the IFERROR function used for?
7. Click in cell C4 8. Click: *Formulas->Function Library->Lookup & Reference->VLOOKUP*. 9. Complete the arguments as follows: *Lookup_Value: B4* *Table_array: Grade* *Col_index_num: 2* 10. Autofill cell C4 to cells C5:C17
Lesson 3-25: Use a VLOOKUP function for an inexact lookup (file-Grades-1): *Add an inexact VLOOKUP to cell C4 to return the grade that corresponds to the percentage mark in cell B4.*
1. Select the cells E3:F8 2. *Insert->Tables->Table 3. Click OK on the dialog 4. Click: *Table Tools->Design->Properties* 5. Under *Table Name* type: *Grades* 6. Hit *<Enter>* to save the table name
Lesson 3-25: Use a VLOOKUP function for an inexact lookup (file-Grades-1): *Convert cells E3:F8 into a table named: Grade*
When you are interested in the *nearest match*. (e.g. assigning a letter grade from a percentage grade) - If you ask VLOOKUP to perform an inexact lookup, it will return an exact match if one is found. - If an exact match is not found, it will return the *largest value* that is *less than* the *lookup value*
Lesson 3-25: Use a VLOOKUP function for an inexact lookup: *When would you employ an inexact lookup?*
1. Select rows 53 to 60 2. Right click on one of the selected column headers. 3. Click: Unhide from the shortcut menu. - Several new rows are now revealed. These are often called *helper cells*. They will make the formulas *more readable*. - Later, when the worksheet is complete, you will hide these cells again.
Lesson 3-26: Use a MATCH function for an exact lookup: *Unhide all rows between row 53 and 60*
1. Select cell B53 2. Navigate: *Data->Data Tools->Data Validation* - The Data Validation dialog is generated 3. Allow: List 4. Source: Select Cells *A6:A35* 5. Click Ok 6. Repeat same process for cell B54 7. Select Atlanta as the From City and Chicago as the To city
Lesson 3-26: Use a MATCH function for an exact lookup: *1.Add a list validation to cells B53 and B54 to show a dropdown list of all cities. 2. Select Atlanta as the From City and Chicago as the To city.*
1. Click in cell *B56*. 2. Click: *Formulas->Function Library->Lookup & Reference->MATCH* - The *Function Arguments* dialog for the MATCH function appears. 3. Click in the *Lookup_value* box. 4. Click on cell *B54*. 5. Click in the *Lookup_array* box 6. Select the *table cells in row 5* in the distance table. - Note that this results in the *structured reference*: *Distance[#Headers]*. 7. Click in the *Match_type* box and type *0* - A *value of 7* is returned to cell *B56*. Notice that Chicago is the *7th header* in the list.
Lesson 3-26: Use a MATCH function for an exact lookup: *Insert a MATCH function into cell B56 to define the column in the Distance table that contains the header text: Chicago*
1. Click in cell *B55*. 2. Click: *Formulas->Function Library->Lookup & Reference->MATCH* - The *Function Arguments* dialog for the MATCH function appears. 3. Click in the *Lookup_value* box. 4. Click on cell *B53*. 5. Click in the *Lookup_array* box 6. Select the range *A6:A35* in the distance table. Note that this results in the *structured reference*: *Distance[City]*. 7. Click in the *Match_type* box and type *0* - Match type is an optional input, very similar to exact and inexact match in VLOOKUP - A *value of 2* is returned to cell *B55*. Notice that Atlanta is the *second city* listed in the Distance table
Lesson 3-26: Use a MATCH function for an exact lookup: *Use a MATCH function to find which row in the City column of the Distance table contains the text: Atlanta.*
11. Click in cell *B59* 12. Click: *Formulas->Function Library->Lookup & Reference->VLOOKUP*. 13. Complete the arguments as follows: *Lookup_Value: B58* *Table_array: Fare* *Col_index_num: 2* Range_Lookup: True (or blank)
Lesson 3-27: Use the INDEX function :Place an INDEX function in cell B57 that will return the value in the Distance table that is identified by the row and column references in cells B55 and B56.: *Add a VLOOKUP formula to cell B59 to retrieve the Fare per mile from the Fare table*
- This is simply the distance multiplied by the fare per mile: 14. Click in Cell B60 15. Type the simple formula: =B57*B59
Lesson 3-27: Use the INDEX function :Place an INDEX function in cell B57 that will return the value in the Distance table that is identified by the row and column references in cells B55 and B56.: *Add a function to cell B60 to calculate the fare for this journey*
It is always a good idea to hide helper cells when a worksheet is completed. This keeps the worksheet simple and clear. 1. Select rows 55, 56 and 59. 2. Right click on one of the selected row headers and click Hide from the shortcut menu.
Lesson 3-27: Use the INDEX function :Place an INDEX function in cell B57 that will return the value in the Distance table that is identified by the row and column references in cells B55 and B56.: *Hide rows 55, 56 and 59*
1. Click in cell B57. 2. Click: *Formulas->Function Library->Lookup & Reference->INDEX* - A *Select Arguments* dialog appears The INDEX function is quite unusual because it is said to be *overloaded*. An overloaded function *can accept different sets of arguments.* - The function *decides the form it will take* by examining the arguments provided. See sidebar (facing page) for examples of the different sets of arguments that can be used.
Lesson 3-27: Use the INDEX function :Place an INDEX function in cell B57 that will return the value in the Distance table that is identified by the row and column references in cells B55 and B56.: *Part 1 populate dialog*
3. Click the *first set of arguments* (the set beginning with array and click the OK button. This is the more commonly used form of the INDEX function. 4. Click in the *Array box*. 5. Type: *Distance* to reference the *Distance table*. 6. Click in the *Row_num box*. 7. Click on *cell B55* (this identifies the row in the table that has Atlanta in the City column). 8. Click in the *Column_num* box. 9. Click on *cell B56* (this identifies the column in the table that has Chicago in the header row). 10. Click the *OK button*
Lesson 3-27: Use the INDEX function :Place an INDEX function in cell B57 that will return the value in the Distance table that is identified by the row and column references in cells B55 and B56.: *Part 2 complete the Index Function arguments*
The difference from the first approach is that the grades will change if the Percentage/Grade thresholds shown in cells E3:F8 change in the future 1. Click in cell C4. 2. Click: *Formulas->Function Library->Logical->IFS* The arguments entered into the dialogue should result in the formula entered as follows: =IFS(B4<$E$5,"Fail",B4<$E$6,"C",B4<$E$7,"B",B4<$E$8,"A",B4>=$E$8,"A*") 3. Autofill the remaining cells
Lesson 3-28: Use the IFS function (file: IFS Grades-1): *USE the IFS function to calculate the grade for each student using the grade data defined in cells E3:F8.*
1. Click in cell C4. 2. Click: *Formulas->Function Library->Logical->IFS* The arguments entered into the dialogue should result in the formula entered as follows: =IFS(B4<60%,"Fail",B4<70%,"C",B4<80%,"B",B4<90%,"A",B4>=90%,"A*") 3. Autofill the remaining cells
Lesson 3-28: Use the IFS function (file: IFS Grades-1): *Use the IFS function to calculate the grade for each student by defining grade data within the function.*
Sometimes it may be better to "hard code" data (such as the percentage grade thresholds) within the function itself. This prevents users from accidentally deleting or changing the grade percentage thresholds within the worksheet.
Lesson 3-28: Use the IFS function: *What is the purpose of the IFS function*
*Required*
Lesson 3-2: Use common functions with *Formula Autocomplete: When observing the parameters of a particular function *arguments in parantheses are*:
*Optional*
Lesson 3-2: Use common functions with *Formula Autocomplete: When observing the parameters of a particular function *arguments in square brackets are*:
1. Click in cell B25. 2. Type =SU - Excel auto-populates possible selections. 3. Arrow down to SUM - Observe tip that pops up to the right, selecting s the <F1> key will generate the help article for the function. 4. Hit the <Tab> key to select the SUM function. 5. select cells F4:F23 (using the keyboard or mouse) 6. Press the <Enter> key to finish the formula. 7. Use the same technique to add MAX, MIN, AVERAGE and COUNT functions to cells B26:B29
Lesson 3-2: Use common functions with Formula AutoComplete: Add a SUM function to cell B25.
SUM, MAX, MIN, AVERAGE and COUNT - In this lesson, you'll use Excel's Formula AutoComplete feature to add these formulas to a workbook
Lesson 3-2: Use common functions with Formula AutoComplete: What are the five most often seen in workbooks are
1. Click in cell D4. 2. Click the Insert Function button at the left of the formula bar ( The "fx" symbol): - The Insert Function dialog appears: 3. Type Loan into the Search for a function text box and then click the Go button: - A list of functions is shown in the Select a function list. Excel's best guess is the PMT function. 4. Click the OK button. (Continued on next card.)
Lesson 3-3: Use the Insert Function dialog and the PMT function (file: Mortgage Repayments-1): Calculate the monthly repayments using the PMT function and the Insert Function dialog: *First 4 steps completing Insert Function dialog*
After selecting the PMT function from the insert function dialog (see previous card) the *Function Arguments* dialog is displayed 1. The arguments shown in bold face are required, enter as follows: Rate: C4/12 Nper:B4x12 Pv:A4 2. The value is displayed in red, enter a minus sign in front of the formula to make it positive, if desired, and hit enter 3. AutoFill the formula to the end of the list to show monthly repayments for all six loan amounts.
Lesson 3-3: Use the Insert Function dialog and the PMT function (file: Mortgage Repayments-1): Calculate the monthly repayments using the PMT function and the Insert Function dialog: *Complete the PMT funtion*
1. Click in cell B7. 2. Click: Formulas->Function Library->Financial->FV (can also use function insert) 3. Add the correct values to the Function Arguments dialog: Rate: B5/12 Nper:B4x12 Pmt: B3 4. Click the OK button. 5. Enter a minus sign in front of the formula to make the value positive, if desired, and hit enter. - This is shown as negative because it represents money leaving your account
Lesson 3-4: Use the PV and FV: *Complete the following (using file Investments-1)* I save $100 each month towards my retirement fund. If I save for 25 years and the interest rate during this time will be 4%, how much money will I have in my fund upon retirement? *Use the FV function to calculate the retirement fund value.*
Future value is used to work out how much capital will accumulate if a fixed amount is saved each month at a specified compound interest rate.
Lesson 3-4: Use the PV and FV: *What is future value and what is it used for?*
Present Value is the total amount that a series of future payments is worth now. Present Value can be used to value an existing loan. This would be useful if you wanted to sell the loan to another party
Lesson 3-4: Use the PV and FV: *What is present value and what is it used for?*
1. Click in cell B15. 2. Click: Formulas->Function Library->Financial->PV. (can also use function insert) 3. Add the correct values to the Function Arguments dialog: Rate: B13/12 Nper:B2x12 Pmt: B11 4. Add a minus sign in front of PV in the formula bar to convert it to a positive value: 5. Click OK. The result is 21,243.39 so you turned a profit on the deal!
Lesson 3-4: Use the PV and FV: Complete the following (using file Investments-1) I have loaned my friend John $20,000 to buy a car. John has agreed to repay $1,000 per month for two years (making me $4,000 in interest on the deal). I want to sell the loan on to my other friend Bill. Bill says he is happy to buy it from me but he needs a return of 12% on his investment. I can use the PV function to work out what the loan is worth today based upon Bill's requirement for a 12% return.
9. The correct formula could be either of the following: =F9+G9+H9 =SUM(F9:H9) 10. AutoFill the formulas in cells F9:I9 to cells F10:I17. The payroll worksheet is now complete.
Lesson 3-5: Use the IF logic function (Uses file: Earnings Summary-1): *Add a formula to cell I9 to calculate total pay.*
4. Click in cell G9. 5. Click: *Formulas->Logical->IF*. The Function Arguments dialog appears. 6. Complete the dialog as follows: Logical Test: E9<=35 Value_if_true: 0 Value if false (E9-35)*D9*1.5
Lesson 3-5: Use the IF logic function (Uses file: Earnings Summary-1): *Use an IF function to calculate overtime pay*
The IF function relies on a logical test and then performs one action if the test returns TRUE and a different action if the test returns FALSE Examples: 6=2 is: False why: Because six does not equal two. 100<90 *is: False* why: Because 100 is not less than 90. 6+2 = 4+4 *is: True* why: Because eight does equal eight
Lesson 3-5: Use the IF logic function: Describe The IF function
1. Click in cell F9. 2. Click: *Formulas->Logical->IF*. The Function Arguments dialog appears. 3. Complete the dialog as follows: Logical test: E9<=35 Value_if_true: E9*D9 Value if false: 35*D9
Lesson 3-5: Use the IF logic function:(Uses file: Earnings Summary-1): *Use an IF function to calculate standard pay*.
7. Click in cell H9. 8. Click: *Formulas->Logical->IF*. The Function Arguments dialog appears.B9>C9 Value_if_true: (B9-C9)*5% Value if false
Lesson 3-5: Use the IF logic unction (Uses file: Earnings Summary-1): *Use the IF function to calculate bonus*
1. Click in cell C27. 2. Click: Formulas->More Functions->Statistical->COUNTIF. 3. Complete the dialog with the following arguments: Range: $B$4:$B$17 Criteria: A27 4. Click OK 5. Autofill cell C28
Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals (file: Headcount & Salaries-1): *Use a COUNTIF to calculate headcount for male and female employees.
1. Click in cell C21. 2. Click: Formulas->More Functions->Statistical->COUNTIF. 3. Complete the dialog with the following arguments: Range: $D$4:$D$17 Criteria: A21 4. Click the OK button. 5. AutoFill cell C21 to cells C22:C23 to display the headcount for the Purchasing and Logistics departments.
Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals (file: Headcount & Salaries-1): *Use the COUNTIF function to calculate the headcount for each department.*
1. Click in cell B27. 2. Click: Formulas->Math & Trig->SUMIF. The Function Arguments dialog appears. 3. There are three arguments for the SUMIF function complete with the following arguments: Range: $B$4:$B$17 Criteria: A27 Sum_range: $C$4:$C$17 4. Click OK 5. Autofill cell B28
Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals(file: Headcount & Salaries-1) : *Use a SUMIF to calculate the salaary for male and female employees.
1. Click in cell B21. 2. Click: Formulas->Math & Trig->SUMIF. The Function Arguments dialog appears. 3. There are three arguments for the SUMIF function complete with the following arguments: Range: $D$4:$D$17 Criteria: A21 Sum_range: $C$4:$C$17 4. Click OK 5. Autofill to cell B23
Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals: *Use the SUMIF function to calculate the total salary for each department.*
The number of days that have elapsed since 1st of January 1900
Lesson 3-7 : Understand date serial numbers: Every number (for this purpose) has a corresponding *date serial number* that denotes....
Date: *5*-Jan-1900 Time: *12:00*
Lesson 3-7: Understand date serial numbers: *Every time is a date and every date is a time:* For example the number *5.5* represents...
Dates from *January 1st 1900 to January 5th 1900*
Lesson 3-7: Understand date serial numbers: 2: For example, the numbers *1 through 5*, represent....
1. Create a blank workbook 2. In Cell *A1 type the number 1* 3. Cell *B1: =A1 Enter* 4. Select B1, *Right-Click -> Format Cells ->Custom* 5. Under *Type: dd-mmm-yyyy hh:mm* (Make sure United States is the Location) 6. Change the *number in A1* and the *corresponding date and time* will be displayed in Cell B1.
Lesson 3-7: Understand date serial numbers: A simple way to demonstrate visually the corresponding date and time for any number is to:
1. Cell *A1 type the date 01/1/1900* 2. Cell *A2 type the date 1/1/2000* 3. Cell *A3 type =A2-A1, Enter*. The number of *days elapsed between the two dates* are displayed.
Lesson 3-7: Understand date serial numbers: Demonstrate date arithmetic: *Compute the number of days that occurred between 01 Jan 1900 and 01 Jan 2000*.
*Date: 1-Jan-1900 Time: 00:00*
Lesson 3-7: Understand date serial numbers: For example, the number *1* represents...
*a number less than one, , *Date: 0-Jan-1900 Time: 12:00*
Lesson 3-7: Understand date serial numbers: When you enter a time into a cell without the date, the time is stored as ____________, for example, the number *0.5* is store as....
1. Use the YEAR function to calculate the year in which each employee was born in cell D4. -*Click: Formulas->Date & Time->Year* 2. Use the DAY function to place the day when Brad Cruise was born into cell E4. 3. Use the MONTH function to calculate the month when Brad Cruise was born into cell F4. 4. Use the TODAY function to place a volatile current date into cell G4. 5. Use the YEAR function to place the current year into cell H4. 6. Add a formula to cell I4 to calculate the employee's maximum age this year - a simple subtraction of the year born from the current year
Lesson 3-8: Understand common date functions ( Resources-1): Use the necessary date functions to complete the worksheet.
TODAY, DAY, MONTH and YEAR
Lesson 3-8: Understand common date functions: Excel's primary date functions are...
5 Change the cell headers in J3 an K3 to Age (Months) and Age (Days) respectively 6 Add the following formula to cell J4: *=DATEDIF(C4,TODAY(),"ym")* 7 Add the following formula to cell K4: *=DATEDIF(C4,TODAY(),"md")* 8 Autofill cells I4:K4 to the end of the range.
Lesson 3-9: Use the DATEDIF function: *Calculate each resource's precise age in years, months and days.*
"m" Months between two dates. "d" Days between two dates. "y" Years between two dates. "ym" Months between two dates, ignoring the year (ie as if both dates were the same year). "yd" Days between two dates, ignoring the year. "md" Days between two dates, ignoring the months and years.
Lesson 3-9: Use the DATEDIF function: The interval arguments are:
*=DATEDIF(StartDate, EndDate, Interval)* *StartDate:* The first date. *EndDate:* The second date. *nterval:* The interval to return, such as the number of months or years between the two dates.
Lesson 3-9: Use the DATEDIF function: The syntax for the function is...
1 Change the text in cell I3 to: Age (Years) 2 Delete cells I4:I12. 3 Add the following formula to cell I4: *=DATEDIF(C4,TODAY(),"y")* - The function works by comparing the date of birth (in cell C4) with today's date (returned by the TODAY function) and returns the interval between the date of birth, and today's date, in years.
Lesson 3-9: Use the DATEDIF function: Use the DATEDIF function to calculate the age in years as of today.
DATEDIF is able to calculate the difference between two dates for several intervals. In this lesson, you'll use it to calculate an age from a date of birth.
Lesson 3-9: Use the DATEDIF function: What is the DATEDIF used for?
