Can a pivot table calculated field's formula reference the row value of another field in the same pivot table?

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.

Category:Office Views:1 Time:2014-04-18

Related post

  • how to add count formula in HLOOKUP ( row index number place) 2014-11-09

    how to add count formula in HLOOKUP ( row index number place) --------------Solutions------------- Given the folloing table starting in A1 aaa bbb ccc 1 2 3 4 5 6 =HLOOKUP("AAA",A1:C3,2) - will provide a 1 =HLOOKUP("AAA",A1:C3,INT(ROW(A1)/2)+3) - wil

  • How to use a VBA custom function in a pivot table calculated field? (Excel 2010) 2015-03-05

    Although I suspect the answer is that I can't. :-( I'd like to use a custom function to calculate a score based on two other fields in my data. The two fields might be, for instance, an identifier for a question (Q1, Q2, Q3, ...) and another for the

  • Pivot table - Calculated field 2012-01-23

    Is it possible to create a pivot table that sums all fees and then create a calculated field that sums the fees based on a another fields requirements? For example: =IF('Type'="Associations",Sum(Revenue)) - I can't seem to get the 2 to work in the sa

  • Pivot Table Calculated Field results in error when data updates 2013-03-17

    Has anyone any suggestion as to why a pivot table returns the Name error in calculated fields after additional columns are added to a database. To recreate the problem: a) Create a simple database - Field names: Product, Quantity and Price; b) Insert

  • Excel 2013 - pivot table calculated fields - referring to other dimensions in calc 2014-08-15

    I'm struggling to find much useful documentation on Excel 2013 pivot table calculated fields. My pivot table is fairly simple. I have a 'Company' dimensions as rows (e.g. 'A', 'B' etc) and calendar weeks as columns ('1' --> '52'). The value in the

  • Pivot Table Calculated Column 2012-01-26

    I have two columns in a Pivot Table and would like a third column that shows the result of column 2 divided by column 1. Specifically, column 2 is a sum of items used. Column 1 is a count of the number of dates on which the items were used. So what I

  • Pivot Table Calculation - Compare Two Columns (%) 2013-10-07

    I have a pivot table where I am counting the number of order lines and then the number of back orders in two columns. I want to calculate a third column that shows the % of back orders. (Back Orders/Order Lines) Order lines is a count of part numbers

  • Pivot table drill down... How to add additional fact fields 2014-05-18

    When i double click on a value in a pivot table, i get the facts plus associated dimension values for each fact row. How can i add additional fields from the same fact row to the output? So, if my fact table currently includes Black, collard, button-

  • Creating calculated fields in queries that do not have a coresponding field in a table 2014-08-23

    Hi, I want to create a calculated field in a query like (A-B)/A. I then want this data to be displayed in a field. I know I can create it in the table first, then put it in the query. I input the question in help and got an immense list of links. It

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.166 (s). 11 q(s)