Charles has attended:
Excel VBA Intro Intermediate course
Graph - data series Att: Anthony
I am graphing data from a range of cells for 12 months. The formula for the range of cells is: =IF(I29>$A$1,"",SUM(I31:I33)) where I29 = month(1-12) and A1 = current month. For months beyond current month the graph continues to read cell as zero rather than blank therefore graghing future month (beyond current month) as zero rather than stopping plotting at current month. How do I go about ensuring that values are only graphed up to current month and does not graph future months as zero. Thanks.
RE: Graph - data series Att: Anthony
Hi Charles, many thanks for posting your query. Change your formula to this:
=IF(I29>$A$1,NA(),SUM(I31:I33))
Instead of blank cells on your data you'll end up with #N/A but you'll find they don't get plotted on your chart. Then use conditional formatting on those cells to turn any #N/A white: select your plotted data, select Conditional Formatting and then New Rule, and then the option to use a formula. You'll need something along the lines of =ISNA(D1) (where D1 is the first cell in the line of your selected data), then click the format button
and select white fonts, click OK twice.
Do let me know how you get on.
Anthony
