Annoying yet Simple chart question

[Daisy_Bumbleroot]

Im sure the answer to this is something simple....

I have an excel spreadsheet that is used to monitor progress on our job. We have an activity called a "Buffer" which is a time contingency, if you like and this gets used up and we need to moitor how much it gets used.

The progress goes up each week in steady increments in column A ie week 1 - 1.76%, week 2 - 3.52%, week 3 - 5.28%, week 4 - 7.04% etc (rounded off on my sheet of course) and this forms the Y axis.

The X Axis is the buffer consumed in column B - ie how much we have consumed of the buffer that week. week 1 - 0%, week 2 - 4%, week 3 - 4%, week 4 - 8%, so what I should get is a nice graph with buffer consumed against time.



But what happens is, because I have not filled in the buffer consumed for weeks 5, 6, 7, 8 (because it hasnt happened yet) the line on the chart just shoots down to 0 and plots to the end for weeks 5, 6, 7, etc I dont want it to do that, and Ive done it before on other graphs where I can fill the chart in week by week and it nicely plots my line.

Can anyone help me stop the line dropping down to zero just because my B column data is incomplete?



I know its something simple....

 

[Daisy_Bumbleroot]

bump? anyone? surely this is one of the easy problems on here?

 

[RoryA]

If the cells are empty, then they should not be plotted. Do they actually contain formulas? If so, you want the formulas to return NA() when there is no data.

 

[Daisy_Bumbleroot]

If the cells are empty, then they should not be plotted. Do they actually contain formulas? If so, you want the formulas to return NA() when there is no data.

Yes they contain formulas :D I was just looking on another thread when I saw the NA() thing. Thank you.

The chart no longer plots those items, but now I have an ugly #N/A in my column. If i go to Conditional Formatting and i put 'If Cell Value = "#N/A" make the font and background the same colour, but this doesnt work. How do I hide the #N/A so it looks blank please?

(This isnt for me, its for someone else to fill in and they will not like the look of the #N/A, I wouldnt care cos i know what it is but to them it could look like an error)

Thanks

 

[RoryA]

Use a formula in the CF dialog using ISNA:
=ISNA(B1)
for example.

 

[Daisy_Bumbleroot]

Use a formula in the CF dialog using ISNA:
=ISNA(B1)
for example.

Im sorry, im not sure I understand

so i go on to my first cell with a #N/A (which is C9),
go to conditional formatting,
Formula is : =ISNA(C9)

It changes it to ="isna=c9" and then doesnt change the colours?

 

[RoryA]

Are you sure you entered the formula as =ISNA(C9)? It should not be converted to ="isna=c9" as that makes no sense.

 

[Daisy_Bumbleroot]

Are you sure you entered the formula as =ISNA(C9)? It should not be converted to ="isna=c9" as that makes no sense.

I cant have entered it properly. I tried again and it works. Many thanks for your help, thats brilliant.

 

[RoryA]

Glad to help.