Graph Gripes

MR STAT · May 2nd, 2002, 04:40 AM

Having real problems with representing blank data in line charts. Need standard line graphs but when there is no data for a particular x axis point I want the line to break and I don't want the line dropping to zero. Does anyone know a way I can represent the blank data without deleting the formula that brings back the blank cell.

Aladin Akyurek · May 2nd, 2002, 04:54 AM

Quote:

On 2002-05-02 03:40, MR STAT wrote:
Having real problems with representing blank data in line charts. Need standard line graphs but when there is no data for a particular x axis point I want the line to break and I don't want the line dropping to zero. Does anyone know a way I can represent the blank data without deleting the formula that brings back the blank cell.

Make the formula that currently returns a 0 to return #N/A instead.

nancyo · May 2nd, 2002, 05:58 AM

I have the same problem - I tried the suggestion below and it didn't work. I have three data series, and wanted the spreadsheet to have NA where there was no data, can't seem to figure out another way.

Aladin Akyurek · May 2nd, 2002, 06:05 AM

Quote:

On 2002-05-02 04:58, nancyo wrote:
I have the same problem - I tried the suggestion below and it didn't work. I have three data series, and wanted the spreadsheet to have NA where there was no data, can't seem to figure out another way.

Are you using a formula "where there is no data"? If so, please post it.

nancyo · May 2nd, 2002, 06:54 AM

The graph consistes of three series:

1. Data values which are formulas to copy data from other spreadsheets. These formulas currently return "NA" if there is no data on the other spreadsheet.

2. The second and third data series are formulas which refer to #1. I had originally had the formulas return "NA" if #1 was "NA"(istext). When I did this, these points "zeroed out" on the graph. I changed the formulas to be simple formuls (now return "#VALUE!"). This works fine, but the spreadhseet is messy.

I can live with this, but would prefer to clean it all up.

Aladin Akyurek · May 2nd, 2002, 07:32 AM

Quote:

On 2002-05-02 05:54, nancyo wrote:
The graph consistes of three series:

1. Data values which are formulas to copy data from other spreadsheets. These formulas currently return "NA" if there is no data on the other spreadsheet.

2. The second and third data series are formulas which refer to #1. I had originally had the formulas return "NA" if #1 was "NA"(istext). When I did this, these points "zeroed out" on the graph. I changed the formulas to be simple formuls (now return "#VALUE!"). This works fine, but the spreadhseet is messy.

I can live with this, but would prefer to clean it all up.

You didn't post the formula(s) that you use.

If you use a formula of the form:

=IF(LEN(A1),A1,#N/A)

zeroes will not show up in the graphs.

If you don't like the sight of #N/A's, you can whiten them using conditional formatting.

Aladin

nancyo · May 2nd, 2002, 08:18 AM

Just simple formulas:

=($c$51+(0.31*$g$51))

I'm not that familiar with the format suggested, but will look into it. Can you exlain what it means?

[ This Message was edited by: nancyo on 2002-05-02 07:18 ]

MR STAT · May 2nd, 2002, 08:31 AM

Ta Aladin, works a treat

Aladin Akyurek · May 2nd, 2002, 08:52 AM

Quote:

On 2002-05-02 07:18, nancyo wrote:
Just simple formulas:

=($c$51+(0.31*$g$51))

I'm not that familiar with the format suggested, but will look into it. Can you exlain what it means?

[ This Message was edited by: nancyo on 2002-05-02 07:18 ]

Do C51 or G51 or both ever contain formula-generated blanks instead of numbers, so that you get a #VALUE! error?

nancyo · May 3rd, 2002, 10:03 AM

No, both c51 and g51 are formulas which return NA if there is no data, or else they return an average on a column of values.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 04:40 AM	#1
MR STAT New Member Join Date: May 2002 Posts: 4	Having real problems with representing blank data in line charts. Need standard line graphs but when there is no data for a particular x axis point I want the line to break and I don't want the line dropping to zero. Does anyone know a way I can represent the blank data without deleting the formula that brings back the blank cell.

May 2nd, 2002, 05:58 AM	#3
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	I have the same problem - I tried the suggestion below and it didn't work. I have three data series, and wanted the spreadsheet to have NA where there was no data, can't seem to figure out another way.

May 2nd, 2002, 06:54 AM	#5
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	The graph consistes of three series: 1. Data values which are formulas to copy data from other spreadsheets. These formulas currently return "NA" if there is no data on the other spreadsheet. 2. The second and third data series are formulas which refer to #1. I had originally had the formulas return "NA" if #1 was "NA"(istext). When I did this, these points "zeroed out" on the graph. I changed the formulas to be simple formuls (now return "#VALUE!"). This works fine, but the spreadhseet is messy. I can live with this, but would prefer to clean it all up.

May 2nd, 2002, 08:18 AM	#7
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	Just simple formulas: =($c$51+(0.31*$g$51)) I'm not that familiar with the format suggested, but will look into it. Can you exlain what it means? [ This Message was edited by: nancyo on 2002-05-02 07:18 ]

May 2nd, 2002, 08:31 AM	#8
MR STAT New Member Join Date: May 2002 Posts: 4	Ta Aladin, works a treat

May 3rd, 2002, 10:03 AM	#10
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	No, both c51 and g51 are formulas which return NA if there is no data, or else they return an average on a column of values.