Excel 2007 Overview Guide - Part 4 - Formula Bar, Home Menu, Quick Access Toolbar and the Status Bar

Uploaded by kbcoble on 04.10.2010

Transcript:

Excel Formulas, Order of Operations, and Simple Functions

Hello, my name is Kelly, and in this screen cast I will be using an Excel spreadsheet

that already has some information typed into it. I am going to use it to demonstrate how

to use formulas, order of operations, and simple functions.

As you can see I have a spreadsheet up that will let us do Fahrenheit to Celsius conversions

for temperatures. There is already a formula provided for converting

Fahrenheit into Celsius therefore all we have to do is enter this formula into the appropriate

location in the spreadsheet so that Excel can make the calculations. In all cases when

typing a formula or function into Excel you must first put an = sign. By putting an =

sign this lets Excel know that what follows is a formula and that it should use for calculation.

So I am going to highlight the cell where the formula should be to calculate the Celsius

temperature. You start with = then you type in the formula as shown above except by substituting

the cell that contains the Fahrenheit temperature in place of the F. So I start with an = then

click on the cell address of the Fahrenheit temperature which automatically puts the cell

address in the formula, A4, then minus 32 times 5 divided by 9. After pressing enter

the result of Excel applying the formula shows up in the cell instead of the long formula.

Something went wrong here. 32 degrees Fahrenheit is supposed to be 0 degrees Celsius. I believe

I must have forgotten something in the formula. I forgot to put in the parentheses in around

the Fahrenheit degree and the 32. Parentheses are necessary because we need the formula

to calculate the minus, the subtraction before the multiplication and division. Always remember

that if you don’t have the correct order of operations the calculation you are trying

to make could turn out wrong. When we press enter this time, notice that the conversion

is correct. The formula gave 0 degrees Celsius as the answer. Next you move on to the next

cell down however, instead of typing the formula every time, I would like to introduce you

to the fill handle. Notice when you click on a cell, there is a dot in the lower right

corner of the selected cell. You are able to click this dot and drag a box down to the

final cell you want to make calculations in. This fills the content of the cell with the

same formula that was in the original cell. Notice that for the first cell it has the

cell address A4 in the formula and in the second cell it has A5, the third A6. The reason

A4 changes to A5 and to A6 is because we have used a relative cell address. That means the

row number in the cell address changed RELATIVE to the row we were on when we filled the formula

containing that cell address. For more information about relative and other types of cell addresses,

check out Dawn’s screencast on that topic. The fill handle is very neat because it saves

a lot of time and it is very easy to use. Next I am going to show you a spreadsheet

designed to calculate averages, minimums, and maximums.

As you can see here is a spreadsheet keeping track of students’ grades on tests. The

goal is to get a test average for each student as well as a class average for each test.

Another interesting thing here is how to generate the class high and class low on each test.

For starters, to generate the test average of a student, you may use an Excel predefined

formula, which is called a function. Each time you enter a formula you must put an equals,

so we will start with equals and then this formula will use the function average. In

