Line chart zero values

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 ???

Hi,

Try using NA() instead of ""

Walshy wrote:

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, Microsoft MVP - Excel
http://www.andypope.info

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 ?

-----Original Message-----
Hi,

Try using NA() instead of ""

Walshy wrote:

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, Microsoft MVP - Excel
http://www.andypope.info
.

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 !!!

-----Original Message-----
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 ?

-----Original Message-----
Hi,

Try using NA() instead of ""

Walshy wrote:

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, Microsoft MVP - Excel
http://www.andypope.info
.

.

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

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

wrote:

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 !!!


-----Original Message-----
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 ?


-----Original Message-----
Hi,

Try using NA() instead of ""

Walshy wrote:


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, Microsoft MVP - Excel
http://www.andypope.info
.


.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info