I’ve found a rather obnoxious bug in the version of Excel 2013 that comes with Office 365.
As you know, I’m a big fan of doing stuff with charts. One of my favorite things is to make a dynamic series that disappears based on some user functionality like in the animation below.
So far so good. Now look what happens when I change the Y-axis scale to a much larger range. Below, I’ve given the Y-axis a range of [0, 10,000]. Now see what happens when I plot a series of –1’s.
That’s not right. I shouldn’t be seeing the line! And if I change the values to plot to –10, I still see the line…
But if I change them to –100, the line disappears…that is, until I choose a larger scale for the Y-Axis.
For good measure, I tried these three exact same scenarios in Office 2010 and had no issues. The line disappeared as expected.
I used Microsoft’s feedback form for Office 365 and let them know what I found. However, I have feeling the feedback form has the same effectiveness as writing your congressmen. Oh well.
Jon’s comment made me think I should include a way to get around this bug. He mentioned using NA(), which some might argue I should be using anyway. Here’s how to do it. Below, I’ve added an IF formula between the checkbox result and the values I want to display.
If the checkbox is checked, the IF function will return a 1 which is then multiplied by the values across the top making the line reappear. If not, it will become an #N/A which will make the rest of the values become #N/A in turn.
Here’s a challenge: can anyone thank of a way to do this without using IF?