this case you would select the cell where the average will be generated and write =AVERAGE(

. Then using the mouse, click and drag the box over the cells you want to select the

class average. So we want to do Billy’s test averages we would select the numbers

from test 1 to test 4 which is cells B2 to E2 and then press [enter] to calculate the

average. A shortcut way to do this would be to locate

the formulas tab in the ribbon at the top of the screen. Notice that there are options

you can choose from as it relates to formulas. Notice in the function library group there

is an icon that says AutoSum and an arrow down. Click on the down arrow of auto sum

and select, and see that it says average. What this does is it automatically places

the =AVERAGE( in the cell and all you have to do is select the cells that will determine

the average. So to calculate Samantha’s average you will select cells B3 to E3 and

then press [enter]. You may also use the fill handle as we did

before on this and it will copy the average formula down for the remaining students and

calculate all the students’ test averages for you. Once again, notice that the cell

address changed from row B3 to E3 to the cell row B4 to E4 and then B5 to E5.

To generate the class average of a test you would use the same principle. I’m going

to use the average function again, but this time I am going to select all the students’

grades on the test one for the average. This means that Excel has added up all the students’

grades in the class then divided that sum by how many students there are in the class.

Thus, I have just calculated the class test average for the class on the first test.

In order to calculate the class average for the other columns use the fill handle and

highlight those 3 cells to the right. Again, the cells contents across that row changed

to be appropriate for the column. Notice in column C it changed to C2 to C18 and in column

D, it changed to D2 to D18. The class high can be determined by using

the Excel defined function =MAX( into the cell where you want to show the maximum test

grade in the class. This formula displays the highest grade out of all those selected.

So I am going to put =Max( then select all the student’s grades on Test 1 and press

enter. Again if you want a shortcut, go to the ribbon

and use the formulas tab to select AutoSum. The down arrow has a function that says MAX.

First, make sure the cell you are going to place it in is highlighted and then select

MAX and the cells that you want Excel to generate the maximum from.

To fill the remaining cells with the class maximum again, use the fill handle and Excel

will automatically fill them in for you. The same is for the Class Low there is a shortcut

key for minimum under AutoSum. Select that, then highlight the cells of all the student’s

grades from the test and press enter. From this you have generated the lowest grade in

the class on test #1. Now I will use the fill handle again and fill the other cells on the

row with the minimum test grades. Now this spreadsheet is complete with student test

averages, class test averages, class maximums and class minimums.

So to review all that I have went over in this screencast, remember that a formula is

something used to calculate certain values. It can include numbers, cell addresses, order

of operations, and possibly a function to generate a calculation. A function is something

that is predefined by Excel that already knows how to calculate a certain answer. So for

example to calculate average, Excel already knows that the formula to calculate average

is the sum of all the numbers divided by how many numbers there are. The auto-fill handle

is especially important because it fills the content of the cell with the same formula

that was in the original cell. This can save a lot of time because you don’t have to

individually write the formula or function in the other cells because Excel does it for

you. I hope this screencast gave you a better understanding of how to use formulas and functions

in Excel. Thank you for watching!

Hello, my name is Kelly, and in this screen cast I will be using an Excel spreadsheet

that already has some information typed into it. I am going to use it to demonstrate how

to use formulas, order of operations, and simple functions.

As you can see I have a spreadsheet up that will let us do Fahrenheit to Celsius conversions

for temperatures. There is already a formula provided for converting

Fahrenheit into Celsius therefore all we have to do is enter this formula into the appropriate

location in the spreadsheet so that Excel can make the calculations. In all cases when

typing a formula or function into Excel you must first put an = sign. By putting an =

sign this lets Excel know that what follows is a formula and that it should use for calculation.

So I am going to highlight the cell where the formula should be to calculate the Celsius

temperature. You start with = then you type in the formula as shown above except by substituting

the cell that contains the Fahrenheit temperature in place of the F. So I start with an = then

click on the cell address of the Fahrenheit temperature which automatically puts the cell

address in the formula, A4, then minus 32 times 5 divided by 9. After pressing enter

the result of Excel applying the formula shows up in the cell instead of the long formula.

Something went wrong here. 32 degrees Fahrenheit is supposed to be 0 degrees Celsius. I believe

I must have forgotten something in the formula. I forgot to put in the parentheses in around

the Fahrenheit degree and the 32. Parentheses are necessary because we need the formula

to calculate the minus, the subtraction before the multiplication and division. Always remember

that if you don’t have the correct order of operations the calculation you are trying

to make could turn out wrong. When we press enter this time, notice that the conversion

is correct. The formula gave 0 degrees Celsius as the answer. Next you move on to the next

cell down however, instead of typing the formula every time, I would like to introduce you

to the fill handle. Notice when you click on a cell, there is a dot in the lower right

corner of the selected cell. You are able to click this dot and drag a box down to the

final cell you want to make calculations in. This fills the content of the cell with the

same formula that was in the original cell. Notice that for the first cell it has the

cell address A4 in the formula and in the second cell it has A5, the third A6. The reason

A4 changes to A5 and to A6 is because we have used a relative cell address. That means the

row number in the cell address changed RELATIVE to the row we were on when we filled the formula

containing that cell address. For more information about relative and other types of cell addresses,

check out Dawn’s screencast on that topic. The fill handle is very neat because it saves

a lot of time and it is very easy to use. Next I am going to show you a spreadsheet

designed to calculate averages, minimums, and maximums.

As you can see here is a spreadsheet keeping track of students’ grades on tests. The

goal is to get a test average for each student as well as a class average for each test.

Another interesting thing here is how to generate the class high and class low on each test.

For starters, to generate the test average of a student, you may use an Excel predefined

formula, which is called a function. Each time you enter a formula you must put an equals,

so we will start with equals and then this formula will use the function average. In

this case you would select the cell where the average will be generated and write =AVERAGE(

. Then using the mouse, click and drag the box over the cells you want to select the

class average. So we want to do Billy’s test averages we would select the numbers

from test 1 to test 4 which is cells B2 to E2 and then press [enter] to calculate the

average. A shortcut way to do this would be to locate

the formulas tab in the ribbon at the top of the screen. Notice that there are options

you can choose from as it relates to formulas. Notice in the function library group there

is an icon that says AutoSum and an arrow down. Click on the down arrow of auto sum

and select, and see that it says average. What this does is it automatically places

the =AVERAGE( in the cell and all you have to do is select the cells that will determine

the average. So to calculate Samantha’s average you will select cells B3 to E3 and

then press [enter]. You may also use the fill handle as we did

before on this and it will copy the average formula down for the remaining students and

calculate all the students’ test averages for you. Once again, notice that the cell

address changed from row B3 to E3 to the cell row B4 to E4 and then B5 to E5.

To generate the class average of a test you would use the same principle. I’m going

to use the average function again, but this time I am going to select all the students’

grades on the test one for the average. This means that Excel has added up all the students’

grades in the class then divided that sum by how many students there are in the class.

Thus, I have just calculated the class test average for the class on the first test.

In order to calculate the class average for the other columns use the fill handle and

highlight those 3 cells to the right. Again, the cells contents across that row changed

to be appropriate for the column. Notice in column C it changed to C2 to C18 and in column

D, it changed to D2 to D18. The class high can be determined by using

the Excel defined function =MAX( into the cell where you want to show the maximum test

grade in the class. This formula displays the highest grade out of all those selected.

So I am going to put =Max( then select all the student’s grades on Test 1 and press

enter. Again if you want a shortcut, go to the ribbon

and use the formulas tab to select AutoSum. The down arrow has a function that says MAX.

First, make sure the cell you are going to place it in is highlighted and then select

MAX and the cells that you want Excel to generate the maximum from.

To fill the remaining cells with the class maximum again, use the fill handle and Excel

will automatically fill them in for you. The same is for the Class Low there is a shortcut

key for minimum under AutoSum. Select that, then highlight the cells of all the student’s

grades from the test and press enter. From this you have generated the lowest grade in

the class on test #1. Now I will use the fill handle again and fill the other cells on the

row with the minimum test grades. Now this spreadsheet is complete with student test

averages, class test averages, class maximums and class minimums.

So to review all that I have went over in this screencast, remember that a formula is

something used to calculate certain values. It can include numbers, cell addresses, order

of operations, and possibly a function to generate a calculation. A function is something

that is predefined by Excel that already knows how to calculate a certain answer. So for

example to calculate average, Excel already knows that the formula to calculate average

is the sum of all the numbers divided by how many numbers there are. The auto-fill handle

is especially important because it fills the content of the cell with the same formula

that was in the original cell. This can save a lot of time because you don’t have to

individually write the formula or function in the other cells because Excel does it for

you. I hope this screencast gave you a better understanding of how to use formulas and functions

in Excel. Thank you for watching!