Microsoft KB Archive/29822

Multiplan: Accumulating by Account Code PSS ID Number: Q29822 Article last modified on 06-04-1990 PSS database name: D_MPlan

1.x 2.x 3.x 4.00 4.01 4.20

MS-DOS

Summary:

More Information:

To accumulate totals by account code without using a separate column for each code, do the following:

  Set up an entry area with a column for codes (named “CODES”) and a column for amounts (named “AMOUNTS”). For calculation, set manual calculation, iteration on, and a completion test that says “=ITERCNT=COUNT(CODES)”. Note: Do not include the column titles in the names you define for codes and accounts; if you do, #VALUE! errors will appear in your account totals. Do not use this method if you are using iteration for other formulas in the worksheet.   In the summary section, list all account codes down one column.   In the cell to the immediate right of the first account code, enter the following formula: =IF(ISNA(ITERCNT),0,IF(INDEX(CODES,ITERCNT)=RC[-1], RC+INDEX(AMOUNTS,ITERCNT),RC)) Copy this formula to cover all accounts and calculate when necessary. This formula will accumulate the amounts for all occurrences of an account number, as seen below. 

More Information:

The following is the completion test, which will display a value of #TRUE when the calculation is complete:

1 2 3 4 5

Entry           Summary        Completion Codes Amounts Code Total Test 1 100 1 300 =ITERCNT=COUNT(CODES) 2 100 2 500 3 100 3 100 2 100 4 100 4 100 5 100 2 100 6 100 2 100 7 200 5 100 8 0 7 100 9 100 1 100 10 100 2 100 7 100 6 100 9 100 10 100 1 100

Copyright Microsoft Corporation 1990.