Advanced+Excel+and+Using+Formulas

[|Excel lesson template]


 * How to enter formulas**

You are able to write any formula in Microsoft Excel. Formulas usually have at least two parts: constants and variables. Constants are numbers, such as 12 or -5. Variables represent a number, such x or n. For example, in the equation 2 + x = 5, 2 and 5 are constants, and x is a variable that represents the number 3.

Each constant and variable must have its own cell, or individual box in Excel.

There is a list of simple formulas built into Microsoft Excel. Some of these are already built into the Excel spreadsheet so that you can quickly see them: Average, Count, Max, Min, and Sum. In the bottom right corner of Microsoft Excel, it will by default say Sum. When you highlight, or select, a list of numbers, Excel will automatically display the sum total of those numbers.



You can change the Sum function to one of the others by clicking where Excel displays Sum in the bottom corner:

Sometimes, you may want to have one of these functions actually written into the spreadsheet. There is a format that you must follow each time you write a formula. First, choose the cell that you want to display the answer. In the picture, it will be E8. Begin the equation by typing = into the cell. Now, type in the function you want to use and open a parenthesis: =Sum(. At this point, you can type the names of the cells separated by commas (i.e. B2, C4, F4) or a range of cells using a colon (A1:A7 would include all of the cells from A1 through A7). Also, you can click and highlight the cells that you want to include. When you are done, close the parenthesis and press enter. This equation will be the coding (the programming that tells Excel what to do) behind the cell. When you press enter, it will only display the answer.

When you enter a formula in Microsoft Excel, it can only solve for one variable at a time. Therefore, you want to isolate the variable that you need to solve for.

View the following Geometry examples below. The Pythagorean Theorem is A2 + B2 = C2. A and B are variables relating to two sides of a right triangle. C is a variable that represents the hypotenuse.

Use Excel to solve for the missing side of the triangle. Image from www.mathwarehouse.com. Before we can write a formula in Excel, we need to isolate the variable we want to solve for. A = 73.24 B = 21 C = x

A2 + B2 = C2, or 73.242 + 212 = C2 C2 = 73.242 + 212 To get C by itself, we need to find the square root of A2 + B2. C = √(73.242 + 212)

The way that we would enter this in Excel is =(B2^2 + C2^2) ^(1/2).

The reason for this is that every formula has to be written so that Excel can read it. B2 is the cell where 73.24 is typed. C2 is the cell where 21 is typed. ^ is the symbol for squared in Excel. ^(1/2) is raising a number to the power of ½, which is the same as finding the square root. So, (B2^2 + C2^2) is the same as 73.24 squared + 21 squared. ^(1/2) raises 73.24 squared + 21 squared to the power of ½. By doing so, we are really finding the square root, so that Excel shows us the answer to C, not C2.





Excel allows users to use ranges of data without having to type every cell. The way to do this with a range in a column or row is to separate the first and last cells with a colon. A1:A10 would include all of the cells in column A from 1 through 10. A1:E1 would include the first five cells in the first row. Try an example. Put numbers in cells A1, A2, A3, A4, and A5. Then, in another cell, type =Sum(A1:A5). Your result should be similar to the following:

If all of the numbers required are not in a straight row or column, you can separate the cells in a range with commas. For example, =Sum(A1:A5, B2, C4). If you want to use the same group of cells several times, you can actually name them. Select different boxes with the command button on a Mac or the control button on a PC. For our purposes, select the cells that we have been using, then type a name you will remember (use “test” for now) in the Name Box, which is located immediately to the left of the formula bar, and press enter. Notice below that I typed “test” in the top left box.
 * Name Ranges**

Now that you have defined, or named, a range of data, you can treat that range like any other cells. Try using a basic function. Type =Average(test) into a blank cell. Do not use quotation marks (“ ”). Notice after you finish typing “test,” the cells in the range are highlighted. Press enter and Excel will return the average. You can use and manipulate a name range the same way that you would any other cell.
 * Templates**

A template is a specific type of file that has particular formatting in a program. For example, you may want to create a file that will allow students to find acceleration by entering the variables vf, vi, and t, where vf, is the final speed, vi is the initial speed, and t is time. You format an Excel document so that the cells are labeled, the equation is working, and everything is how you want it. Instead of a creating a new document every time you or your students work with acceleration, you can save it as a template. This way, you or you students can bring up the template, enter your individual information, and save it as another file. A common example of templates is a school wide lesson plan that every teacher has to complete on a weekly basis. The way that you save a template is similar to saving a normal file. Click File, Save As. This will bring up the following menu.

Under format, click Template. You can always tell an Excel template because the file will end in .xlt instead of .xls. Templates can be used anytime that you want to reuse information.


 * Database Functions**

Database functions are similar to simple functions such as Count, Average, and Sum; however, they are much more versatile and can do much more. The following exercise is adapted from www.meadinkent.com. The way the database functions DCOUNT, DSUM, and DAVERAGE work is that you define a region of data, such as every cell in the columns A, B, C, and D, and the rows, 3, 4, 5, and 6. Then, you tell Excel what column contains the data to be calculated and what the criteria is. Follow these steps to see how they work. Set up your spreadsheet so that you have three columns labeled “Doctors,” “Patients,” and “Age.”

The data range that we will use is A3:C9. This will encompass every cell in column A, column B, and column C in rows 3, 4, 5, 6, 7, 8, and 9.

Now, we have to tell Excel what criteria we want to look for. We will be using database functions to tell us the average age of Dr. Smith’s patients and the number of patients over 40. Set up the criteria as follows.

The range of data for the criteria will be the four cells E4:F5 (that is E4, E5, F5, and F6). This contains “Doctor,” “Dr. Smith,” “Age,” and “>40.”

Now we have to actually write the equations. Type =DAVERAGE(A3:C9, 3, E4, F5). This shows that the cells in the blue box A3:C9 are the data to be used, 3 is the row that tells Excel to look for Doctor, Patient, and Age, and the criteria is the green box in E4:F5.

This will show us that the average of Dr. Smith’s patients is 82. =DCOUNT(A3:C9,3,E4:F5) will tell us that he has three patients over 40 because the criteria is “Dr. Smith,” and “> (Over) 40.” There are only two instances that meet this information. The last database function is DSUM, which would add everything that meets the criteria. These database functions are very helpful when you are dealing with large quantities of information or if you need the computer to analyze information without you having to do by hand.

Conditional formulas in their most basic form allow you to determine is something is true of false. The way that you structure an IF formula is =IF(test, such as A2=4, what Excel should say if true, what Excel should say if false). So, an example is =IF(A2=4, True, False). If cell A2 has the number 4 in it, then the cell with the formula will say True. If it does not, it will say False.
 * Conditional Formulas**

Conditional formulas allows you to be flexible as well. Instead of =, you could use <, or >. Instead of True and False, you can use messages that are more relevant to what you are trying to accomplish. IF statements can also be used to display a value. Using the same formula that we already have, enter the number 2 in cell A3 and the number 3 in cell A4. Now, we can change the IF statement to manipulate the data. For example, =IF(A2=4, A3*A4, False). If A2 has a 4 in it, the cell with the formula in it will multiply the 2 and the 3, thus giving displaying a 6. If it does not have 4, it will still show False because we did not specify another value.


 * Formula Errors in Microsoft Excel**

Sometimes, entering a formula will result in an error. The error can be caused by several different scenarios. The most obvious is an error in syntax (the way that the formula is entered). If a formula is entered in a way that the computer cannot read, the following message will be displayed:

This simply means that something was not typed correctly and pressing OK will allow you to retype the formula.

There are several other errors that you should be aware of. Below is a brief list along with possible reasons for the errors:
 * 1) DIV/0! This is most likely caused when a number is divided by 0 or by an empty cell.
 * 2) This is not an error; it just means that the column is not wide enough to display the result.
 * 3) REF! This happens when a formula refers to an empty or deleted cell.
 * 4) VALUE! This occurs when there is an error in the type of data. For example, the formula is expecting a number, but one of the cells has a word in it.
 * 5) NAME? This happens when a name range does not exist, or quotation marks (“ ”) were used when not required.

