When using an IF-formula, my diagram treats empty cells as zero
 

I have prepared a line-diagram that will show quarterly information for the
last year and the coming five years. Since data only is available for three
quarters at this point of time, I want the diagram to show those three
quarters now and nothing more and then automatically add an additional
quarter when I have filled in that information in the input sheet.

In order to get this functionality, I have tried to use two different
IF-formulas:

=IF(O32="n.a.","",O32) and
=IF(O32="n.a.",,O32)

My problem is that the Excel-diagram treats the empty cells in the spread
sheet (those cells containing any of the IF-formulas above but lack any
information) as zero, and do not treat them as €œtrue empty cells€� (even
though they are empty in the spread sheet). And yes, I have instructed the
diagram not to plot empty cells by using Tools/options/Chart/ Plot empty
cells as - not plotted.

My question is now €“ can I in any way make those cells €œempty€� in the sense
that Excel will not plot those cells that are empty? i.e. is there any other
writing that I can use in my IF-formula above in order to achieve this? Or is
there any other way that I can get the same result, i.e. make the diagram
plot another quarter automatically when the data is added in the input sheet
but dont plot any future quarters?

I would really appreciate a solution on this one and I know that some of my
colleagues also are struggling with the same issue.

Best regards,

/Lars

When using an IF-formula, my diagram treats empty cells as zero
 

How about

=IF(O32="",NA(),O32)

HTH
Kostis Vezerides


Lars F wrote:
I have prepared a line-diagram that will show quarterly information for the
last year and the coming five years. Since data only is available for three
quarters at this point of time, I want the diagram to show those three
quarters now and nothing more and then automatically add an additional
quarter when I have filled in that information in the input sheet.

In order to get this functionality, I have tried to use two different
IF-formulas:

=IF(O32="n.a.","",O32) and
=IF(O32="n.a.",,O32)

My problem is that the Excel-diagram treats the empty cells in the spread
sheet (those cells containing any of the IF-formulas above but lack any
information) as zero, and do not treat them as "true empty cells" (even
though they are empty in the spread sheet). And yes, I have instructed the
diagram not to plot empty cells by using Tools/options/Chart/ Plot empty
cells as - not plotted.

My question is now - can I in any way make those cells "empty" in the sense
that Excel will not plot those cells that are empty? i.e. is there any other
writing that I can use in my IF-formula above in order to achieve this? Or is
there any other way that I can get the same result, i.e. make the diagram
plot another quarter automatically when the data is added in the input sheet
but don't plot any future quarters?

I would really appreciate a solution on this one and I know that some of my
colleagues also are struggling with the same issue.

Best regards,

/Lars

When using an IF-formula, my diagram treats empty cells as zer
 

It worked great - thank you so much!

/Lars

"vezerid" wrote:

How about

=IF(O32="",NA(),O32)

HTH
Kostis Vezerides


Lars F wrote:
I have prepared a line-diagram that will show quarterly information for the
last year and the coming five years. Since data only is available for three
quarters at this point of time, I want the diagram to show those three
quarters now and nothing more and then automatically add an additional
quarter when I have filled in that information in the input sheet.

In order to get this functionality, I have tried to use two different
IF-formulas:

=IF(O32="n.a.","",O32) and
=IF(O32="n.a.",,O32)

My problem is that the Excel-diagram treats the empty cells in the spread
sheet (those cells containing any of the IF-formulas above but lack any
information) as zero, and do not treat them as "true empty cells" (even
though they are empty in the spread sheet). And yes, I have instructed the
diagram not to plot empty cells by using Tools/options/Chart/ Plot empty
cells as - not plotted.

My question is now - can I in any way make those cells "empty" in the sense
that Excel will not plot those cells that are empty? i.e. is there any other
writing that I can use in my IF-formula above in order to achieve this? Or is
there any other way that I can get the same result, i.e. make the diagram
plot another quarter automatically when the data is added in the input sheet
but don't plot any future quarters?

I would really appreciate a solution on this one and I know that some of my
colleagues also are struggling with the same issue.

Best regards,

/Lars

When using an IF-formula, my diagram treats empty cells as zero
 

I'd try:

=IF(O32="n.a.",na(),O32)

And maybe use format|conditional formatting to hide those errors in the
worksheet.

Lars F wrote:

I have prepared a line-diagram that will show quarterly information for the
last year and the coming five years. Since data only is available for three
quarters at this point of time, I want the diagram to show those three
quarters now and nothing more and then automatically add an additional
quarter when I have filled in that information in the input sheet.

In order to get this functionality, I have tried to use two different
IF-formulas:

=IF(O32="n.a.","",O32) and
=IF(O32="n.a.",,O32)

My problem is that the Excel-diagram treats the empty cells in the spread
sheet (those cells containing any of the IF-formulas above but lack any
information) as zero, and do not treat them as €œtrue empty cells€� (even
though they are empty in the spread sheet). And yes, I have instructed the
diagram not to plot empty cells by using Tools/options/Chart/ Plot empty
cells as - not plotted.

My question is now €“ can I in any way make those cells €œempty€� in the sense
that Excel will not plot those cells that are empty? i.e. is there any other
writing that I can use in my IF-formula above in order to achieve this? Or is
there any other way that I can get the same result, i.e. make the diagram
plot another quarter automatically when the data is added in the input sheet
but dont plot any future quarters?

I would really appreciate a solution on this one and I know that some of my
colleagues also are struggling with the same issue.

Best regards,

/Lars

--

Dave Peterson