In our first example, the solution was $252.61This was NOT typed into the keyboard. The formula that was typed into the spreadsheet was:
=PMT(C4/12,C5,-C3) C4 (annual interest rate) was divided by 12 because there are 12 months in a year. Dividing by 12 will give us the interest rate for the payment period - in this case a payment period of one month.
It is also important to type in the reference to the constants instead of the constants. Had I entered =PMT(.096/12,60,-12000) my formula would only work for that particular set of data. I could change the months above and the payment would not change. Remember to enter the cell where the data is stored and NOT the data itself.
Formulas are mathematical equations. There is a list of the functions available within Excel under the menu INSERT down to Function.
Formulas OR Functions MUST BEGIN with an equal sign (=).
Again, we use formulas to CALCULATE a value to be displayed.
When we are entering formulas into a spreadsheet we want to make as many references as possible to existing data. If we can reference that information we don't have to type it in again. AND more importantly if that OTHER information changes, we DO-NOT have to change the equations.
If you work for 23 hours and make $5.36 an hour, how much do you make? We can set up this situation using
- three labels
- two constants
- one equation
Let's look at this equation in B4: - = B1 * B2
- = 23 * 5.36
DO YOU KNOW which is BEST and WHY?
It is BEST if we can Reference as much data as possible as opposed to typing data into equations.
Change in FormulasIn our last example, things were pretty straightforward. We had number of hours worked multiplied by wage per hour and we got our total pay. Once you have a working spreadsheet you can save your work and use it at a later time. If we referenced the actual cells (instead of typing the data into the equation) we could update the entire spreadsheet by just typing in the NEW Hours worked. And -- you're done!
Let's look at the new spreadsheet: - hours have been changed to 34
- wage is the same
- total pay would now be = 34 * 5.36
- but would still be = B1 * B2
If we had typed in ( = 23 * 5.36 ) the first time and just changed the hours worked, our equation in B4 would still be ( = 23 * 5.36 )
INSTEAD we typed in references to the data that we wanted to use in the equation.
We typed in ( = B1 * B2 ). These are the locations of the data that we want to use in our equation.
It is BEST if we can Reference as much data as possible as opposed to typing data into equations.
Basic Math FunctionsSpreadsheets have many Math functions built into them. Of the most basic operations are the standard multiply, divide, add and subtract. These operations follow the order of operations (just like algebra). Let's look at some examples.
For these following examples let's consider the following data:
| |
| Operation | Data | Data | | |
| Multiplication | = 5 * 6 | = A1 * B3 | | |
| Division | = 8 / 4 | = A3 / B2 | ||
| Addition | = 4 + 7 | = B2 + A2 | | |
| Subtraction | = 8 - 3 | = A3 - B1 | |
Methods of Selecting Cells
Selecting cells in an equation is a very important concept of a spreadsheet. We need to know how to reference the data in other parts of the spreadsheet. When entering your selection you may use the keyboard or the mouse.
We can select several cells together if we can specify a starting cell and a stopping cell. This will select ALL the cells within this specified BLOCK of cells.
If the cells that we want to work with are not together (non-contiguous cells) we can use the comma to separate the cells or by holding down the control-key (command key on a MAC) and selecting cells or blocks of cells the comma will be inserted automatically to separate these chunks of data.
For the following examples let's consider the table below:
| |
or
=sum(Click On)
| To Select | ||
| A1 |
| |
| A1, A2, A3 |
| |
| A1, B1 |
| |
| A1, B3 |
| |
| A1, A2, B1, B2 |
|
No comments:
Post a Comment