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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,181,063
Messages
5,927,903
Members
436,575
Latest member
Tiger750

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