excel charting - ignoring values with na()
On my current project I am playing an iteration manager role. Coming from the development kingdom I have avoided excel like the plague it rightfully is. However I am unable to avoid it any longer and I’ve been playing around with charting. I was creating a simple iteration burn up chart from a series of values. The table itself was simple:
| Date | Scope | Complete | Total Complete |
|---|---|---|---|
| Sep 1 | 10 | 2 | 5 |
| Sep 2 | 12 | 3 | 5 |
| Sep 3 | 12 |
My challenge was the red highlighted values. I wanted to make my table smart so it summed the total complete based on the days progress. That was simple (by summing that days plus the previous day’s total complete value). I used the if(condition, value if true, value if false) function to set the value to blank (”") when there was no value present:
=IF(ISBLANK(C4),"",C4+D3)
This worked visually but then when I charted the values the blanks were displayed as zeros which considering the values were at the tail end of the iteration looked rather silly on top of being inaccurate. It’s not often when a team undoes all the work it has done the last 2 days of a 5 day iteration!
A bit of searching yielded the charting solution. I found this excel newsletter which described the na() function. All na() does is set the value of the cell to “no value available” which the excel charting is clever enough to ignore. My table now looks like this:
| Date | Scope | Complete | Total Complete |
|---|---|---|---|
| Sep 1 | 10 | 2 | 5 |
| Sep 2 | 12 | 3 | 5 |
| Sep 3 | 12 | #N/A |
And the Total Complete function now looks like this:
=IF(ISBLANK(C4),NA(),C4+D3)
This is not as preferable as a null which could display as a blank but for the sake of the burn up chart it will have to do. I have no doubt this is hardly worthy of the designation “excel tip” and is something any seasoned excel veteran is familiar with but I found difficult and also found the solution (buried away on the Windmill Software Newsletter #62 from October 2003) difficult to find as well.
As an aside I was reading some excel forum and some guy signed his posts with the phrase “Keep Excelling”. After you get over your involuntary shudder I suggest you all take a look at OpenOffice calc which I have had great success with.