Getting a chart to stop if the formula resutls in a blank cell
Hi,
I'm trying to make a chart that displays data for each month of the year. The data itself is referenced from another location. this is the formula that is in each cell currently: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E))," ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) so my data has infor from January through July, but because I don't have info yet from August through December, those cells are blank. My problem is that my chart doesn't think those cells are blank, it automatically plots them like there value is zero. I just would like my chart to stop at those blank cells and not automatically default to zero. I tried changing it under options/chart but it is already set to stop at blank cells. I would appreciate any help I can get. Thank you, Markag |
Getting a chart to stop if the formula resutls in a blank cell
You may use the function NA() instead of the double quotes in the formula.
Something like: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E)),NA(),VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) The result will be #N/A for unfound data, and that will not be represented as zero. Hope this helps, Miguel. "markag" wrote: Hi, I'm trying to make a chart that displays data for each month of the year. The data itself is referenced from another location. this is the formula that is in each cell currently: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E))," ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) so my data has infor from January through July, but because I don't have info yet from August through December, those cells are blank. My problem is that my chart doesn't think those cells are blank, it automatically plots them like there value is zero. I just would like my chart to stop at those blank cells and not automatically default to zero. I tried changing it under options/chart but it is already set to stop at blank cells. I would appreciate any help I can get. Thank you, Markag |
Getting a chart to stop if the formula resutls in a blank cell
Try something like this:
Let your base formula return an error, like this: =VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE) Excel charts ignore error values. THEN.... Apply Conditional Formatting to the plotted data... Select the data (Assuming Cells A3:K3, with A3 as the active cell) From the Excel main menu: <format<conditional formatting Formula is: =ISERROR(A3) Click the [Format...] button and set the font color to white. That way, the errors won't chart and the error values won't be visible. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "markag" wrote: Hi, I'm trying to make a chart that displays data for each month of the year. The data itself is referenced from another location. this is the formula that is in each cell currently: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E))," ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) so my data has infor from January through July, but because I don't have info yet from August through December, those cells are blank. My problem is that my chart doesn't think those cells are blank, it automatically plots them like there value is zero. I just would like my chart to stop at those blank cells and not automatically default to zero. I tried changing it under options/chart but it is already set to stop at blank cells. I would appreciate any help I can get. Thank you, Markag |
Getting a chart to stop if the formula resutls in a blank cell
Thank you, I would have been really stuck without that.
"Miguel Zapico" wrote: You may use the function NA() instead of the double quotes in the formula. Something like: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E)),NA(),VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) The result will be #N/A for unfound data, and that will not be represented as zero. Hope this helps, Miguel. "markag" wrote: Hi, I'm trying to make a chart that displays data for each month of the year. The data itself is referenced from another location. this is the formula that is in each cell currently: =IF(ISERROR(VLOOKUP($G6,Results!$A$4:$P$368,4,FALS E))," ",VLOOKUP($G6,Results!$A$4:$P$368,4,FALSE)) so my data has infor from January through July, but because I don't have info yet from August through December, those cells are blank. My problem is that my chart doesn't think those cells are blank, it automatically plots them like there value is zero. I just would like my chart to stop at those blank cells and not automatically default to zero. I tried changing it under options/chart but it is already set to stop at blank cells. I would appreciate any help I can get. Thank you, Markag |
All times are GMT +1. The time now is 03:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com