Sum in a pivot table of blank cells returns zero and not blank ... bad in pivot chart

Hello

We use a pivot chart to display the forecast with the achieved sells.
Obviously for the future quarters there is no achieved sells yet and therefore the cells in the table are empty.
But the "SUM" function in the pivot table convert the sum of blank cells into a zero.
The bad is when you plot it on a chart, actually we would like to don't have any value on the chart for the achieved sells for the future quarters but just the forecast.
Until now I couldn't find any solution and just to prevent it, we don't look for a solution of the type: "Copy paste your date in another spread sheet, correct the data and create a plain excel chart", this isn't a solution, this is more work every time that we want to check this chart. The solution must work directly in the excel table, pivot table and pivot chart.

SumOfBlanksToZero.png

I would hardcode your pivot with sumifs formulas and turn 0 into #n/a

Thank for the reply, this was exactly what I didn't want as mentioned in the original post:

Originally Posted by spamme

"Copy paste your date in another spread sheet, correct the data and create a plain excel chart"

Rory's solution is probably better however

"Copy paste your date in another spread sheet, correct the data and create a plain excel chart"

1) my solution does not copy or paste, it takes the data from the table and analyses it.
2) The chart would just redraw itsself once it got refreshed.
3) If future quarters were required , it coul dhave the ranges change dynamically to accomodate this, although quarters without years would make this impossible.

So exactly what was wrong with the solution, I do not see that it is exactly what you didn't want?


I think that your reply is a little rude!