IF statement in Pivot table Calculated field

[gideon]

Hi,

I want to put an IF statement in a Pivot table Calculated field. My aim is to have a formula that is different depending on the what the column is.....

EG....

TYPE is the column field...there are 3 types, "Fixed","Floating" and "OD"

MANAGER is the row field...

The calculated field that I would like to make up the data is as follows....Balance,Income and limit are also fields in the data set.

=IF(Type= "Fixed",Income/Balance,IF(Type= "Floating",Income/Balance,Income/(Limit* 0.6)))

The result of doing this is that all types have the formula....Income/(Limit* 0.6).

How do I get the data to show Income/Balance for Fixed and floating?

Thanks in advance..

 

[Juan Pablo González]

I believe you must do this within your data table, and include that field in the Pivot Table instead.

 

[gideon]

I'm not sure how to do this and then have it in a pivot table. I am calculating a Weighted Average Margin.

EG.....the data looks like this...<pre>
Manager Type income balance limit

ANDREW MCGUCKIN OD -20,597.59 -250,000.00 -5250
ANDREW MCGUCKIN OD 30,703.25 -200,000.00 -3600
ANDREW MCGUCKIN OD 75,880.79 -40,000.00 -840
ANDREW MCGUCKIN Fixed -200,000.00 200,000.00 -2980
ANDREW MCGUCKIN Fixed -200,000.00 200,000.00 -2640</pre>The formula for the fixed type should be SUM(-2980,-2640)/sum(-200000,-200000)

The formula for OD should be SUM(-5250,-3600,
-840)/60% of sum(-250,000.00,-200,000.00,-40,000.00)

So a pivot table does this through the Calculated field, but can only include one of the options...

Hope this makes sense....Thanks
This message was edited by Juan Pablo G. on 2002-09-18 21:38