New accounting year item 18 Method 3 pg 172 173 174

Method 3: Use Formula in Column TEMP

1. Go to cell AB5 (under the column title TEMP).

172

2. Write this formula in the cell (overwrite its contents):
=IF(AND(G5="BANK", B5 =< #12/31/2003#,0,H5).

This formula will put zero into cell AB5 if the account name in cell G5 is "BANK A", otherwise the original amount will remain. (Replace BANK A with your actual bank account name in the formula and #12/31/2003# with your actual previous year and date.)

3. Copy this formula down the column until the last row of the data area. (To copy down fast, double-click the handle at the bottom-right of the cell AB5). This will put zero into all cells in column AB (starting from row 5) if the corresponding account name in column ACCOUNT-DEBIT is "BANK A", and the date in column B is earlier than or equal to 12/31/2003. Otherwise the original amount will remain.

4. Next, we want to copy the values in column AB into the column RM-DEBIT. To do this, highlight all the cells in column AB (from row 5 to the last row of the data area). (To highlight fast, hold down the Ctrl key and press Shift. While still holding down both keys, press the downward arrow key.) Then click menu Edit/Copy. Go to cell H5. Click menu Edit/Paste Special to open the Paste Special dialog box. Select Values. (This means we want to paste values, not formulas). Click OK. The new amounts should have replaced the figures in column RM-DEBIT.

5. Next, we want to do the same on the column RM-CREDIT. The process is similar, only change some parameters in the formula. Go to cell AB5. Write this formula:
=IF(AND(15="BANK", B5=< #12/31/2003#,0,J5)

This formula will put zero into cell AB5 if the account name in cell 15 is BANK A, otherwise the original amount will remain. (Replace "BANK A" with your actual bank name in the formula and #12/31/2003# with your actual previous year and date.)

Copy this formula down the column until the last row of the data area. (To copy down fast, double-click the handle at the bottom-right of the cell AB5). This will put zero into the cells in column AB (starting from row 5) if the corresponding accounts name in column ACCOUNT CREDIT is "BANK A", and the date in column DATE is earlier than or equal to #12/31/2003#. Otherwise the original amount will remain.

173

6. Next, we want to copy the values in column AB into the column RM-CREDIT. To do this, highlight all the cells in column AB (from row 5 to the last row of the data area). (To highlight fast, hold down the Ctrl key and press Shift. While still holding down both keys, press the downward arrow key.) Then click menu Edit/Copy. Go to cell J5. Click menu Edit/Paste Special to open the Paste Special dialog box. Select Values: (This means we want to paste values, not formulas). Click OK. The new amounts should have replaced the figures in column RM-CREDIT.

10. Check that the absolute total of RM-DEBIT (in cell H1) and RM-CREDIT (in cell J1) is still the same. Otherwise, you may not have identified the amounts correctly. A review of the bank accounts may help you identify the reason.

11. Repeat the operation for each bank account.

12. If the number of bank transactions is not many, you can identify manually, rather than using formula in cell AB.