Solved

Posted on 2011-05-05

If i have a formula on a pivot table that calculates some results no problem.

I want to add an IF to the formula so that if a field is a value it uses the formula but if it's zero i need it to drop in a value from a cell outside the pivot table.

For example if i had the formula to work out the cost we are paying for advertising, the formula would be

= Cost * Number of Enquiries

If i have a seperately worked out (outside the pivot) cost for the Internet in Cell M4 can i use this in the formula? i.e.

=IF (Source <> "Internet", Cost * Number of Enquiries, Value in Cell M4)

Is this possible?

I want to add an IF to the formula so that if a field is a value it uses the formula but if it's zero i need it to drop in a value from a cell outside the pivot table.

For example if i had the formula to work out the cost we are paying for advertising, the formula would be

= Cost * Number of Enquiries

If i have a seperately worked out (outside the pivot) cost for the Internet in Cell M4 can i use this in the formula? i.e.

=IF (Source <> "Internet", Cost * Number of Enquiries, Value in Cell M4)

Is this possible?

8 Comments

=IF(D2<>"Internet", GETPIVOTDATA("Marks",$B$11

See an example is attached. See the formulas in Column E with heading "Formula column".

Example.xls

If you look at the attached sample, I have the cost for all other Sources from the data. Internet is a bit more complicated and is based on a monthly fee plus 1-5 enquires at so much, 6-10 at another rate etc. (i've not included this formula just the total in M4)

The Total Calculation is - =IF(internet= 0,Cost, "Value in M4")

I need it to put the Value in M4 in if "Internet?" equals 1

Sample.xls

It would be much easier of course to change your source data.

Sorry but I didn't quite understand your usecase.

What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost.

Now use this column in the pivot table.

See attached.

Does this help?

Sample1.xls

I was thinking along those lines but really complicated for my project as the data is externally generated.

You can manipulate the data in the pivottable calculation using "IF" statement (see ScreenCast) :

=IF(internet= 0,Cost, 100)

would put the value of 100 in if Internet was not = 0

I need to know if the 100 can be replaced by the value in a cell outside the table.

Thanks

carlspywell-456436.flv

"What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost."

My solution is different because:

If you read my response, your solution added a column to the data source! I cannot add a column to the data source as the data source is external and cannot be manipulated from the spreadsheet.

In the attached sample, I used the data from the cells "Source", "Enquiries" & "Cost" to create a new column "Solution" outside the table, and used the formula "=IF(A12="Internet",M4,C12

If Souce = Internet it uses M4 otherwise it Calculates the result by multiplying the Enquiries by the Cost.

Thanks for you input.

Carl

Sample.xls

Title | # Comments | Views | Activity |
---|---|---|---|

Why am I getting "No Duplicate Values Found" on this spreadsheet when there very clearly ARE duplicates? | 12 | 48 | |

how to add and add vat in excel | 9 | 26 | |

Copy Value of cell in formula | 1 | 25 | |

Adding to a VBA? | 6 | 30 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**16** Experts available now in Live!