Chart Question

[mrkyle5845]

Hello all!

This is my first time posting in the forums here so I apologize if my question isn't quite right or not clear.

I have a spreadsheet I'm setting up that involves a few charts. The chart represents performance over an entire quarter, so the chart needs to only graph those cells which have values and not chart them as zeros (which is the problem I'm trying to solve).

I've been able to avoid that with two out of the three metrics I'm charting by using this formula: "=IF((VLOOKUP($A$64,$A$3:$IP$60,COLUMN(E1),FALSE)=0),#N/A,VLOOKUP($A$64,$A$3:$IP$60,COLUMN(E1),FALSE))"

However, the third metric needs a formula to calculate it instead of it being entered manually. In the interest of keeping the spreadsheet clean looking, I have it set to show blank if it's not able to calculate that weeks metric with this: "=IF(ISBLANK(E17),"",E13/E17)".

Unfortunately this results in the chart graphing each of the blank cells as 0. I assume this is because it doesn't read "" as 0. I tried to fix this by also testing for a blank and if it's blank, showing an error so that the chart won't map it with this: "=IF((VLOOKUP($A$64,$A$3:$IP$60,COLUMN(CO1),FALSE)=0),#N/A,IF(ISBLANK(VLOOKUP($A$64,$A$3:$IP$60,COLUMN(CO1),FALSE)),#N/A,VLOOKUP($A$64,$A$3:$IP$60,COLUMN(CO1),FALSE)))".

This isn't working either and it's still showing the cell as blank, but graphs it as a zero.

I guess it all boils down to how do I get the IF statement to come back as true when testing a cell that's blank as a result of the formula "=IF(ISBLANK(E17),"",E13/E17)"? If I can figure that out I can just test for it and kick back an error if it's blank.

I hope that was clear enough! I appreciate any help anyone can give!

 

[Steve=True]

Hi Mrkyle,

I would recommend that you not change your formula, but instead, use an Excel Table for your chart to not chart N/A or Zero or Blanks.

Check out this post. Will work for other chart types, not just pie charts:

how-to-easily-hide-zero-and-blank-values-from-an-excel-pie-chart-legend

Hope it helps.

Steve=True