Lexington Public Library | Reading is Just the Beginning ...



Intermediate Features of Excel541147019685Functions & FormulasSymbols11+add-subtract/divide*multiply(cell:cell)series of numbers=equal to<less than>greater than<=less than or equal to>=greater than or equal to<>not equal toPriority of CalculationsFirst( ) ^Second* /Third+ -Sample12sTo add two cells=B1+B6To add more than two cells=SUM(A1,A2,B1,B4)=SUM(A1:A4)To subtract two cells=C3-C4To multiply two cells=A1*B1To divide two cells=C3/C4To calculate an average=AVERAGE(A1,A5,A6)=AVERAGE(A1:A3)2003425-154940Common FunctionsSUM (to add the values in a range of cells)Syntax:AB154236345427563=SUM(number1,number2,number3,etc.)For example:=SUM(A1,A2,A3,B1,B2,B3)=SUM(5,3,4,4,6,5)=27AVERAGE(to return an average for a range of cells)Syntax:=AVERAGE(number1,number2,number3,etc.)For example:=AVERAGE(A1,A2,A3,A4,A5)=AVERAGE(5,3,4,2,6)=4You can also represent the five cells in a series. For example:=AVERAGE(A1:A5)MAXIMUM(to return the largest value in a range of cells)Syntax:=MAX(number1,number2,number3,etc.)For example:=MAX(A1:B5)=7MINIMUM(to return the smallest value in a range of cells)Syntax:=MIN(number1,number2,number3,etc.)For example:=MIN(A1:B5)=2ROUNDAB15.234.9123.146.5634.745.1142.247.3256.813.09 (to round a number to a specified number of decimal places)Syntax:=ROUND(number,num_digits)For example:=ROUND(A1,1)=5.2=ROUND(B1,0)=5IF(to test data and return one value if passes test, another value if fails test)Syntax:= IF(logical_test,value_if_true,value_if_false)For example:=IF(A1<5,”discontinue”,”renew”)=renew=IF(B1<5,”discontinue”,”renew”)=discontinue5143500-290830Troubleshooting...Errors in FormulasMake sure all parentheses are part of a matching pair. When you create a formula, Excel will display parentheses in color as they are entered.Make sure you use the correct range operator when you refer to a range of cells. When you refer to a range of cells, use a colon (:) to separate the reference to the first cell in the range and the reference to the last cell in the range. Make sure you have entered all arguments that are required. Some functions have required arguments. Also, make sure you have not entered too many arguments.You can enter, or nest, no more than seven levels of functions within a function.Make sure each external reference contains a workbook name and the path to the workbook. If the name of a workbook or a worksheet you refer to contains a non-alphabetic character, you must enclose the name within single quotation marks.Do not format numbers as you enter them in formulas. For example, even if the value you want to enter is $1,000, enter 1000 in the formula. To format the result of a formula, click Cells on the Format menu, click the Number tab, and then select a format.Error ValuesIf a formula cannot properly evaluate a result, Excel will display an error value. Error values can be the result of:Using text where a formula expects a numeric valueDeleting a cell that is referenced by a formulaUsing a cell that is not wide enough to display the result.Error values might not be caused by the formula itself. For example, if a formula shows #N/A or #VALUE!, a cell referenced by the formula may contain the error. What does ##### mean?!?The numeric value entered into a cell is too wide to display within the cell. You can resize the column by dragging the boundary between the column headings.The formula in the cell produces a result that is too long to fit in the cell. You can increase the width of the column by dragging the boundary between the column headings or by changing the number format for the cell. To change the number format, click Cell on the Format menu, click the Number tab, and then select another format.When you subtract dates and times, make sure you build the formula correctly. Dates and times in Microsoft Excel must be positive values. If the date or time formula produces a negative result, Microsoft Excel will display #### across the width of the cell. To display the value, click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.186690031750Exercise #1Student GradesGETTING STARTEDClick the Office Button, and then Open. In the dialog box that appears, find and open the file Student Grades.xls. This file is located in the My Documents folder under Excel ANIZING YOUR WORKBOOKFor ease of identification, let’s change the names of the Sheets. Double click the Sheet1 tab and type “Grades.” For the Sheet2 tab, right click and select Rename. Type “Tests.” Also notice on the right-click menu the option of “Tab Color.” You can use this to color code the sheet tabs in Excel.You can also move sheet tabs around in Excel. We are going to move the Tests tab to the front, placing the Grades tab in the second position. To move worksheets, click and hold the left mouse button over the tab. An icon of a sheet of paper appears under the point of the arrow. Now drag to the right. As the cursor moves between tabs, a downward pointing arrow appears between each tab. At the point where you would like to place the Tests tab, let go, dropping it into that location.USING INSERT FUNCTIONMake sure the Tests tab is showing.Click inside cell J3.To perform an AVERAGE calculation, click the INSERT FUNCTION button. (The fx on the Formula Toolbar.)Select AVERAGE and click OK.If you cannot see the necessary cells, click and drag the FORMULA PALETTE out of the way.Select the cells you wish to AVERAGE, in this case, cells B3 to I3 (B3:I3).On the FORMULA PALETTE, the result should say 58.375.Click OK to place that value in cell J3.USING AUTOFILLTo copy the formula, select cell J3 and place the cursor, a thick white plus, on the lower right corner. The tiny dark green square is called the FILL HANDLE. The white plus turns into a thin black plus sign. Click, hold, and drag down to cell J12. Excel copies the AVERAGE formula to the other cells. Each row calculates from the figures on that row. We are going to use these test scores to help us figure these students’ overall grades.On the Grades worksheet, notice that the Attendance column has the number 100 in the first cell. Assume all students had perfect attendance. We want to carry the number 100 over to all the students’ attendance. Click cell D4.Move the mouse pointer to the lower-right corner, over the tiny, dark square (Fill Handle), until the mouse pointer becomes a thin, dark plus.Click, hold, and drag down to cell D13. AutoFill carries that number to the other cells.NOTE: Using AutoFill in Excel will also allow you to complete lists of times, days, months, etc. in a sequential series.LINKING CELLSOn the Grades tab, one column is titled “Test Average.” We will link the cells in this column to the cells on the Tests worksheet that have the values we need.On the Grades worksheet, select cell B4.553275583185Click back over to the Tests worksheet and select cells J3 through J12.Click the Copy button located in the Clipboard section of the Home tab. A moving line should appear around the selected cells.Go back to Grades worksheet. Make sure cell B4 is selected.Right-click over the selected cell B4 and choose Paste Link on the menu that appears.The test averages are now linked from the Tests worksheet. If data changes on the Tests sheet, it will automatically be reflected on the Grades worksheet, as well.INSERTING DATAClick back to Tests. Notice that Sara had one missed test (the zero) and that her current average is 71.125.Replace the 0 in cell G5 with 87, Sara’s score on her make-up test. Hit Enter. The new average should read 82.Click over to the Grades worksheet. Sara’s average has been adjusted there also.INSERTING A CELLLook at the Paper Averages. Something is wrong. Somehow, when typing, the teacher forgot to enter a student’s average. Once you determine which number is missing, we will assume in this case Leela’s, click the cell that needs the new number, cell C11.Click on the Insert button located in the Cells section of the Home tab. The cells beneath will be shifted down by one row. To shift the cells down is the default setting. NOTE: Shifting the cells down may move the thick black line that’s across the bottom of the column. Click on cell C14, then click the arrow next to the Borders button in the Font section of the Home tab and select “No Border”. We will fix the bottom border later.Type 97.5 and press ENTER.CALCULATING THE FINAL WEIGHTED GRADETo determine the final grade for Neil, first click inside cell E4.To create a formula, we begin by typing an equal sign.Next, we want to perform an addition, so we type SUM.We have to isolate the calculations and cell references, so type a beginning parenthesis.The first step is to represent the test averages. The test scores will represent 50% of the final grade, so we multiply 50% times the test average. We can do this by clicking cell B3 which holds the 50%, typing the asterisk *, and clicking cell B4 for Neil’s Test Average. We follow this entry by a comma.Next, repeat for the Paper Average, followed by a comma.Repeat for Attendance.Follow the entire formula with an ending parenthesis.The formula should read “=SUM(B3*B4,C3*C4,D3*D4)”. Hit Enter. The result is 58.4475.Instead of typing this formula for every student, we can copy it. Make sure cell E4 is selected. Click the Fill Handle at the lower right corner. Hold and drag down to cell E13. Excel has calculated the final grade for each student.But wait! The new grades are much too high! Click on cell E5 and look at the cell references for that formula. It reads “=SUM(B4*B5,C4*C5,D4*D5).” It shifted correctly one row down to pull the right cells of B5, C5, and D5 for Tyler. However, it incorrectly shifted the percentage cells down one row also.To correct the error, we must make the cell references to B3, C3, and D3 absolute in the calculation for Neil. We do this by adding dollar signs ($). Click on cell E4 again.In the Formula Bar, edit the formula to read “=SUM($B$3*B4,$C$3*C4,$D$3*D4).”Use the AutoFill handle to pull the formula down for each of the other students.USING THE OFFICE ASSISTANT FOR FORMULA HELPClick into cell F4.We need to find out how to eliminate decimal places. Click the fx button just to the left of the formula bar. When it opens, type “round decimals” into the search box at the top and click Go.Several rounding functions should appear, including ROUND, ROUNDUP, and ROUNDDOWN. Select ROUNDUP and click OK. A dialog box will open allowing you to enter what cells you want to round and how many decimal places you want them to have. Put E4 in the upper Number box and 0 in the lower Num_digits box. The formula in your formula bar should look like this =ROUNDUP(E4,0). Press OK. The number 59 should appear in F4.ARRAY FORMULASLet’s try a different type of formula. We want to curve the students’ grades, so we are going to write a formula that will look up the grades of the students and calculate a bump up. This is helpful when figures are constantly changing. Excel will automatically update the Adjusted Grade if we write the formula just right.Click into cell G4 under “Adjusted Grades.”We need to add the Rounded Grade in cell F4 to the difference between a perfect grade of 100 and the highest grade in this group of students. Type the formula “=SUM(F4,100-MAX($F$4:$F$13)).” We use the MAX function to return the highest number in the series F4 to F13. We use the symbol $ to tell Excel to make reference to that series of cells and no other cells.Press ENTER. The number that appears should be 63.Use the AutoFill to copy the formula down to cell G13.Notice Leela’s grade is now 100. She had the highest grade of 96. Excel calculated the difference between her score and a perfect score, or 4 points. Excel then added 4 points to every grade.THE IF FUNCTIONLet’s try one more kind of function. The IF Function allows Excel to make a comparison based on information stored in a given spreadsheet or file. Usually this action results in an IF/THEN situation. If a certain criterion is met, then a certain action will result. We are going to use this function to calculate which students passed and which failed.Click into cell H4.IF formulas are constructed as follows: =IF(test, true, false). Our formula will test Neil’s “Adjusted Grade” to see if it is 60 or above. If this is true, he “passes.” If this is false, he “fails.”Type the formula “=IF(G4>59,”pass”,”fail”). Hit Enter. He passed!Use AutoFill to copy the formula down for all the students.Click back over to the Tests sheet. Change Jaime’s last test grade, cell I11, to 0. Her previous “Adjusted Grade” was 62, barely passing. After placing a 0 in cell I11 in Tests, her “Adjusted Grade” becomes 59. Excel automatically updates her to “fail” in column H.CREATING A CHARTFor the next step, we will create a chart. We want to see if there is any tendency for test scores to be lower or higher at certain times. Click back over to the Tests tab. Select cells A3 to I12, just the names and test scores of the students, not the averages.Click the Line Chart button located in the Charts section of the Insert tab.Now select Line with Markers, the first choice in the second row.We can choose whether to organize our chart by rows or columns. We want each color to represent a different student and the marked data to be the scoring of each test. Selecting to show the series by rows will organize our chart in this way. Click the Switch Row/Column button located in the Data section of the Chart Tools Design tab to toggle between these two modes.You can move and/or resize your chart so that it doesn't cover your data. You can also choose to create a new tab just for the chart. To do this, first select the chart, then click the Move Chart button located in the Location section of the Chart Tools Design tab. When the Move Chart dialog box appears, click in the bubble to the left of New sheet. Click OK. This places the chart in its own worksheet tab labeled Chart1.*NOTE: If the line at the bottom of columns A through H on the Grades sheet is missing on some or all of the columns, click on cell A13, hold the mouse button down and drag across to H13. Then click the arrow beside the Borders button in the Font section of the Home tab and select Thick Bottom Border from the available options.Self-Evaluation: Additional Features of ExcelPlease keep this form for your own reference.Evaluate your understanding of the following topics. Be sure to ask your instructor to clarify any concepts you find icvery clearcleara little confusedvery confusedRenaming worksheets and adding colorCreating formulasUsing office assistant to search for functionsCell ranges (cell:cell)Inserting cellsLinking cellsSample functions (SUM, AVERAGE, ROUNDUP, IF)Advanced formula types (general concepts)Date and Time formulas (TODAY, WEEKDAY, DATEDIF)Text formulas (CONCATENATE, UPPER)Creating a TableSorting and Filtering a TableExamining Data and Creating Reports with Pivot Tables and Charts**Optional: using Data Validation and Lookup functionsIMPORTANT:Before moving onto the next class, you should answer “very clear” or “clear” to most of the above.If you need further help with these topics, consider taking the class again after practicing your skills independently. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download