Milestone Dashboard: Problems with #N/A values

[DashboardNovice]

I am using the following article as a guide:
http://chandoo.org/wp/2009/07/09/project-milestones-in-timeline/


I'm working on the 2nd tab in the attached spreadsheet. See columns T thru AC. Actual milestones completed are in red. Future milestones yet to be completed are in green.

First I plotted the actuals. Everything seemed to be fine. See the 2nd graph. Notice how nothing was graphed beyond Milestone 7. This is exactly what I want.

But then I ran into problems when I added the data from the Future Milestone Estimates in green. The result appears in the first graph.

1. I don't want to show the green labels in the black rectangle. I don't want to show the red labels in the black rectangle either. Is there a way to do this?

2. I'm still trying to understand when Excel ignores #N/A errors and when it doesn't.

3. Notice the formula in the blue cells in AC7:AC13. These should be evaluating to TRUE. But instead of returning a blank, I get an #N/A error. Why is this?

If I were to guess what is happening I would say this. After I copy and paste the height column of the actuals, I change the X axis labels to the Milestones in column Y. So far, so good. Then I copy and paste the height column of the Future Milestones. Then I also change the X axis labels to the Milestones, this time the Milestones in column AB. At this point is appears as if the latest X axis labels, supersedes the previous ones. This seems to be the basis of the problems I am running into.

Also, can anybody recommend any articles I can read about how changing the X axis labels affects the graph?

 

[NARAYANK991]

Hi ,

The basic problem on the #N/A is that in one case you are inserting the in-built Excel error function NA() ; thereafter you are comparing this value to a text string #N/A ; the two are not the same.

If you have to check whether a cell contains a #N/A error value , use the ISNA(...) function.

Narayan

 

[DashboardNovice]

Hi ,

The basic problem on the #N/A is that in one case you are inserting the in-built Excel error function NA() ; thereafter you are comparing this value to a text string #N/A ; the two are not the same.

If you have to check whether a cell contains a #N/A error value , use the ISNA(...) function.

Narayan

Thank you.

 

[DashboardNovice]

Hi ,

The basic problem on the #N/A is that in one case you are inserting the in-built Excel error function NA() ; thereafter you are comparing this value to a text string #N/A ; the two are not the same.

If you have to check whether a cell contains a #N/A error value , use the ISNA(...) function.

Narayan

I have another problem now with the same cells, but a different formula. I have this:

=IF(AA7="","",V7)

and I'm getting an #N/A error again.

 

[NARAYANK991]

Hi ,

It is related to what I mentioned in my earlier post.

The starting cell reference is T7 , which is either blank or has a manually entered date in it.

T7 is referenced in AA7 , which has the following formula :

=IF(T7<=$Y$1,NA(),T7)

Y1 is supposed to be the Current Date , or at least that is its label ; however , the date it contains is 05/21/2015 ; I cannot say whether this should be 05/21/2014.

Since T7 is less than this date , AA7 now has the error value NA() , which is displayed as #N/A.

AC7 has the formula :

=IF(AA7="","",V7)

Now , let us consider all the possibilities as far as Y1 and T7 are concerned :

1. Y1 - blank ; T7 - blank
2. Y1 - date ; T7 - blank
3. Y1 - blank ; T7 - date
4. Y1 - date ; T7 - date

If both Y1 and T7 have dates , we have two possibilities :

4.1 T7 <= Y1 , and
4.2 T7 > Y1.

The outputs in AA7 and AC7 for these cases will be :

1. AA7 = NA() ; AC7 = NA()
2. AA7 = NA() ; AC7 = NA()
3. AA7 will have the date present in T7 ; AC7 will have the value present in V7
4.1 AA7 = NA() ; AC7 = NA()
4.2 AA7 will have the date present in T7 ; AC7 will have the value present in V7

Thus , the formula in AC7 is wrong , since it is checking for AA7 = "" , whereas we can see that AA7 will never be blank. You need to rewrite the formula to :

=IF(ISNA(AA7),"",V7)

All of this is based on just the formulae , since I really do not know what you are doing otherwise ; changing the formula may or may not resolve your problem.

Narayan

 

[DashboardNovice]

Hi ,

It is related to what I mentioned in my earlier post.

The starting cell reference is T7 , which is either blank or has a manually entered date in it.

T7 is referenced in AA7 , which has the following formula :

=IF(T7<=$Y$1,NA(),T7)

Y1 is supposed to be the Current Date , or at least that is its label ; however , the date it contains is 05/21/2015 ; I cannot say whether this should be 05/21/2014.

Since T7 is less than this date , AA7 now has the error value NA() , which is displayed as #N/A.

AC7 has the formula :

=IF(AA7="","",V7)

Now , let us consider all the possibilities as far as Y1 and T7 are concerned :

1. Y1 - blank ; T7 - blank
2. Y1 - date ; T7 - blank
3. Y1 - blank ; T7 - date
4. Y1 - date ; T7 - date

If both Y1 and T7 have dates , we have two possibilities :

4.1 T7 <= Y1 , and
4.2 T7 > Y1.

The outputs in AA7 and AC7 for these cases will be :

1. AA7 = NA() ; AC7 = NA()
2. AA7 = NA() ; AC7 = NA()
3. AA7 will have the date present in T7 ; AC7 will have the value present in V7
4.1 AA7 = NA() ; AC7 = NA()
4.2 AA7 will have the date present in T7 ; AC7 will have the value present in V7

Thus , the formula in AC7 is wrong , since it is checking for AA7 = "" , whereas we can see that AA7 will never be blank. You need to rewrite the formula to :

=IF(ISNA(AA7),"",V7)

All of this is based on just the formulae , since I really do not know what you are doing otherwise ; changing the formula may or may not resolve your problem.

Narayan

Thank you. Your formula did give me what I was looking for. I'm still at a loss as to why my original formula didn't work. It was: =IF(AA7="","",V7)

In AC7 I was checking if AA7 had a blank and it did not. So because it did not, the condition would evaluate to false, which would mean that the value in cell V7 would be returned. That's what I was thinking. But I'm still not seeing how AA7 being #N/A instead of being blank doesn't evaluate to a FALSE. Because it is true to say that #N/A is not a blank. I was thinking that maybe an #N/A resulting from NA() is not the same as "#N/A" meaning a text version of #N/A. So I tried both and I still got the same error.

Do you have a website link you can send?

 

[NARAYANK991]

Hi ,

It comes down to the same thing :

1. When you enter a formula such as =NA() in a cell , the cell has in it the Excel error value #N/A.

2. When you type in a cell the characters #N/A , Excel recognizes it as the Excel error value , and puts in the same. This is exactly what happens when you type in the words true or false in a cell viz. Excel automatically recognizes them as the Excel Boolean values of TRUE and FALSE ; thus , even if you type in true in lower-case Excel will automatically enter TRUE ( in upper case ) in the cell.

3. The only way to enter #N/A as a text value is to precede it with the apostrophe or single quote character '. When you do this , Excel treats it as text , and then it is not the same as #N/A the error value.

You can try this out in 3 different cells ; use the 3 cells in an IF statement as :

=IF(NA_cell="","","Text")

With the first and second , you will get an output of #N/A ( the Excel error value ) , while with the third , you will get an output of Text.

Narayan