#NA Trick & Flexible X-Scale

The file below demonstrates how to make flexible graphs using the NA() method. This is a very simple way to make the x-scale flexible when you have a time series in the x-scale. To apply this method you substitute the #N/A for the date on the x-scale and you make sure that the x-scale is classified as a date axis. This can be created with a template that is saved in the file and is very good for presenting various statistical analyses. The file demonstrates how to gather data for the graphs with the INDEX, MATCH and INDIRECT function along with using TRUE/FALSE switches to limit the graph to selected items. It also includes use of the OFFSET function in order to change the number of items that appear on the x-axis. The bubble graphs are created using the macros that are included in the bubble file example above and in a video.

You could use an IF(CFADS,DATE,NA()) which would give you the DATE when the CFADS is positive and would give you the #NA when the CFADS is zero.  An example of this kind of formula is shown in the excerpt below that is demonstrated on row 145.  The CFADS and Debt Service graph that results is also shown from the ALT, F1.  You can also see that sculpting has not been used and there is a funny repayment at the end.  Further, you can see there is a grace period at the beginning.

The screenshot below illustrates what may happen after you select the area and make a graph.  It is a big deal to format the x-axis row with SHIFT, CNTL 3 (CNTL, #).  Then you can change the chart type to an area graph. If there are still NA’s, then you should right click on the x-axis and change the chart type to a date axis.

Using the NA trick with Annual Graphs

When you use the NA trick with annual data you will probably have to select the x-axis and then change the x-axis type to a date axis. A problem that can arise is that if there is a big or negative number that is part of the series and is not graphed, the y-axis will not adjust properly.  To fix this, you can put the IF and the NA() into the series data as well as the x-axis. The screenshot below demonstrates this issue.

Dynamic graphs with #NA.xlsm

Spinner Box to Change the Scale on Graphs

The simple video below shows how to put a spinner box on a graph to allow you change the scale on a graph. All you do is to start recording a macro and then go to the graph and change the scale. After you record the macro you can enter the minimum scale somewhere in the file and name the range. Then just go back to your macro and replace the number you entered for the minimum amount with the range name using RANGE(“min”). Of course, you need to name the range.