Pivot Table Not Displaying Negative Time Values

[fadetograham]

Hi everyone

I have a pivot that needs to show a planned journey time, actual journey time and then the variance. The problem I have is that the calculated field in the pivot won't display the negative values in the variance where the journey took less time than planned. It just displays #######, which invalidates the subtotals too.

Outside of the pivot table I can get the figures I need by using the below formula where E4 is actual time and D4 is planned time. When I adjust it for use in a calculated field (swapping e4 for 'actual' etc) it doesn't work. =IF(e4-d4<0, "-"&text(abs(e4-d4),"h:mm"), e4-d4)

Is there an adjustment to the basic =actual-planned formula in the calculated field which will get it to show the correct figure?

Thanks in advance for any help you can give.
Graham

 

[RoryA]

You could use:

=(Planned-Actual)*IF(Actual>Planned,-1,1)

 

[fadetograham]

Hi Rory

Thanks for the quick response but unfortunately that doesn't seem to have worked. It's zero'd all negative figures and ###### all the positive figures.

Regards
Graham

 

[RoryA]

I don't see how that could possibly zero anything since it multiplies by either 1 or -1.

However planned and actual should be transposed in both parts of the formula!

 

[fadetograham]

My apologies, it's showing zero where there wasn't a difference between plan and actual but both positive and negative values are showing as #######.

The formula bar does shows a figure but it's showing as a decimal and no amount of column widening shows it in the pivot.

 

[RoryA]

What formula are you using currently in the calculated field?

 

[fadetograham]

I've just chopped it down to the bare minimum of =Actual-Planned as that at least gives me the positive values.

 

[RoryA]

It should be:

=(Actual-Planned)*IF(Planned>Actual,-1,1)

 

[fadetograham]

Yeah I changed it to that but it ####### all values other than those without a difference so I changed to back to the just actual-plan.

Your formula does work fine outside of a pivot although it does display everything as a positive value, which will give a false total when looking at multiple journeys. As soon as I place the formula in a calculated field and transpose for the pivot it doesn't display anything other than 0:00 for where there is no difference between plan and actual.

I find it bizarre that formulas in pivot tables behave differently to those outside and such a simple metric is so hard to get from in a pivot.

Thanks for your time and help trying to sort this, it's much appreciated.

 

[RoryA]

I tested that in a calculated field and it worked as expected. Can you provide a workbook where it doesn't?