Solved

Pivot table: issues with calculated field

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now