+ Reply to Thread
Results 1 to 2 of 2

Is it possible to have a calculation in a PT that resembles a complex formula calc?

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Florida, USA
    MS-Off Ver
    Excel 2007
    Posts
    48

    Is it possible to have a calculation in a PT that resembles a complex formula calc?

    Hey y'all,

    Question: Is it possible to add a calculated field (or some sort) that resembles a complex formula in a cell?

    Example: I have a pivot table that lists a person's name, how many compliant items they had, how many non compliant items and the grand total (sum(compliant & non compliant)).
    What I want to be able to add to the pivot table is a field that has a complex calculation.
    What I have:

    Count of C or NC Column Labels
    Row Labels Compliant Non Compliant Grand Total
    Joe Smith 9 24 33

    What I would like to have:

    Count of C or NC Column Labels
    Row Labels Compliant Non Compliant Grand Total Compliance < 50%
    Joe Smith 9 24 33 38%

    Formula to get the 38%: =IFERROR(IF(Compliant/Non Compliant=0,"",IF(Compliant/Non Compliant<.5,Compliant/Non Compliant,"")),"")

    I've attached a sample file to help.
    Don
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Is it possible to have a calculation in a PT that resembles a complex formula calc?

    Hi,

    You might do this
    1. Add new columns to the source data titled 'Compliant' and Non compliant.
    2. For the Compliant the formula is simply =IF(Table1[[#This Row],[C or NC]]="Compliant",1,0)
    3. For the non-compliant, the formula is =1-Table1[[#This Row],[Compliant]]
    4. Use these two new columns as data fields in your pivot table instead of the [C or NC] field as a column header and data field.
    5. Add a calculated field to the pivot table using = IF(Compliant/'Non compliant'<0.5,Compliant/'Non compliant',"")
    6. In the pivot table options check the option to show error values as blank
    Attached Files Attached Files
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2016, 11:25 AM
  2. Replies: 11
    Last Post: 04-08-2013, 12:11 AM
  3. Excel 2007 : Complex calculation help
    By Betadog in forum Excel General
    Replies: 5
    Last Post: 04-20-2011, 05:06 PM
  4. Complex CSA Calculation
    By NatashaBatsford in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-28-2009, 10:42 AM
  5. Complex If Calculation Help
    By Marcus Gee in forum Excel General
    Replies: 3
    Last Post: 02-26-2008, 07:00 AM
  6. complex calculation
    By wislndixie in forum Excel General
    Replies: 1
    Last Post: 12-06-2007, 03:13 PM
  7. Replies: 0
    Last Post: 07-25-2006, 04:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1