How to calculate formulas and functions in Excel using Windows

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!