Gaps in Sparklines not working when there is no data

[bpjod]

I'm attempting to make a Sparkline but I'm not getting gaps in the Sparkline as I would expect.

Column A contains Sales for the Week.

Column B contains Hours Worked for the Week.

Column C contains Sales per Hour: =IFERROR(Column A/Column B,"")

The IFERROR is necessary becomes sometimes people take a vacation and work zero hours in the week and we get an error when we divide by zero.

I wish for the cells with no data (because there was an error because Hours Worked was zero) to be represented by gaps in my sparkline but currently they plot as if the value was zero. If I delete the contents of the cell, I get the sparkline looking like I want, but that isn't practical.


EDITED TO ADD: Furthermore, if I do =ISBLANK() on those cells it returns FALSE. If I copy those cells and paste the values into a new column, then do =ISBLANK() on those cells I get FALSE. The only way to get =ISBLANK() to be TRUE is to delete the value of the cell (even those that look blank to me from the Copy/Paste Values). When the cells give a value of TRUE for =ISBLANK(), that's when my sparkline looks correct. So I guess what I'm really asking is: how do I use IFERROR to give me a truly blank cell because clearly "" which is supposed to give me a null value is not equal to blank and doesn't trigger a gap in my sparkline.


How do I go about fixing this?

 

[Hui]

Have you tried: =IFERROR(Column A/Column B,NA())

Then select the Sparkline

Goto Design, Edit Data, Hidden and Empty Cells

and try the various options

 

[bpjod]

Thanks for that Hui. I was not aware of the NA() function. Quite interesting behaviour I observe depending on what the cells are filled with:


"": ISBLANK() returns FALSE; Sparklines treats the "" cells as though they are value zero regardless of Hidden & Empty Cells setting


NA(): ISBLANK() returns FALSE; Sparklines acts as though I've selected Connect Data Points with Line regardless of which setting I've picked in Hidden and Empty Cell Settings.


Delete the cell contents: ISBLANK() returns TRUE; Sparklines behave properly according to whatever is selected in Hidden and Empty Cell Settings.


I find this behaviour odd. My first choice would be to have gaps, but neither "" nor NA() allow me to do that. Plotting a zero point doesn't work as the range of data is usually between 400 & 500 and when zero points appear the rest of the sparkline essentially looks flat with a spike down for the zero because the Y-axis range gets messed up by the zeros. Therefore using "" doesn't work at all for me.


Using NA() causes the data points to connect with a line which doesn't affect the graph adversely by throwing off the Y-axis scale so that's what I'll use; even though my preference would be to have gaps.


I do hope this behaviour is something that MS looks at addressing in future versions.

 

[bpjod]

Ugh, fix one thing, break another. On a different worksheet I use the data in those columns to calculate percentiles. Now that I'm using NA() instead of "" for when there's an error, the =PERCENTILE.EXC() function is broken and returns a value of #N/A whereas it worked just fine when I used "". Any other ideas?

 

[bpjod]

For future reference, I fixed my PERCENTILE problem with a little help from Google. Solution to be found at http://www.mrexcel.com/forum/excel-questions/407917-quartile-percentile-function-range-containing-n.html


Now things are working as hoped!