2
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?
NickL
Posted 2019-04-05T14:34:01.693
Reputation: 31
Welcome to Super User! Cool question. Your test is failing because your CategoryB field isn't actually null. You can get around the difficulty of null vs blank vs 0 etc by writing the condition differently. Try
=IF(CategoryA-CategoryB=CategoryA,"n/a",CategoryA-CategoryB)and that'll work. (You could also figure out what the correct test against CategoryB would be, if you prefer a less hacky solution) – Alex M – 2019-04-05T16:27:36.800Interestingly, the solution provided works only if I replace n/a with a numerical string - text returns #VALUE! instead. I assumed this may have been because of n/a being seen as n divided by a but trying any text string at all returns the issue, but any numerical string does not. I wonder if this is a limitation of 'calculated item'?
I used the ISBLANK function and it appears you are indeed correct that the CategoryB field isn't actually blank - now I just need to work out the correct test. – NickL – 2019-04-08T08:26:10.900