If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic Charts and Blanks
Dear All.
I have produced a chart dynamically, using the technique outlined on the Peliter website. In general this works fine, in terms of when the list of data grows. However, when my data list shrinks it causes a problem. The data is the result of an Offset function, which leaves a blank cell where there is no data. However, Excel charts a zero value - which I dont want to have (I would rather it treat the cells as though they were blank). Does anyone know how to do this??? Thanks. |
#2
|
|||
|
|||
Dynamic Charts and Blanks
You need to change your formula so it results in NA() instead of "" (which
isn't blank, by the way, it's a short text value). This looks like #N/A in the sheet, but isn't plotted in a line or XY chart. Use conditional formatting to hide the error: http://contextures.com/xlCondFormat03.html#Errors - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "BoRed79" wrote in message ... Dear All. I have produced a chart dynamically, using the technique outlined on the Peliter website. In general this works fine, in terms of when the list of data grows. However, when my data list shrinks it causes a problem. The data is the result of an Offset function, which leaves a blank cell where there is no data. However, Excel charts a zero value - which I dont want to have (I would rather it treat the cells as though they were blank). Does anyone know how to do this??? Thanks. |
#3
|
|||
|
|||
Dynamic Charts and Blanks
Thanks.
How do I change my formula to get an error?? My current formula is: =OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2) "Jon Peltier" wrote: You need to change your formula so it results in NA() instead of "" (which isn't blank, by the way, it's a short text value). This looks like #N/A in the sheet, but isn't plotted in a line or XY chart. Use conditional formatting to hide the error: http://contextures.com/xlCondFormat03.html#Errors - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "BoRed79" wrote in message ... Dear All. I have produced a chart dynamically, using the technique outlined on the Peliter website. In general this works fine, in terms of when the list of data grows. However, when my data list shrinks it causes a problem. The data is the result of an Offset function, which leaves a blank cell where there is no data. However, Excel charts a zero value - which I dont want to have (I would rather it treat the cells as though they were blank). Does anyone know how to do this??? Thanks. |
#4
|
|||
|
|||
Dynamic Charts and Blanks
I'm guessing that it might be like this, though I'm not sure I understand
why it doesn't already raise an error: =if(isna(MATCH(ROW()-1,$BL$2:$BL$17,0)),NA(),OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2)) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "BoRed79" wrote in message ... Thanks. How do I change my formula to get an error?? My current formula is: =OFFSET($BL$1,MATCH(ROW()-1,$BL$2:$BL$17,0),-2) "Jon Peltier" wrote: You need to change your formula so it results in NA() instead of "" (which isn't blank, by the way, it's a short text value). This looks like #N/A in the sheet, but isn't plotted in a line or XY chart. Use conditional formatting to hide the error: http://contextures.com/xlCondFormat03.html#Errors - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "BoRed79" wrote in message ... Dear All. I have produced a chart dynamically, using the technique outlined on the Peliter website. In general this works fine, in terms of when the list of data grows. However, when my data list shrinks it causes a problem. The data is the result of an Offset function, which leaves a blank cell where there is no data. However, Excel charts a zero value - which I dont want to have (I would rather it treat the cells as though they were blank). Does anyone know how to do this??? Thanks. |
Thread Tools | |
Display Modes | |
|
|