Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

conditional statements in pivot table calculated items

Posted on 2014-01-10
1
Medium Priority
?
729 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
[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 53

Accepted Solution

by:
Rgonzo1971 earned 1500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
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…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

609 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