Thread: Forcing a chart to plot only relevant data entries

Dear all- please help!

I am having an issue with charting in Excel 2007.

I am attempting to create a basic column chart (with 1 series)

The issue is- I want the chart to only plot data entries which show a change from the previous data entry i.e if the previous data entry is 5 and the following data entries are all 5, then I want the chart to only plot data points (bars) up to the 1st appearance of the 5 data value

The background to this (may be helpful/of interest):

I am creating an excel toolkit which assesses carbon reductions when renewable technologies are applied in sequence/cumulatively to a building

On the chart I wish to plot
1) A baseline value of carbon emissions (data entry 1) - before any technologies are applied

2) The carbon emissions after 1 technology is applied
3) the carbon emissions after 2 technologies are applied
4).....and so on

However, if the user of the toolkit only wishes to apply 2 technologies, my chart still displays 3 useless bars for empty technology selections 3,4 and 5 (which they could have activated, but have left blank)!!!! - the graph looks silly and pointless.

- can I get rid of the useless data entries from the chart? How do I do this?

Any help would be very gratefully received,

cheers,

Sam

The attachment isn't elegant (wait for Andy Pope), but it may do what you want.

Dear Andy,

thanks very much for that (and the promptness of the response!) and I'll have a go first thing on Monday morning- I will send more details and an attachment if I run into more problems- thank you so much again.

p.s

I am not trying to switch off technologies which have no effect, but simply trying to ensure that if a user select only 2 technologies then the other 3 technologies (which they didnt activate) are not displayed. No technology I apply would have 0 effect.

I.e-
1) I have results of carbon reductions resulting when 10 different technologies are applied to a building

2) The user can select the number of technologies they wish to apply to the building (max of 5) by selecting technologies from drop down menus (5 menus)
. This allows the user to tailor a renewable energy strategy, given the results in point 1)

3) The graphs mentioned before are plotted for the relevent strategy and the problem of useless data entries (when the strategy only has 2 instead of 5 technologies) results

- sorry if this was confusing in any way

Sam

Sorry missed off SHG- Thank you

Dear Andy/SHG/anyone else,

I tried to use SHG's solution to my problem, but it did not do all that i needed. It did get me half way though, so thank you for the initial help.

I enclose an Excel 2007 attachment for perusal, which highlights the issue I am still having- basically I still have rogue data entries which ruin my graphs-

If anyone can think of a solution to this problem I would be exceptionally grateful!!!

Thanks,

Sam

Dear Andy-

this is an excellent work-around and I am exceptionally grateful for both your help and rapid response,

Thank you very much,

Sam