X - Axis, Cant get excel to use the correct scale.

[raven_squire]

Hello, I am making a mortgage calculator that you can use to compare two different scenarios and have the results displayed on a graph. The problem is that Excel is plotting the data based on the location of the data for the x axis and not the value in the cell.

I have a balance in one column and a date in the other for both scenarios. The frequency and amount of the repayments will change the length of the loan and generate more data accordingly. To do this I have used a formula similar to the following if(a1=" "," ",formula) a1 is set to be " " if the balance is below 0. I have 2500 cells set up with these formulas so that all reasonable scenarios should reach zero before hitting the bottom of the table.

I am using Excel 2010 and have used scatter plots as I believe that they are appropriate if the data on the x axis is to be interpolated or set to scale. The cells in the date columns have been set to dates so that excel should recognise them as dates and plot the balances accordingly.

I hope that I have an accurately described the problem for you (is there any way that I can upload the spread sheet and you could see the issue for yourself?).

Many thanks for your help in advance.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

In the example you gave using this formula, is the X-Axis date range in Column A?
if(a1=" "," ",formula)

If so, the problem is that by mixing spaces " ", or even formulas that return null strings "" in with the dates, Excel considers these "Text" Labels instead of Date values.

Try returning #N/A values instead of text, like this:
=IF(A1=" ",NA(),formula)

 

[raven_squire]

Hello JS411,

Thanks for your help, I have implemented your advice and it worked as the graphs are plotting correctly. It does however bring up two other questions.

1) Now that the graph is plotting as dates and not cell references the range of the axis is out. It is displaying from 0/01/1900 to 8/04/2064. The range that I am interested in is from the first cell being =Today() or 15/01/2013 to the end of the mortgage or first cell with #N/A as its value

2)I think I might need to do some research on the other question as I am sure that it would be answered elsewhere. "How do I automatically change the text colour to white in a cell containing #N/A"

Thanks again
Raven_squire

 

[Jerry Sullivan]

Glad that helped for the scale.

1) If you're using an XY (Scatter Chart), you should be able to change the Minimum and Maximum X-Axis Value under:
Format Axis > Axis Options

2) You can do that through conditional formatting. If you are going to do that, a better option than changing the font color or background color is to change the number format.

Create a CF rule that says when this formula is true: =ISNA(A1) then use Custom Number format:
Format Tab>Cells>Format Cells>Number category Custom
in the "Type" textbox, enter ;;;
then click OK.

Your first question implies you have at least one value in your X-Axis range that evaluates to 0 (which will display as 0/01/1900).
You might want to replace that with =NA() so that your automatic X-Axis range is more reasonably sized.

 

[Jerry Sullivan]

Oops, I should have tested my Custom Number format suggestion before posting it.

Using a Custom Number format of ;;; will "hide" Negative numbers, Positive Numbers and Text. Apparently it won't change the number formatting of Cells with Error Values such as #N/A.

You could do the approach you had in mind to change the font color to match the background.

If you are trying to clean this up for User Viewing a better option would be to have a display range table that "mirrors" the chart data.
You can use a formula like
=IFERROR(A1,"")
...to clean up the data for the user table.

 

[raven_squire]

Hello JS411,

Thanks this worked again, I had originally not wanted to set the ranges manually as I had intended to use an integer of years rather than a date. However setting them manually works a treat and saves me from having to get the automatic range working.

I think that your idea of having another table for user viewing is a great one and I will give it a try. That should hide all manner of cells that the viewer need not be concerned with.

Many thanks
Raven_Squire