Not Displaying Data on a Line Chart

[HangMan]

Appologies - This post got posted twice in error

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

[DBY]

Hello
Not sure if this is the active post of the two but see the attached example for a couple ways you might do this.

DBY

[HangMan]

Hi DBY,

Okay, thanks for this, on my real chart, the data is made up from non-contiguous cells. I'm struggling to adapt the OFFSET formula...

Please Login or Register  to view this content.

[DBY]

Hi
I'll give it some thought and try and get back to you. Unless someone else comes in with some ideas in the meantime.

[HangMan]

Okay, many thanks, I appreciate your help...

[DBY]

The best way I think would be to create a column data series by transposing the non-contiguous cells with formulas. See amended attachment.

[DBY]

Deleted this post the formula was in error when I tested it out as it puts the numbers in the wrong position. Should have realized. Sorry!

[DBY]

Post was entered twice by mistake!

[HangMan]

I'll take a look and come back to you, probably tomorrow now...

[HangMan]

So, I could use

Please Login or Register  to view this content.

to get the value from every second column, the part I'm struggling with is how to use COUNTIF to determine when the value in every second column <> 0 to determine the width part of the OFFSET.

Please Login or Register  to view this content.

On its own this seems to count the number of cells that 'are' zero, rather than <> 0.

Combined this gives me:

Please Login or Register  to view this content.

So I think the first half works but the COUNTIF part used to determine the width of the OFFSET isn't working and I can't see why?

[DBY]

Hi
I'm assuming from your last post that transposing them into a new column as in my last attachment is not an option you wish to consider as it seems the easiest way to achieve this. The named range way is tricky because Offset will return a continuous range whatever you do the countif merely tells it how far to extend. I'll give it some thought or maybe another member might have a solution.

[HangMan]

Sadly because of the nature of the data, this isn't an option.

I think the formula is close, I just can't figure out how to count every other column to check when the cell <> 0 to define the width of the OFFSET.

Any thoughts?

[DBY]

I don't see as the formula's close as it only returns a single value and has to be dragged across the page to produce an array and you say you don't want this ,as in my example. The named range needs to produce an array of values in a single formula.

[HangMan]

Okay, I think you're right, it's not close, now that I see what it is actually doing, so likewise

[DBY]

Take a look at the attached file, Sheet2. Is this any thing like?

[HangMan]

That seems to work, though I noticed you'd replaced the zero's with blanks. I put the zero's back in and whilst that still 'appears' to work, does it the use of NA() still apply when there is a 0 in the cell of only if there is nothing in the cell at all. Sadly I can't alter the source data and there are zeros in the cells when there is no data yet for the relevant month. Will this potentiall cause problems?

Please Login or Register  to view this content.

I also changed the >0 to <>0 to allow for negative numbers.

[DBY]

Now you've adjusted the formula to accept negative values, cells that are zeros or blank will generate an #N/A value and not be charted. The chart simply won't see those values.

[HangMan]

Okay, that's great...

One additional problem is, that on my actual chart, I also need to plot the Forecast Data as well and if I just select the non contiguous cells C4, E4, G4 etc through to U4, it squashes the 10 months of data into half the chart, as in ten months of forecast data is shown across the first 5 months only, instead of displaying it across the full 10 months.

I assume that a similar formula would be needed for the forecast data, but how would I adapt this, so it picks up the forecast figures rather than the actual figures?

[DBY]

See version 4 attached is this what you're looking to do?

[HangMan]

This is exactly what I want, I'm having a problem on my real charts where the X Axis M1 to M12 are still squashed into 6 months, even though the forecast and actual lines appear to be plotted correctly! I need to try and figure out what is going on, but I think this in theory works, so many thanks for the time you've taken looking at this.

I'll come back if I still can't figure out why this isn't working properly on my real charts!

[DBY]

You're welcome. With a name like 'Hangman' I thought I better stick with it!

[HangMan]

So the problem seems to be, looking at your last example, that it is plotting 20 points rather than 10 points, so the X Axis should basically say M1, M2, M3, M4 etc, and not M1 Forecast, M1 Actual and so on and then plot both the forecast and actual for M1, then M2 and so on.

This is why on my real chart, the X axis is being squashed into half the graph...

Any ideas how to resolve that?

[HangMan]

I'm glad you did, I would never have figured out how to get this far... Just need to resolve the X Axis, will this require another formula to resolve or is there another way to fix this?

[DBY]

If we can't add anything to the spreadsheet layout and it has to be done with named ranges, the only thing I can think of is to double space the categories and offset the Forecast range to line up with the 'Actual'. See example 5 attached.