Microsoft Excel Tutorial: Formulas and Functions
Uploaded by UTMLibrary on 14.03.2012
Transcript:
This tutorial has time links located in the description so you can fast-forward to the specific Excel help you need.
Microsoft Excel: A Formula Tutorial
Formulas are a great tool for calculating data directly into an Excel Sheet rather than using a calculator.
To enter a simple formula into a cell to calculate a number,
there are five basic steps:
For example, let's say we would like to enter in grades for Student 5's essay mark
We know that the student got 30 marks for the multiple choice section
and 36 points for the essay answers out of 100 points.
So let's add them together to get a total mark.
Step 1: Enter the equals sign. Step 2) Enter a number, in this case 30
Step 3) Enter an operator, such as the plus sign
Step 4) Enter another number to calculate which is 36.
Step 5) Press Enter or Tab. The new number is 66.
The Auto-sum formula sums up a range of cells quickly.
Let's say we would like to sum up all the marks that students received on Assignment 1.
Enter the equals sign, then write down 'sum', enter the open parenthesis,
select the range of cells you would wish to sum by clicking and dragging, and close the parenthesis.
and hit the enter or tab button.
The number we get is 359, but this is not a useful number.
However, calculating the mean is useful.
Complex Formulas are the ones that use multiple operators.
Remember the order of operations from math class.:
BEDMAS- so number one is brackets, then exponents,
division and multiplication (whichever comes first),
adding and subtracting, whichever comes first.
To find the mean of assignment one, divide the sum of grades,
which is 359 by the number of students, which is 5.
So the equation should read: equal sign, 'SUM', the range of the cells in parenthesis
divided by five.
When you enter or tab out of the cell, the class average is a mark of 71.8.
Applying the Same Formula to Multiple Cells.
Let's say you wish to apply that same formula to figure out averages to the other assignments and tests.
This can be quickly done by dragging the corner of the cell that we've just calculated
This can be quickly done by dragging the corner of the cell that we've just calculated
down to the exam row.
All the marks are put into place.
Functions are predefined formulas, so that you don't need to use multiple operators in complex equations.
Most common functions are the mean, maximum, minimum and standard deviation.
For example, let us calculate the average of the marks on Assignment 1 for our first example
but using the mean function instead. To insert a function,
click on the 'Formulas' tab and select 'Insert Function', scroll through the functions
to determine which ones you wish to use.
The bottom part of the window describes what the function does.
For example, the 'Average' function is described as 'Returns the average
of its arguments, which can be numbers or names, or references that contain numbers.
Click 'Ok'. Now select a range of cells to calculate the average. Click 'Ok'.
The formula reads like this: equals average, brackets, B2 to F2, brackets.
And the result is 71.8.
Copying Formulas: Relative Versus Absolute.
Relative References:
when we copied and pasted the formulas to find the averages of each assignment,
it was an example of a relative reference.
This is because the formula 'B2 to F2' in Assignment 1's row became B3:F:3 in Assignment 2's row
and so forth so that the class average is relative to their particular assignment, or row.
Absolute References: are used when you don't want B2:F2 to change when you change to a different row.
For example, let's say we have a list of clothing items and their regular prices but we would like to calculate their sale prices.
which are 30% off, or 70% of the regular price.
So to calculate the sale price of the straight leg jeans,
under the 'Sales Price', type in the equals sign, then click the cell containing the price of the jeans,
add the multiplication operator which is a star, and click on the sale's tax cell, which is B:2.
Hit the enter or tab button. The value of the jeans on sale is $46.87.
Now to apply the discount to all other items on the list,
you might be tempted to drag the corner of the cell with the applied formula down to all
the other items in the list. But as you can see,
the other items in the list. But as you can see,
the values of the other items do not make sense.
This is where absolute references come into play.
In formulas, absolute references are designated with dollar signs.
If something is typed 'dollar sign, A, dollar sign, 2', the column and row do not change when
they are copied and pasted.
'A, dollar sign, 2' indicates the row does not change when pasted,
and 'dollar sign, A, 2' indicates the column does not change when copied.
So before you drag the corner, type in the dollar sign before the 'B' and before the '2'.
Then drag down the corner, and the new values should be correct.
since they now all multiply with the absolute cell, which is 0.7.
For further instruction in Excel, please visit our Video Archives.
Microsoft Excel: A Formula Tutorial
Formulas are a great tool for calculating data directly into an Excel Sheet rather than using a calculator.
To enter a simple formula into a cell to calculate a number,
there are five basic steps:
For example, let's say we would like to enter in grades for Student 5's essay mark
We know that the student got 30 marks for the multiple choice section
and 36 points for the essay answers out of 100 points.
So let's add them together to get a total mark.
Step 1: Enter the equals sign. Step 2) Enter a number, in this case 30
Step 3) Enter an operator, such as the plus sign
Step 4) Enter another number to calculate which is 36.
Step 5) Press Enter or Tab. The new number is 66.
The Auto-sum formula sums up a range of cells quickly.
Let's say we would like to sum up all the marks that students received on Assignment 1.
Enter the equals sign, then write down 'sum', enter the open parenthesis,
select the range of cells you would wish to sum by clicking and dragging, and close the parenthesis.
and hit the enter or tab button.
The number we get is 359, but this is not a useful number.
However, calculating the mean is useful.
Complex Formulas are the ones that use multiple operators.
Remember the order of operations from math class.:
BEDMAS- so number one is brackets, then exponents,
division and multiplication (whichever comes first),
adding and subtracting, whichever comes first.
To find the mean of assignment one, divide the sum of grades,
which is 359 by the number of students, which is 5.
So the equation should read: equal sign, 'SUM', the range of the cells in parenthesis
divided by five.
When you enter or tab out of the cell, the class average is a mark of 71.8.
Applying the Same Formula to Multiple Cells.
Let's say you wish to apply that same formula to figure out averages to the other assignments and tests.
This can be quickly done by dragging the corner of the cell that we've just calculated
This can be quickly done by dragging the corner of the cell that we've just calculated
down to the exam row.
All the marks are put into place.
Functions are predefined formulas, so that you don't need to use multiple operators in complex equations.
Most common functions are the mean, maximum, minimum and standard deviation.
For example, let us calculate the average of the marks on Assignment 1 for our first example
but using the mean function instead. To insert a function,
click on the 'Formulas' tab and select 'Insert Function', scroll through the functions
to determine which ones you wish to use.
The bottom part of the window describes what the function does.
For example, the 'Average' function is described as 'Returns the average
of its arguments, which can be numbers or names, or references that contain numbers.
Click 'Ok'. Now select a range of cells to calculate the average. Click 'Ok'.
The formula reads like this: equals average, brackets, B2 to F2, brackets.
And the result is 71.8.
Copying Formulas: Relative Versus Absolute.
Relative References:
when we copied and pasted the formulas to find the averages of each assignment,
it was an example of a relative reference.
This is because the formula 'B2 to F2' in Assignment 1's row became B3:F:3 in Assignment 2's row
and so forth so that the class average is relative to their particular assignment, or row.
Absolute References: are used when you don't want B2:F2 to change when you change to a different row.
For example, let's say we have a list of clothing items and their regular prices but we would like to calculate their sale prices.
which are 30% off, or 70% of the regular price.
So to calculate the sale price of the straight leg jeans,
under the 'Sales Price', type in the equals sign, then click the cell containing the price of the jeans,
add the multiplication operator which is a star, and click on the sale's tax cell, which is B:2.
Hit the enter or tab button. The value of the jeans on sale is $46.87.
Now to apply the discount to all other items on the list,
you might be tempted to drag the corner of the cell with the applied formula down to all
the other items in the list. But as you can see,
the other items in the list. But as you can see,
the values of the other items do not make sense.
This is where absolute references come into play.
In formulas, absolute references are designated with dollar signs.
If something is typed 'dollar sign, A, dollar sign, 2', the column and row do not change when
they are copied and pasted.
'A, dollar sign, 2' indicates the row does not change when pasted,
and 'dollar sign, A, 2' indicates the column does not change when copied.
So before you drag the corner, type in the dollar sign before the 'B' and before the '2'.
Then drag down the corner, and the new values should be correct.
since they now all multiply with the absolute cell, which is 0.7.
For further instruction in Excel, please visit our Video Archives.