IF statement in Pivot table Calculated field

gideon

Board Regular
Joined
Mar 18, 2002
Messages
78
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..
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

gideon

Board Regular
Joined
Mar 18, 2002
Messages
78
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
 

Forum statistics

Threads
1,144,208
Messages
5,723,022
Members
422,476
Latest member
beck85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top