Back to the template. By now, you have your column headings in Row 3. To make your printout look nicer, you may want to realign R3 to center the headings. F for Format, C for Cells, Type in R3,tab over to align and hit C for center. Now you can begin to enter the information from your checkbook for January. Start with the check balance at the beginning of the month. This item has no check number and it's amount is not allocated to any expense so put the ALPHA description FORWARD in R4C2 (under Paid To)and then right arrow until the cell pointer is in the Balance Column(R4C11). Type the amount and press ENTER. Next you can begin filling in checks and deposits. At R5C1 enter the first check no. for January. Let's use 1234. As soon as you press enter, you'll see we need to make an adjustment. 1234.00 has been entered. This is because of the new default format we entered which is fine for dollars and cents but not so great for check nos. So we simply change the format of the cells in column 1 to be whole numbers or integers. And while we're at it, let's change the alignment format for Cl to Left to make a neater printout. Choose F for format, then Cells. To change all the cells in Column 1, type Cl where the system says Format cells, tab to align, type L, and tab to Format code and type I for integer. Now press ENTER and watch as the system changes the check number to 1234 and moves it to the left of the column. Next move the cell pointer to R5C2 and enter the payee for ck #1234 (let's use Duquesne Light). The check for $65 was a utility expense so right arrow to the utility column (R5C6) and enter 65.
Now we want to have the system calculate the new balance and put the answer in the Balance Column in R5. Check #1234 just happened to be entirely for utilities. Some checks however may be split among 2 or more expenses. So what we really want for the balance on the row is the balance from the previous row minus the sum of any expense on this row. Some rows will show income instead of expense so we need to enter a general formula here because the equation for the balance in row N should be the balance at N-1 plus the income in row N minus the sum of the expenses in row N. Sound confusing? Don't worry, it's harder to say than do with Multiplan.
Put the cell pointer at R5C11 where the balance to be calculated belongs. Next press = to tell the system you are going to enter a formula. The first term in the formula is the balance from the row above. Use the up arrow to move the cell pointer to the above balance. You will see the expression R[-1]C in the command line. This means the cell at row minus one in the same column. Next we want to add any income from the current row. Hit + and the cell pointer pops back down to R5C11(where we started building the formula) and the formula is now R[-1]C+. Select the deposit amt. for the same row by left arrowing until the cell pointer is in the deposit column. Notice that the formula is now R[-1]C+RC[-8). Since the deposit is in C3 and the balance is in C11, you can see why MP expresses this as Column minus 8.
Now for the formulas third term. We want to subtract the sum of any expenses in the same row from the balance. Type - and the cell pointer returns to R5C11. We will use the special function called SUM to get the sum of the expenses. When you use SUM, you must tell MP what cells to add together in a FROM:TO expression. Type SUM( and then indicate the first cell to include in the sum by left arrowing until you are under the first expense which is Food in C4. Type : to show that you're ready to put in the last cell to include in the sum. Again the cell pointer pops back to R5C11. Left arrow once to select the last cell of the group of expenses to be added together. Finally type ) to close the expression and then press ENTER. The finished formula is R[-1]C+RC[-8]-SUM(RC[-7]:RC[-1]). MP will calculate the balance and fill in R5C11.
The formula applies only to R5C11 but we'd like it to be used on every row in the balance column. Since we have expresed the formula in relative terms (for example, using R-1 instead of R4, the same formula can be used for every balance. You can have as many entries as you need for the month and just copy the formula down that many rows. Let's just assume you will have 10 entries. With the cell pointer still at R5C11, invoke the Copy command by typing C, then choose the option D for down and type 9 at number of cells. Press ENTER. MP will copy the formula down 9 rows. For now, this will give you identical balances in all 10 cells because there are no income or expense entries below R5 as yet. This will change as you make more entries.
When you finish filling in the checks and deposits, the final touch is to add the totals for the income and expense columns. Label the totals line by skipping a row after the last check no. and entering TOTALS under the Paid To column. Arrow one to the right and prepare to enter a formula by typing =. Here we'll use the SUM function again. Enter SUM( and then arrow up to R5C3 for the first item to sum. Type : and then use the up arrow for the last item (R14C3). Finish your formula by typing ). Check that the formula is SUM(R[-11]C:R[-2]C) and then press ENTER to see the income total appear. To total the other columns just copy the formula 7 cells to the right. C for copy, R for right and 7 for no. of cells.
Now press Function 8 and the entire sheet will be recalculated. Save your worksheet by pressing T for transfer, S for save and then enter a filename where the system says TEMP. If you have not recalculated with Function 8, MP will automatically do so before saving your spreadsheet. Next month we'll talk about printing the spreadsheet. If you have any questions, feel free to call."
Just wanted to say 'I'm glad I found you!' Pulled out my TI and have started to play with it again. I forgot how much fun it was. Wish there were more people out there still using it!
Main
Members Only
