Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Do not want chart to graph cells with formulas that are "blank"
I am trying to chart % actual v. % plan data.
I want the chart to show % plan data through 6 quarters I want the chart to show only % actual through current quarter - I have table1 that will get updated monthly with values representing "actual" data. - I have table2 that is filled with formulas that gets autopopulated with % data based on values entered in table1. - Table2 formula is something like this [If(table1C6="","",1-table1C6/C14)]. That way the cells in table2 look blank until values are entered in table1. - I have chart1 that graphs the output of table2 (along with "% plan" data from table3). - chart1 graphs the blank cells in table2 as "0" apparently because the cells are not truly "blank", i.e. the cells have the [If(table1C6="","",1-table1C6/C14)] formula. - with my curser activating chart1, I choose Tools--Options--Chart--Plot Empty Cells as 'not plotted' (leave gaps) - it still charts the "empty cells" as 0 Goal: to have chart1 plot the data from table2 as table1 data is entered each month. Do not plot the "blank" cells from table2 as "0". And I don't want to have to remove the formulas from table2, otherwise I'll have to update both table1 and table2 every month (the point of having formulas). thanks for any thoughts on how to accomplish this!! :) |
#2
|
|||
|
|||
What kind of chart is it? This technique works with XY and Line charts.
Note that "" in a cell means the cell is not blank, it includes the string "". Change your formula to: If(table1C6="",NA(),1-table1C6/C14) NA() produces the #N/A error in the worksheet, which looks ugly, but is not plotted in the chart. You don't get a gap in the connecting line, instead you get the line interpolated over the gap, connecting the points on either side. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ julnord wrote: I am trying to chart % actual v. % plan data. I want the chart to show % plan data through 6 quarters I want the chart to show only % actual through current quarter - I have table1 that will get updated monthly with values representing "actual" data. - I have table2 that is filled with formulas that gets autopopulated with % data based on values entered in table1. - Table2 formula is something like this [If(table1C6="","",1-table1C6/C14)]. That way the cells in table2 look blank until values are entered in table1. - I have chart1 that graphs the output of table2 (along with "% plan" data from table3). - chart1 graphs the blank cells in table2 as "0" apparently because the cells are not truly "blank", i.e. the cells have the [If(table1C6="","",1-table1C6/C14)] formula. - with my curser activating chart1, I choose Tools--Options--Chart--Plot Empty Cells as 'not plotted' (leave gaps) - it still charts the "empty cells" as 0 Goal: to have chart1 plot the data from table2 as table1 data is entered each month. Do not plot the "blank" cells from table2 as "0". And I don't want to have to remove the formulas from table2, otherwise I'll have to update both table1 and table2 every month (the point of having formulas). thanks for any thoughts on how to accomplish this!! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) | |||
How do you show formulas in certain cells only (not the whole she. | Excel Worksheet Functions | |||
how to copy formulas to other cells in excell | Setting up and Configuration of Excel |