Graph Gripes

MR STAT

New Member
Joined
May 1, 2002
Messages
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top