Excel Graph Help Needed

[graviz]

Graph Setup:
-Bar Graph
-4 Dates
-4 Values
-Y Axis Scale - Set to "Auto"

Source Data:
C6:F7 (8 Cells Total / 4 Dates / 4 Values Below)
The cells change based upon a control box, so the values changed based on the selection.

When there is a blank value under one of the dates it makes the min axis 0 which skews the look of the graph. Is there a way to make the graph's auto Y axis ignore blank values when it calculates the axis? I would still like to have the date as a placeholder if possible. Here is my current formula for one of my values if it would help:

=IF(VLOOKUP(Dashboard!$N$34,CSAT!A:E,2,0)="","",VLOOKUP(Dashboard!$N$34,CSAT!A:E,2,0))

Any ideas?

 

[Gerald Higgins]

How about using a separate row for the dates, to drive your chart ?

Like this

=if(C6="",min($C6:$F6),C6)

Copy this 3 times - let's say you put it in the range C8:F8.
Then use THAT range as the source for your chart.

 

[Jon Peltier]

A bar chart should always include zero along the value axis. The values in a bar chart are read by the length of the bars. Even though we might notice that the axis doesn't start at zero, our eyes and brains still judge the length of the bars, not the position of the end of the bars. See Bar Chart Value Axis Scale Must Include Zero.

If the category axis contains dates, use a line chart. The position of the markers encodes the data, so you don't need to include zero in the axis scale.

In an appropriate type chart, you can get what you want using

=IF(C6="",NA(),C6)

This puts an ugly #N/A error in the cell, but a line or XY chart will not plot it, and no chart will consider it when calculating an axis scale.