ShareThis

Wednesday, March 3, 2010

Excel III



Formulas in Excel

Formulas are entries that have an equation that calculates the value to display. We DO NOT type in the numbers we are looking for; we type in the equation. This equation will be updated upon the change or entry of any data that is referenced in the equation.

In our 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
Both of these equations will produce the same answers, but one is much more useful than the other. 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.

No comments:

Post a Comment