I have two columns of data in a pivot table but one of the columns is only partially populated. I've been able to use 'Calculated Item' to add a third column that will calculate the difference between both columns, but I'd like an IF statement that ensures the 'difference' column remains blank when the partially populated column is blank.
I've tested the logic outside of the pivot table in standard cells and this works fine. When applying the same IF logic to a formula in 'Calculated Item' however, it simply defaults to the 'else' and calculates difference no matter what. I wondered if anybody had any advice for me for this?
21 Answer
The problem is solved by building upon Alex M's advice.
=IF(CategoryA-CategoryB=CategoryA,"n/a",CategoryA-CategoryB) returned #VALUE! error using a text string when the IF condition was met, so selecting the pivot table option 'For error values show: n/a' functions as a crude workaround that achieves the desired result.