If you would like to check your file for formula errors, click Edit in the file menu, then click Go To…, then click Special. It will bring up the following menu. Just check errors under Formulas and it will bring you to the formulas with errors in them.


 * Preventing errors**

The best way to prevent errors is to proofread your formulas. Make sure that they are written in a way that the computer or a calculator can read. Also, be sure to double check that every cell referenced in the formula has information in it. For example, if your formula is =A1+B1, make sure that both cells A1 and B1 have numbers in them. If there is an error in a formula, there will probably be a green triangle in the upper left hand corner of the cell that the formula is in. Click on that cell, then click on the exclamation point that comes up. When you click Trace Error, it will show an arrow that shows the cells involved and points to the formula in question.



Charts and Graphs Charting and graphing data is a helpful function that allows students and teachers to get a visual picture of the data. This is a definite benefit for visual learners who have a difficult time understanding numbers in isolation. The easiest way to make a chart is to input the data that you want to use and then highlight it. In this case, I took a poll with one of my classes and am creating a graph of their answers. Then, click the chart wizard button. In the following picture, the chart wizard button is to the left of the bright red toolbox.

The chart wizard allows you to choose different types of charts and graphs. You simply click on which type of graph you would like to use:

Select your graph and press next. It will bring up a screen where you can input a data range. If you already highlighted data, you can just press next again. If not, highlight or type a data range In the third step, it is good policy to make sure there is a title and, if possible, titles for the x-axis (horizontal) and y-axis (vertical). In this case, Excel automatically made the question the title of the graph.

