Pivot chart - Ignore zero values - help needed

[girth69]

I have a pivot chart set up that reflects data across an annual period with each plot point being a month of the year - jan - december.

The problem i have is that the line chart that i have in place has an inclining slope showing the increase in the data as the year progresses. But as i only have data up until April, the line goes back down to zero for may. How can i get it to ignore the zero value that is in my data series?? I cant just delete the formula in place as then other parts of my spreadsheet wont work. Is there any way i can just have the line reflect the data that has values to it??

I can do it on a set of data where the value just reflects zero, but i think that because i have a formula in place for the data that at the moment returns a zero value that it automatically picks it up??

please somebody help!

[Irathi]

Hello

This problem can be countered in 2 easy ways:

1. select the Axis you want to change, select format axis, change "minimum" from "auto" to "fixed" and set it to 0,0001. Your lines might still hit the floor at the end so i have another solution.

2. In order to make this work i have 2 sheets; sheet1 for raw input data added by users, not sorted in a pivot friendly style. Sheet2 that refers to sheet1, but it keeps everything sorted in columns. Normally the formula in sheet 2 would look like this =sheet1!A1, problem is that empty cells in sheet1 comes up as 0 in sheet 2, thus making my chart fall to 0 where no input has been made.

To counter this problem i added an IF, =if(sheet1!A1=0;sheet1!A1/0;sheet1!A1). The syntax reads that if the cell "sheet1!A1 = 0 = True" then divide it by 0, if "sheet1!A1 = 0 = False then sheet1!A1=A1". This formula divides every cell equal 0 by 0, thus returning you with the error "#DIV/0!" which the pivot chart will simply ignore as it is not a value, but an error.

I am not certain how this will work if you actually want 0 to be displayed in your pivot chart at certain points. In my sheet however a 0 value means that there is 0 input and it should not be included in pivot chart.

Problem solved