I would like to be able to use the following formula in a pivot table calculated field:

=IF('Cost Type' = "Internal Labor Cost", 'Jan Delta' / 78, 0)

However, the result is always zero. Is there any way to reference the row value of another pivot table field in the aforementioned formula?

Any assistance would be greatly appreciated. Thanks.

-------------Problems Reply------------

Hi,

No, that cannot be done. If you do so, when you click on OK, a message will pop up saying that cell or range references are not allowed within pivot table formulas

Furhermore, please avoid writing an IF() formula within a pivot table. This causes the subtotal cell values to be wrong. Instead perform the calculation in a spare column on the source data and then drag that column in the pivot table.

No, that cannot be done. If you do so, when you click on OK, a message will pop up saying that cell or range references are not allowed within pivot table formulas

Hmm, I did not receive any pop-up message. My formula simply evaluated to zero.

Furhermore, please avoid writing an IF() formula within a pivot table. This causes the subtotal cell values to be wrong. Instead perform the calculation in a spare column on the source data and then drag that column in the pivot table.

I was not aware that using an IF() formula within a pivot table causes sub-total cell values to be wrong. While I can certainly perform the calculation in a spare column within the source data, I will need to add 14 more columns (Jan - Dec, YTD, and Total Year). There has to be a more efficient way to accomplish this within the pivot table without having to add 14 more columns to my source data.

Thanks for your help. I sincerely appreciate it.

Hi,

Please upload the file on some online hard drive and then provide a link here.

I would be happy to upload my file. However, it consists entirely of company confidential data. Consequently (and regrettably), I cannot uplaod it.

Thanks for your generous offer.