PivotReport situation

[wk]

Hi,
I have data in the following manner:

investment buy_date principle mature_date final_amount
invest1 5/8/1980 1000 5/8/1987 1590
invest1 8/11/1981 2000 8/11/1987 3180
invest1 2/2/1981 1500 2/2/1988 2385
invest1 4/23/1982 4000 4/23/1988 8060
invest1 9/30/1983 3000 9/30/1989 6045
invest1 3/10/1984 3500 3/21/1990 7053


Can i create a pivot table reort to show me the total principle AND the
total final_amount every year?
e.g. 1980: Total principle will be sum of all principle with buy_date
of 1980, and Total Final_amount will be sum of all final_amount with
mature_date of 1980


Thanks in anticipation.

 

[Chris Madison - Almeda University]

wk,
You can create calculated fields. From the pivot-table toolbar:
Pivottable>>formulas>>calculated field.

HTH
-Chris

 

[Debra Dalgleish]

Instead of a pivot table, you could use a SUMPRODUCT formula to
calculate the totals.

For example, with your sample data in cells A1:E7 --

Type 1980 in cell H1.
In cell G2, type: Principal
In cell G3, type: Final Amt
In cell H2, type: =SUMPRODUCT((YEAR($B$2:$B$7)=$H$1)*($C$2:$C$7))
Copy the formula down to cell H3.