Microsoft Excel 2003
Introduction
Using Formulas
Formulas and functions are mathematical statements used to perform calculations.
- A formula is an instruction made up by the user to perform a specific calculation.
- All formulas must start with an equal sign and contain cell addresses, numbers, and mathematical operators.
- When preparing a formula it is important to consider the order of mathematical operations. A formula that has more than one operation follows an order of precedence.
The order is as follows from left to right:
- Exponentiation
- Multiplication
- Division
- Addition
- Subtraction
Note: If a formula has parenthesis, the operation (s) in the parenthesis is performed first. Remember to use the math acronym Please Excuse My Dear Aunt Sally to assist with the order of operations.
Example:
|
Operator
|
Meaning
|
Example
|
Result (If A1=18 & A2=2)
|
|---|---|---|---|
|
ˆ
|
Exponentiation
|
=A1^A2
|
324
|
|
*
|
Multiplication
|
=A1*A2
|
36
|
|
/
|
Division
|
=A1/A2
|
9
|
|
%
|
Percentage
|
=A1%A2
|
.18
|
|
+
|
Addition
|
=A1+A2
|
20
|
|
-
|
Subtraction
|
=A1-A2
|
16
|
Exercise 1
A quick review of the basic functions used in a spreadsheet.
How to:
- Start Microsoft Excel and create the worksheet listed below.
- Enter the labels and values in the exact cell locations shown in the spreadsheet. The sale tax is approximately is 6%.
- Enter the formulas, as shown in the shaded cells. Tap the Enter key after each formula.
- Copy the formula to the respective cells by using the fill handle and drag downward.
- Click inside cell C10 and then click on the down arrow next to the Auto Sum button ‘Σ’ located on the Standard Toolbar. Choose the function Average from the menu.
- Select the cell range G4:G6.
- Repeat the prior steps for cell C11, but this time use the Sum function.
- Format the cells using the currency format with the dollar sign.
| 1 |
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|---|---|---|---|---|---|---|---|
|
2
|
Merchandise
|
List Price
|
Discount
|
Purchase Price
|
Sales Tax
|
Total
|
|
|
3
|
|||||||
|
4
|
Printer | 345 | 185 | =C4-D4 | =F4*$C$8 | =E4+F4 | |
|
5
|
Computer | 985 | 265 | ||||
|
6
|
Monitor | 395 | 98 | ||||
|
7
|
|||||||
|
8
|
Sales Tax | .06 | |||||
|
9
|
|||||||
| 10 | Average Cost | =AVG(G4:G6) | |||||
| 11 | Grand Total | =SUM(G4:G6) |
Using the Payment (PMT) Function
Exercise 2
In this exercise a spreadsheet is created to calculate loan payments based on data enter by the user. Data that includes the purchase price, interest rate, down payment, and the term of the loan. Utilizing the Payment Function the user can increase or decrease any of the variables to change the monthly payment.
How to:
- In a new worksheet type the following column and row headings.
| Cell | Headings |
|---|---|
|
A1
|
Payment Calculator |
|
A3
|
Sticker Price |
|
A4
|
Interest Rate |
|
A5
|
Down Payment |
|
A6
|
Loan Amount |
|
A7
|
Months |
|
A8
|
Monthly Payment |
- Select the cell range A1:C1: and then click on the Merge & Center button located on the Formatting Toolbar. Change the font to size 12.
- Select the cell range A3:B3 and then click on the Merge & Center button. Repeat the same steps for the following cell ranges - A4:B4, A5:B5, A6:B6, A7:B7, and A8:B8.
- Select the cell range A3:C8. On the Formatting Toolbar click on the Bold (B) and Align Left buttons.
- While holding the Ctrl key down select cells C3, C5, C6, and C8. Next, right click the mouse and choose Format Cells | Number | Currency | Decimal, with 2 places and the $ sign. Select the fourth format from the negative numbers box to display the debit format.
- Select cell C4, right click the mouse and then choose Format Cells | Percentage | Decimal places 2. Click on the button OK and then press the Enter key.
- Select cell C7, right click the mouse and then choose Format Cells | Number, change the decimal places to
0. - In cell C6 type =C3-C5 and then press the Enter key.
- Select cell C8 and then click on the Insert Function button located on the Formula Bar.
- When the Formula dialog box opens choose the PMT function from the drop down menu.
- Place the mouse pointer on the top part of the dialog box and then hold down the left mouse button. Drag the dialog box out of your way so that you can see the cells you are working with. Release the mouse button.
- Place the insertion point in the Argument box Rate. Click on cell C4 than press the forward slash key (/) for division. Enter the number 12 for the annual interest rate.
- Place the insertion point in the Argument box Nper (number of payments) and then click on cell C4.
- Place the insertion point in the Argument box Pv (present value of the loan amount) and then type a negative sign (-). Click on cell C6. Placing the negative sign in the box allows for the display of a positive number.
- Save the worksheet.