0

I am trying to create a scatter plot with the y values stored in the y1,y2,y3 columns. The objective is to obtain a color for each column. This works fine. My problem is that blank values are also plotted if the column is computed with an equation. Concretely, E2, E3 and E4 should not appear in the plot.

y1 and y2 are pure values, whereas y3 is computed with a simple if condition. For example, E2 cell is defined as =IF(C2="", 40,""). I also tried to check the 'Hidden and Empty Cells' option, as suggested in this post, but nothing happens.

\ | A | B | C | D | E |

1 | x | y1 | y2 | y3 |

2 | 1 |   | 14 |   |

3 | 2 |  6 | 45 |   |

4 | 3 | 12 | 6 |   |

5 | 4 | 4 |  | 40 |

enter image description here

Nic
  • 1,262
  • 2
  • 22
  • 42
  • 2
    There's an solution here: http://stackoverflow.com/questions/15013911/creating-a-chart-in-excel-that-ignores-n-a-or-blank-cells. Bit ugly because you would have to use `=IF(C2="", 40,NA())` which creates `#NA!` in your data set but does prevent the 0 items in the scatter plot. YMMV – Robin Mackenzie Mar 09 '17 at 14:04
  • Hi @Robin. Actually, it does work, so thank you very much. However, you are right, it's not very clean as a solution. It surprises me that there is no way of preventing a plot from considering blanks as zeros. – Nic Mar 09 '17 at 14:09
  • Hi Robin, can you put your comment in an answer so that the OP can mark it as solved? – ChrisG Mar 09 '17 at 16:40

0 Answers0