# IF statement in Pivot table Calculated field

#### gideon

##### Board Regular
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?

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I believe you must do this within your data table, and include that field in the Pivot Table instead.

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

Replies
6
Views
512
Replies
1
Views
181
Replies
8
Views
340
Replies
17
Views
577
Replies
1
Views
147

1,221,383
Messages
6,159,535
Members
451,571
Latest member
Qwissy

### 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.

### Which adblocker are you using?

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

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