I have a pivot table that contains multiple values, 0 to 10, I need to do an NPS calculation on this data, so sum all 9's+10's minus all (0+1+2+3+4+5+6's) divided by the total.
=((SUM(GETPIVOTDATA("NPS Score",$A$4,"NPS Score",{9,10})))-(SUM(GETPIVOTDATA("NPS Score",$A$4,"NPS Score",{0,1,2,3,4,5,6}))))/GETPIVOTDATA("NPS Score",$A$4)
I can do this when all the references exist, however I need to refresh the pivot weekly, so not all the values will appear every week. Is there a way for my formula to work if the reference doesn't work?
Solved by S. J. in 11 mins