Solved

Pivot table: issues with calculated field

Posted on 2014-11-07
1
171 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
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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