Displaying Data for Current Months Only on a Line Chart

[HangMan]

I have a data set which records earnings on a monthly basis and compares these to target figures. I need to be able to dispaly this information in a line graph (see attached). The problem I have is that for future earnings, i.e. March to December in the sample, the figures are not yet known, resulting in the line dipping back to zero.

I don't want to have to edit the data set each month to add the latest month, so I want to know if there is any way to specify that the line only displays the months for which there is data i.e. anything greater than zero, so in my sample it displays all 12 months for the target line but only Jan and Feb for the earnings line.

Then each month when the earnings figure is entered, the chart extends the line to include just that months data and so on. so you don't get the line dipping back to zero?

This isn't an issue with a bar chart, but I need a line chart.

Many thanks

[Kaper]

No attachment

By the way - have you checked this: http://www.tushar-mehta.com/excel/ne...ynamic_charts/

[HangMan]

I'm experiencing a few problems with the site, not getting reply notifications even when set to via email and so on! Anyway, attachement hopefully attached now.

[Kaper]

But don't email - just keep reading and learning yourself :-)

May be even easier approach. In E3:

Please Login or Register  to view this content.

and copy down.
Now use column E data for your earnings series.

[HangMan]

No, I hadn't seen this, very useful.

Please Login or Register  to view this content.

I guess for me this raises the question how would I adapt the OFFSET forumla to accomodate 0 values, sadly the source data is populated with a zero by default, so if would effectively only become valid data and plottable when the value is < or > 0 in the attached sample?

Many thanks

[HangMan]

Hi Kaper,

I don't have control over the source data, so whilst

Please Login or Register  to view this content.

works in principal, in practice I can't control the data source, which is why an adaptation of the offset code would be a much neater solution, if it is possible to adapt?

Many thanks

[Kaper]

If the range is populated with 0's use COUNTIF rather than COUNTA
So something like (or similar)

Please Login or Register  to view this content.

should do.
See also this very simple approach from prev. post.

[HangMan]

Thanks Kaper,

To take it one step further, I now trying to figure out how to apply your OFFSET when the range is non-contiguous, e.g.

Please Login or Register  to view this content.

Struggling with this one!

[Kaper]

N/A to your sample file.

in "Excel world" as opposite to real one: One spreadsheet is worth 100 pictures (while still a picture is often worth more than 100 words).