Chapter 3 Excel Project
In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14)
!. Click on Formula tab 2. Click More Functions button 3. Hover over statistical and select MAX 4. Type E2:E14 in the Number1 input 5. Click OK OR 1. Type =MAX(E2:E14) in cell E15 2. Press Enter
Three Ways to Enter Functions
1) Typing directly in the cell or the formula bar 2) Using AutoSum button (SUM, AVG, COUNT, MIN, MAX) 3) Using Quick Analysis tool (SUM, AVG, COUNT)
Preview how the worksheet will look when printed and then Hide the formulas and splat the values instead
1. Click File 2. Click Print 3. Click the back arrow to exit backstage view 4. Click Show Formulas
On the SaleInventory worksheet, cells A4:E20 are selected. Use the Create from Selection command to create named ranges for the data table A5:E20 using the labels in row 4 as the basis for the names.
1. Click Formulas tab 2. Click Create from Slection 3. Click OK
Edit the Inventory name so it refers to cells A5:F20 on the SalonInventory worksheet. Close the Name Manager when you're finished.
1. Click Formulas tab 2. Click Name Manager button 3. Edit the cell range reference to ='Salon Inventory'!$A$5:$F$20 in the Refers to box 4. Click the Checkmark next to the Refers to box 5. Click Close
Enter a formula in cell B3 to combine the text from cells B2 and C2 to display the customer name in the format Maxwell Arch. Do NOT forget the argument for the space.
1. Click Formulas tab 2. Click Text button 3. Click CONCAT 4. Type B2 in Text 1 box 5. Type " " in Text 2 box 6. Type C2 in Text 3 box 7. Click OK
On the Loan2 worksheet, show the dependent arrow for cell B6
1. Click Formulas tab 2. Click Trace Dependents button
Enter a formula in cell B2 to display the current date and time
1. Click Formulas tab 2. Click Date & Time button 3. Click Now 4. Click OK
Enter a formula in cell B1 to display only the current date
1. Click Formulas tab 2. Click Date & Time button 3. Click Today 4. Click OK
Enter a formula in cell B7 using the PMT function to calculate the monthly loan payment. Use cell references as the functions argument. Remember, payments will be monthly, so divide the annual interest rate by 12. USe a negative number for the Pv argument. Do NOT include the optional arguments
1. Click Formulas tab 2. Click Financials button 3. Select PMT 4. Type B4/12 in the Rate argument box 5. Type B5 in the Nper argument box 6. Type -B3 in the Pv argument box 7. Click OK
Using the Insert Function dialog, enter a SUM function in cell E7 to calculate the totals of cells B7:D7
1. Click Formulas tab 2. Click Insert Function button 3. Expand the Or select a category: list 4. Select Most Recently Used 5. Click OK 6. Click OK
Enter a formula in cell F5 using the logical function IF to display do not order if the quantity in stock (cell D5) is greater than the reorder level (cell E5) and order if it is not.
1. Click Formulas tab 2. Click Logical button 3. Click IF 4. Type D5>E5 in the Logical_test box 5. Type do not order in the Value_if_true box 6. Type order in Value_if_false box 7. Click OK
Enter a formula in cell B2 using the VLOOKUP function to find the item name for the item number listed in cell B1. Use the name Inventory for the lookup table. The item names are located in column 2 of the lookup table. Require an exact match.
1. Click Formulas tab 2. Click Lookup & Reference button 3. Click VLOOKUP 4. Type B1 in the Lookup_value argument box 5. Type Inventory in the Table_array argument box 6. Type 2 in the Col_num argument box 7. Type False in the Range_lookup box (find an exact match) (TRUE- find the closest match in the first column) 8. Click OK
Use the command on the Ribbon to open to Function Arguments dialog to enter a SUM function in a cell B1 to calculate the total of cells B12:D12. Do not open the Insert Function dialog first. Be sure to use a cell range and not individual cell references.
1. Click Formulas tab 2. Click Math & Trig button 3. Click SUM 4. Type B12:D12 in the Number1 box 5. Click OK
Enter a formula in cell B2 to calculate the average value of cells B12:D12. Be sure to use a cell range and not individual cell references
1. Click Formulas tab 2. Click More Functions button 3. Hover mouse over Statistical and click Average 4. Type B12:D12 in the Number1 box 5. Click OK
In cell E1, enter a formula using a counting function to count the number of items on sale. Count the numerical values in the ItemID column (cells A7:A20)
1. Click Formulas tab 2. Click More Functions button 3. Hover mouse over Statistical and click COUNT 4. Type A7:A20 in the Value1 box 5. Click OK
In cell E2, enter a formula using a counting function to count the number of items on sale. Count the values in the Item column (cells B7:B20)
1. Click Formulas tab 2. Click More Functions button 3. Hover mouse over Statistical and click COUNTA 4. Type B7:B20 in the Value1 box 5. Click OK
In cell E3, enter a formula using a counting function to count the number of blank cells in the QuantityInStock column (cells E7:E20).
1. Click Formulas tab 2. Click More Functions button 3. Hover mouse over Statistical and click COUNTBLANK 4. Type E7:E20 in the Value1 box 5. Click OK
There are two inventory names. Rename the one that limited in scope to the Sale Inventory worksheet to: InventoryOnSale
1. Click Formulas tab 2. Click Name Manager button 3. Click Edit..., the fist Inventory name is selected by default 4. Type InventoryOnSale in the Namebox 5. Click OK
In cell B4, enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name in the format Mr. Maxwell Arch. Use a space as the delimiter. Use a cell range as the Text1 argument.
1. Click Formulas tab 2. Click Text button 3. Click TEXTJOIN 4. Type " " in the Deliminator box 5. Type TRUE in the Ignore_empty argument box 6. Type A2:C2 in Text1 box 7. Click OK
Enter a formula in cell H3 to display text from cell H2 so all letters displat in lowercase
1. Click Formulas tab 2. Click Text button 3. Select LOWER 4. Type H2 in the Text box 5. Click OK
Enter a formula in cell D3 to display the text from cell D2 so the first letter in each word is capitalized
1. Click Formulas tab 2. Click Text button 3. Select PROPER 4. Type D2 in the Text box 5. Click OK
Enter a formula in cell F3 to display the text from cell F2 so all the letters display in uppercase
1. Click Formulas tab 2. Click Text button 3. Select UPPER 4. Type F2 in the Text box 5. Click OK
In the Name Manager, review the information about the ReorderTime name. The name results in a #REF! error. You do not need it in this workbook. Delete the ReorderTime name. Close the Name Manager when you are finished.
1. Click ReorderTime name 2. Click Delete 3. Click OK 4. Click Close
15. Enter a formula to compute the percentage for the first student. Divide the student's total points by the total possible points. You will be copying this formula, so make sure the reference to the total points possible uses an absolute reference.
1. Click cell AD10 and type =AC10/$AC$7
7. Count the number of students to calculate the class size
1. Click cell B10 2. Type =COU 3. Double-click COUNTA 4. Type (A10:A26)
7. Enter a formula to calculate the number of clients served each day.
1. Click cell B16 2. Type =COU 3. Double-click COUNT 4. Click cell B8 and drag to cell B11 5. Press Enter 6. Copy the formula in cell B16 to cells C16:H16 Should be: =COUNT(B8:B11)
Enter a formula in cell B7 to display the text from cell A7 in all lower case letters
1. Click cell B7 2. Type =LOW 3. Double-click LOWER and type (A7) 4. Press Enter
Select cell B8 and show the precedent arrows and hide all the tracer arrows and once
1. Click cell B8 2. Click Trace Precendents 3. Click Remove Arrows button
On the open Expenses worksheet, cell B8 has an error. Display the Smart Tag and accept the Excel's suggestion for fixing the error.
1. Click cell B8 2. Click the smart tag (yellow sign with !) 3. Click Update Formula to Include Cells
On the Year1 sheet, in cell B8, enter a formula to display the value of cell B7 from the Salaries sheet
1. Click cell B8 2. Type = 3. Click Salaries tab 4. Click cell B7 5. Press Enter
Enter a formula in cell B9 to display the text from cell A9 in all upper case letter
1. Click cell B9 2. Type =UPPER(A9) 3. Press Enter
7. Determine whether or not you will need a loan for each potential purchase. In cell C15, enter a formula using an IF function to determine if you need a loan. Your available cash is located on the Data sheet in cell A3. If the price of the car is less than or equal to your available cash, display "no". If the price of the car is more than your available cash, display "yes". Use absolute references where appropriate.
1. Click cell C15 2. Go to Formulas and click Logic 3. Select IF 4. Click in Logical_test and click data sheet and click cell A3, then type >=C7 5. Type no in Value_if_true 6. Type yes in value_if_false
9. Calculate the monthly payment amount for each loan. In cell C20, enter a formula using PMT function to calculate the monthly loan payment for the first vehicle. Hint: Divide the interest rate by 12 in the Rate argument Hint: Multiply the number of years by 12 in the Nper argument Hint: Use a negative value for the loan amount in the Pv argument
1. Click cell C20 2. Go to formula tab and click Financials and select PMT 3. Type C17/12 in the Rate 4. Type C18*12 in the Nper 5. Type -C16 in Pv argument =PMT(C17/12,C18*12,-C16)
10. Compute the monthly cost of gas. In cell C21, enter a formula to calculate the number of miles you expect to drive each month. Divide the value of number of miles (cell A5 from Data sheet) by the average MPG for the vehicle multiplies by the price of a gallon of gas (cell A6 from Data sheet)
1. Click cell C21 2. Type =(Data!A5/C17*Data!A6)
6. Use a formula to look up Marshall's current billable rate. Use the employee number as the lookup value.
1. Click cell C4 2. Go to Formulas tab and click Lookup & Reference 3. Select VLOOKUP 4. Click C3 into Lookup_value argument box 5. Type BillableRate in Table_array argument box 6. Type 4 in Col_index argument box 7. Type false in Range_lookup argument box 8. Click OK Should be: =VLOOKUP(C3,BillableRates,4,False)
8. Use a formula to calculate the average daily billable hours.
1. Click cell H18 2. Type =AVERAGE(B12:H12) 3. Press Enter
10. Find out which students have a grade below C at the cut-off point for dropping the class. Enter an IF function in cell S10 to check if the student's total points divided by the total possible points through the midterm is less than 70%. Use SUM functions within the IF function. Be sure to use the cell range name you just defined for possible points. If the student is below a grade C, display Warning! in the cell; otherwise leave the cell blank.
1. Click cell S10 2. On formula tab, click Logical and select IF 3. type SUM(D10:R10)/SUM(PossiblePysMid)<70% in Logical_test argument box 4. Type Warning! in Value_if_true argument box 5. Type " " in Value_if_false Should be: =IF(SUM(D10:R10)/SUM(PossiblePtsMid)<70%,"Warning!"," ")
In cell D16, enter a formula using a counting function to count the number of blank cells in the Billable? Column (cells D2:D14)
1. Click in cell D16 2. Type =COUNT and double-click COUNTBLANK 3. Then type (D2:D14) and press Enter
9. Use formula to calculate the total billable hours for the week.
1. Click in cell H19 2. Type =SUM(B14:H14) 3. Click Enter
12. Each staff member is required to log a minimum number of billable hours per week. Enter a formula using an IF statement to display "yes" if the total billable hours for the week are greater than or equal to the required hours and "no" if they are nor.
1. Click in cell H3 2. Go to formula tab and click Logical 3. Click IF 4. Type H19>=H2 in Logical_test argument box 5. type yes in Value_if_true argument box 6. Type no in Value_if_false argument box 7. Click OK Should be: =IF(H19>=H2,"yes","no")
6. Lookup the grade point for each letter grade as follows. In cell I14, enter a VLOOKUP function. Use J3:K7 as table array. The grade points are located in column 2. Use absolute references where appropriate.
1. Click in cell I14 2. Go to Lookup & Reference and select VLOOKUP 3. Type H14 (the grade) in the Lookup_value 4. Type J3:K7 in the Table_array box 5. Type 2 in Col_index box 6. Type True in Range_lookup 7. Press Enter =VLOOKUP(H14,J3:K7,2,TRUE)
Use the Create from Selection command to create named ranges for the selected data table in cells B2:E6 using the labels in row 1 as the basis for the names.
1. Click on Formula tab 2. Click Create from Selection 3. Click OK
This workbook has two named ranges with the same name: Salaries_Year1 Rename the one that is limited in scope to the Projections list to: BaseSalaries. Close the Name Manager when finished.
1. Click on Formula tab 2. Click Name Manager 3. Click Cell 2x1 4. Click Edit... 5. Type BaseSalaries in name box 6. Click OK 7. Click Close
On the Formulas tab, in the Formula Auditing group, open the Error Checking dialog and check the rest of the worksheet for errors. Review the information about the next error found and click the button to accept the suggested fix. Continue error checking and accepting fixed until Excel reports that error check is complete
1. Click the Formulas tab 2. Click Error Checking button 3. Click Update Formula to Include Cells button 4. Click Copy Formula from Left button 5. Click OK
Display the formulas in this worksheet.
1. Click the Formulas tab 2. Click Show Formulas
5. Use a formula to display Marshall's full name in the format Bob Smith. Staff names are kept in the Rates Worksheet.
1. Click the Marshall Hours sheet tab 2. Go to Formulas tab and click Text button 3. Select CONCAT 4. Click Rates sheet tab and click cell C4 to enter the cell reference into Text1 argument 5. Type " " in Text2 argument 6. Click in Text3 argument and click Rates tab 7. Click cell B4 8. Click OK Should be: =CONCAT(Rates!C4," ",Rates?B4)
14. Now that the worksheet for Marshall is complete, you can generate a bill for the Smith client for the week. All bill are due 60 days from the date the bill was created. Calculate the bill due date using the TODAY function
1. Click the Smith Bill sheet tab 2. Double-click cell C2 3. Type formula =TODAY( )+60 4. Press Enter
6. Add the PROPER function to the CONCAT formula so students name do not appear in all uppercase
1. Double-click cell A10 2. Type in formula box, =PROPER(CONCAT(C10," ",B10))
There is an error in B7. Accept Excels suggestion for fixing the error.
1. Double-click cell B7 2. Click the SmartTag Button 3. Click Update Formula to Include Cells
Enter a formula in cell C2 to calculate next year's salary increase by multiplying the previous years salary (cell B2) times the Cost of Living Adjustment (cell COLA). Use the cell name in the formula
1. Double-click cell C2 2. Type +B2*c 3. Double-click COLA and press Enter
On the SaleInventory worksheet, in cell E4, enter a formula to calculate the total stock value. Use the SUM function and use the range name StockValue as the function argument
1. Double-click cell E4 2. Type =SUM(St to display the Formula AutoComplete List 3. Double-click StockValue 4. Press Enter
Insert the current date in cell A1. Do not include the current time.
1. Go to Formula tab 2. Click Date & Time button 3. Click TODAY 4. Click OK
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument.
1. Go to Formula tab 2. Click Financial button 3. Select PMT 4. Type B3/12 in the Rate input 5. Type B4 in the Nper input 6. Type -B2 in the Pv input 7. Click OK
Use the Function Arguments dialog to enter a SUM function in cell B7 to calculate the total of cells B2:B6.
1. Go to Formula tab 2. Click Math & Trig button 3. Click SUM 4. Click OK
Enter a formula in cell B7 to display the text from cell A7 with only the first letter of each word in upper case
1. Go to Formulas tab 2. Click Text button 3. Click PROPER 4. Type A7 in the Text input 5. Click OK
Enter a formula in cell B10 to return a value of 35000 if the Net Profit After tax *cell B9) is greater than or equal to 470000 or 100 if it is not
1. Go to Formulas tab 2. Click Logical button 3. Click IF 4. Type B9>=470000 in Logical_test input 5. Type 35000 in the Value_if_true 6. Type 100 in the Value_if_false input 7. Click OK
Enter a formula in cell B3 using VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3. Use the name Abbreviation for the lookup table. The item names are located in column 2 of the lookup table. BE sure to require an exact match
1. Go to Formulas tab 2. Click Lookup & Reference 3. Click VLOOKUP 4. Type FALSE in the Range_lookup input 5. Type A3 in Lookup_value input 6. Type Abbreviation in Table_Array input 7. Type 2 in the Col_index input 8. Click OK
In cell E15, enter a formula to find the lowest line item cost this month (cells E2:E14)
1. Go to Formulas tab 2. Click More Functions button 3. Hover over statistical and select MIN 4. Type E2:E14 in the Number1 input box 5. Click OK OR 1. Click cell E15 2. Type =MIN(E2:E14) 3. Press Enter
This workbook includes two named ranges that both refer to cell B9. Delete the named range named CostOfLivingAdjustment
1. Go to Formulas tab 2. Click Name Manager 3. Click CostOfLivingAdjustment 4. Click Delete in the Name Manager dialog 5. Click OK 6. Click Close
Edit the CurrentSalaries named range so it refers to cells B2:B6 on the Salaries worksheet. Close the Name Manager when you are finished.
1. Go to Formulas tab 2. Click Name Manager 3. Click CurrentSalaries 4. Type =Salaries!$B$2:$B$6 in Refers to input 7. Click the checkmark item 8. Click Close
In cell E2, enter a formula using TEXTJOIN to combine the text from cells B2:D2 into a single text string. Use the # character as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument.
1. Go to Formulas tab 2. Click Text button 3. Click TEXTJOIN 4. Type # in the Delimiter input 5. Type TRUE in Ignore_empty input 6. Type B2:D2 in Text1 input 7. Click OK
Insert the current date and time in cell A1
1. Go to formula tab 2. Click Date & Time button 3. Click NOW 4. Click OK
In cell E15, enter a formula using a counting function to count the numbers in the Cost column (cells E2:E14).
1. Go to formula tab 2. Click More Functions 3. Click COUNT 4. Type E2:E14 in Value1 5. Click OK
In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon : in between. The result of the formula should look like this: Rent:Parking
1. Go to formulas tab 2. Click More Functions button 3. Click Text button 4. Click CONCAT 5. Type B2 in Text1 input 6. Type : in Text2 input 7. Type C2 in Text3 input 8. Click OK
On the Summary sheet, in cell B3, enter a formula to displat the value of cell E8 from the Expenses worksheet
1. Type = in cell B3 2. Click the Expenses worksheet 3. Click cell E8 4. Press Enter
On the Summary sheet, in cell B4, enter a formula to displat the value of cell B1 from the Income worksheet minus cell E8 form the Expenses worksheet
1. Type = in cell B4 2. Click Income worksheet 3. Click cell B1 and Type - 4. Click Expenses worksheet 5. Click cell E8 6. Press Enter
15. Enter formulas to reference the number of hours each staff member billed for Smith
1. Type = in cell B5 2. Click Lutz Hours sheet and click cell J11 and press Enter 3. Type = in cell B6 4. Click Marshall Hours sheet and click cell J11 and press Enter 5. Type = in cell B7 6. Click Stevens Hours sheet and click cell J11 and press Enter Should be: ='Lutz Hours'!J11 ='Marshall Hours'!J11 ='Stevens Hours'!J11
In cell B3, enter a formula to find the highest (maximum) monthly income total (cells B12:D12). Be sure to use a cell range not individual cell references
1. Type =MAX(B12:D12) in cell B3 2. Press Enter
In cell B4, enter a formula to find the lowest (minimum) monthly income total (cells B12:D12). Be sure to use a cell range not individual cell references
1. Type =MIN(B12:D12) in cell B4 2. Press Enter
Use a Formula AutoComplete to enter a SUM function in cell E12 to calculate the total of cell B12:D12. Be sure to use a cell range and not individuals cell references
1. Type =SU 2. Double-click SUM 3. Type B12:D12 and press Enter
On the SaleInventory worksheet, cells F7:F20 are selected. Name them as follows : StockValue
1. Type StockValue in the NameBox and press Enter
Statistical Functions
=COUNT(A1:A2) counts cells that contain #s w/in a specified range of cells =COUNTA(A1:A2) counts # of cells that are not blank w/in a specified range on cells =COUNTBLANK(A1:A2) counts # of blanks cells w/in a specified range of cells
A formula to display the value of cell A5 on the Inventory worksheet looks like this:
=Inventory!A5
PMT
=PMT(Rate,Nper,Pv) - Calculating monthly payments, the interest must be divided by 12
Text Function
=PROPER(C2) converts to first letter in each word to be capitalized =UPPER(C2) =LOWER(C2) =CONCAT combines values =TEXTJOIN
Date & Time Functions
=TODAY() : inserts current date =NOW() : insert current date and time
VLOOKUP
=VLOOKUP(Lookup_value,Table_array,Col_index,Range_lookup) - Under Lookup & Reference
Smart Tag
Select Help on this error to open Micro Office Help. Select Ignore error to leave cell unchanged. Select Edit in Formula bar to manually edit. Select Error Checking Options...
Formula AutoComplete
an Excel feature which, after typing an = (equal sign) and the beginning letter or letters of a function name, displays a list of function names that match the typed letter(s)
