Thursday, May 05, 2005

Dynamic line graphs that work with zeros in Excel

I make a lot of graphs for financial reporting for clients. In most cases these are made in database type files that have monthly data imported from a large accounting system. In most cases I need to produce line graphs that will take the whole years results, but only show up to the current month.
The main problem with this is that if you have a line graph for 12 periods, but you only have values in the first 3 months your line will go up and down for 3 months and then for the remainder of the year go down to the x axis and stay there. See figure 1 for an example.

The best way I have discovered to resolve this is to use #N/A errors deliberately.
In most cases the file will have a current month selected. I use this for report headers etc. It is also used for the graphing.
Using a formula similar to =IF(monthdate>currentdate,#N/A,data) I can generate #N/A errors deliberately for the months where I have no data.
#N/A never shows up in a graph. Thus we have created a line graphs that can be dynamically updated. See figure 2.


Posted by: Jethro | 11:07 PM |