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
Solved

conditional statements in pivot table calculated items

Posted on 2014-01-10
1
686 Views
Last Modified: 2014-01-22
the attached spreadsheet includes a pivot table with calculated items, difference between Critical and TOA.  If both the Critical and TOA are blank, I don't want to take the difference, this way the rows won't be created.  When I minimize the rows (column A) by clicking the minus (-) sign, the sheet recalculates the formulas.  It's not too bad on this sample sheet, but takes almost 5 minutes on my complete sheet.  Within the calculated item I tried, if(and(Crititcal <>"",TOA <>""),Critical '-'TOA,"").  However it didn't work and I'm wondering if I'm able to use an If statement within the calculated item?  Is it possible to write a macro or VBA to collapse the rows with no data?
ColumnDifferenceSamplev2.xlsx
0
Comment
Question by:jvantassel1
1 Comment
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39773168
Hi

You could use a filter in your rows to hide Rows with Zero

In Your example click A7 to Select the APPN Rows
Right-click / Filter / Value Filter /Sum of resource Amount "is" not 0

Regards
ColumnDifferenceSamplev3.xlsx
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

807 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