Solved

Pivot table: issues with calculated field

Posted on 2014-11-07
1
175 Views
Last Modified: 2014-11-21
I am getting the dreaded #VALUE! error in my pivot table when using the calculated field: =IFERROR(IF(OR(PATH=1,PATH=2),COMPL_PKG/DEALS, ""),"")

Can you help identify what the error is and perhaps provide a solution. *Pivot table is attached below.

FYI, I only want the pivot table to display percentages for only Path 1 and 2 and NOT include counts of Path 3 in the percentages.

Thank you.
Path-EXa.xlsx
0
Comment
Question by:saved4use
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40428918
I created a calculated item called "Path12" which combines totals for just those two path values.  Then I added a calculated field called "CompPct" with this formula:
=IF(DEALS=0,0,COMPL_PKG/DEALS)

I've modified your PivotTable to show the raw counts for DEALS, COMPL_PKG and the calculated percentages based on the two.  If you hide the three individual path values of 1,2,3 from the columns (using the filter next to "Column Labels"), you'll see just the counts and percentages for Path 1 and 2 totals only.

Regards,
-Glenn
EE-Path-EXa.xlsx
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

732 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question