Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
"X" Axis with different scale | Charts and Charting in Excel | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Chart Axis Scale Auto Values | Charts and Charting in Excel |