Line chart zero values

[Walshy]

I am trying to do a automated line chart that updates as
users input over the next 12 weeks... the chart is
consisting of 12 weeks on the x axis and % on the y.
The problem im having is the values in the table that the
chart is picking up from are formulas... but if the
formula result is zero or ""(blank) I want the line chart
to ignore it... rather than plot zero values making the
line drop when a nil value or blank... for rest of the
whole 12 weeks...?

eg

week 1 = 10%
week 2 = 11%
week 3 =

I want the line to stop at week 2...? but because to work
out the % is a formula the line is carrying on ?

example of the formula is =if(a1="","",a1/100)

Can anyone help me on this please ???

 

[Andy Pope]

Hi,

Try using NA() instead of ""

I am trying to do a automated line chart that updates as
users input over the next 12 weeks... the chart is
consisting of 12 weeks on the x axis and % on the y.
The problem im having is the values in the table that the
chart is picking up from are formulas... but if the
formula result is zero or ""(blank) I want the line chart
to ignore it... rather than plot zero values making the
line drop when a nil value or blank... for rest of the
whole 12 weeks...?

eg

week 1 = 10%
week 2 = 11%
week 3 =

I want the line to stop at week 2...? but because to work
out the % is a formula the line is carrying on ?

example of the formula is =if(a1="","",a1/100)

Can anyone help me on this please ???

 

[Guest]

Thanks you loads for looking, I have tried :

=if(a1="",n/a(),"a1/100)
but this returns #name for the answer and line carries
on ???

also tried putting :

=if(a1="","n/a()","a1/100)
but displays n/a() and chart line still carries on ???


Am I doing this right ?

 

[Guest]

Its ok, I played about with n/a and have sorted it !!!
Thank you very much !!!

ended up :

=if(a1="",#n/a,"a1/100) - this worked !!! WEAH !!!

 

[Andy Pope]

Actually I was referring to the worksheet function
=NA()

but if #N/A works for you thats cool.