Press next and the last step is to determine whether you want the chart displayed on the same screen or whether you want it displayed as its own file. Usually, it is most productive to leave it “As object in: Sheet 1.” This will just display the graph on your page. Final result: There are a few other modifications that you can do to make charts more useful. For example, if you click on the chart, then click the Chart menu on the top of the screen, then click “Add Trend Line,” it will allow you to choose different types of lines that are applicable in different situations. I added a linear trend line. The linear trend line shows the average increase or decrease from the beginning of a chart to the end.
 * Add data to an existing graph**

It is possible to add data to a graph that already exists. There are two ways to do this: as a new series or as new points. If you add a second data range as a new series, it will be a different color on the graph and allow you to differentiate between types of data. If you add data as new points, it will be the same color and allow you analyze more data. For an example, let’s say that for a physics lab, students had to measure velocity by finding distance traveled, in meters, over time, in seconds. Input the following data into a spreadsheet:

Select just the numbers and press the chart wizard button. Select an XY Scatter graph; these work best with trend lines. Press next. Now, type “Velocity” into the Chart Title, “Time (s)” into the x-axis because Excel takes the first set of info as the horizontal data in a graph, and “Distance (m)” in the y-axis. This will make the graph more intelligible to ourselves and the reader. Press next and finish. If you click in the white space of the chart, you can move it so that it does not cover your data. Now, type a second set of data next to the first. Often, students will complete a lab more than once to verify results. After you have done this, click on the chart and a new Chart menu will appear at the top of the screen where the Data menu was. Click on the Chart menu, the Add Data… When the Add Data menu comes up, click and highlight the second range of data and click OK. This will bring up a paste special menu. On the Paste Special menu, make sure the following boxes are checked: • Add cells as o New series • Values (Y) in o Columns • Check Categories (X Values) in First Column Click OK and you should have the following graph. Now, to make it easier to see the difference in the increase in velocity, click on the chart, then click the Chart menu, then click Add Trendline. You will have to do this twice to create a Trendline for both lines. Choose Linear and Based on: Series 1 the first time. Repeat and choose Based on: Series 2. After you have done this both times, you will have a chart the allows for easy differentiation of data from two separate trials of an experiment.

Additional Resources: @http://excelitch.com/