Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 15th 07, 08:20 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 177
Default Auto Scale X axis doesn't work with "" values in cell

First of all, thanks to tushar:
http://www.tushar-mehta.com/excel/ne...rts/index.html

I'm trying to get a chart to work with the basic ranges defined as this:

XValues =OFFSET('Basic Range'!YValues,0,-1)
YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

Assuming dates in A5-A16 and data in B5-B16.

However, this won't work if the data in column B is equal to this ""

I get the "" from an if statement: If cell in column A is blank, then cell
in column B = "" or =IF(isblank(A11),"",get stock price data as of the date
in cell All)

Any suggestions to work around the "" issue?

thanks very much.

SteveC


  #2   Report Post  
Old February 15th 07, 10:10 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Auto Scale X axis doesn't work with "" values in cell

"" is interpreted as a piece of text, with a value of zero. Replace "" with
NA(). This leaves an ugly #N/A error in the cell, but is ignored by line and
XY charts. Hide the cell error with conditional formatting.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"SteveC" wrote in message
...
First of all, thanks to tushar:
http://www.tushar-mehta.com/excel/ne...rts/index.html

I'm trying to get a chart to work with the basic ranges defined as this:

XValues =OFFSET('Basic Range'!YValues,0,-1)
YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

Assuming dates in A5-A16 and data in B5-B16.

However, this won't work if the data in column B is equal to this ""

I get the "" from an if statement: If cell in column A is blank, then
cell
in column B = "" or =IF(isblank(A11),"",get stock price data as of the
date
in cell All)

Any suggestions to work around the "" issue?

thanks very much.

SteveC



  #3   Report Post  
Old February 15th 07, 10:37 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 177
Default Auto Scale X axis doesn't work with "" values in cell

thanks John... was about to write that I found your explanation of this in
another post...

"Jon Peltier" wrote:

"" is interpreted as a piece of text, with a value of zero. Replace "" with
NA(). This leaves an ugly #N/A error in the cell, but is ignored by line and
XY charts. Hide the cell error with conditional formatting.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"SteveC" wrote in message
...
First of all, thanks to tushar:
http://www.tushar-mehta.com/excel/ne...rts/index.html

I'm trying to get a chart to work with the basic ranges defined as this:

XValues =OFFSET('Basic Range'!YValues,0,-1)
YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

Assuming dates in A5-A16 and data in B5-B16.

However, this won't work if the data in column B is equal to this ""

I get the "" from an if statement: If cell in column A is blank, then
cell
in column B = "" or =IF(isblank(A11),"",get stock price data as of the
date
in cell All)

Any suggestions to work around the "" issue?

thanks very much.

SteveC




  #4   Report Post  
Old February 15th 07, 11:34 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Auto Scale X axis doesn't work with "" values in cell

Yes, it's a common problem.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"SteveC" wrote in message
...
thanks John... was about to write that I found your explanation of this in
another post...

"Jon Peltier" wrote:

"" is interpreted as a piece of text, with a value of zero. Replace ""
with
NA(). This leaves an ugly #N/A error in the cell, but is ignored by line
and
XY charts. Hide the cell error with conditional formatting.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"SteveC" wrote in message
...
First of all, thanks to tushar:
http://www.tushar-mehta.com/excel/ne...rts/index.html

I'm trying to get a chart to work with the basic ranges defined as
this:

XValues =OFFSET('Basic Range'!YValues,0,-1)
YValues =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

Assuming dates in A5-A16 and data in B5-B16.

However, this won't work if the data in column B is equal to this ""

I get the "" from an if statement: If cell in column A is blank, then
cell
in column B = "" or =IF(isblank(A11),"",get stock price data as of
the
date
in cell All)

Any suggestions to work around the "" issue?

thanks very much.

SteveC








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pictures to work with "data" "sort" option arad Excel Discussion (Misc queries) 1 April 18th 06 09:15 PM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 11:01 PM
"X" Axis with different scale maperalia Charts and Charting in Excel 0 January 18th 06 08:28 PM
Changing "returned" values from "0" to "blank" LATATC Excel Worksheet Functions 2 October 20th 05 04:41 PM
Chart Axis Scale Auto Values Moses Bunting Charts and Charting in Excel 1 June 7th 05 11:03 PM


All times are GMT +1. The time now is 02:00 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017