Dynamic Range Charting?

[Greg Mooney]

Hello,

I am not sure that dynamic range in charts is going to solve this problem or not?

I have 3 rows of data and say 10 columns wide that I need to chart 2 of them on.

Row 1 has data
Row 2 is blank (based on other Excel conditions)
Row 12 (or some other row number) has the data that I want to plot with Row 1

Can this be done and if so how? I have not used Dynamic named ranges in Excel charts before.

 

[Smallman]

Hi Greg

Welcome to Chandoo

Will you only ever have to Chart Row 1 and 3. If so just hold ctr down as you choose your chart data and the chart will be created off Rows 1 and three. Alternatively create a chart based on Row 1, now copy row 3, select the chart, Ctr V. Now you have a chart based on 1 and 3. If you are oscillating between the 3 lines. Put this at the start of your formula for each line;

If(formulaResult=0,NA(),formulaResult)

The NA() will not be charted.

Anyways just some ideas to get you started.

Smallman

 

[Greg Mooney]

Hi Greg

Welcome to Chandoo

Will you only ever have to Chart Row 1 and 3. If so just hold ctr down as you choose your chart data and the chart will be created off Rows 1 and three. Alternatively create a chart based on Row 1, now copy row 3, select the chart, Ctr V. Now you have a chart based on 1 and 3. If you are oscillating between the 3 lines. Put this at the start of your formula for each line;

If(formulaResult=0,NA(),formulaResult)

The NA() will not be charted.

Anyways just some ideas to get you started.

Smallman

Hello Smallman, Some times I will have 3 to chart (I failed to clarify that Row 2 "could" be blank or could contain something that I want to chart. So if row 2 is blank I chart row 1 and 3 and if row 2 is not blank i chart all 3 rows...I hope that helps you help me?

 

[Smallman]

Yeah it does just refer to my previous post. If you apply the if statement you are there. Test it you will see that it works. The NA() part is the key. Let me know if you are still confused and I will dummy up an example.

Take care

Smallman

 

[Greg Mooney]

I think I am still confused a bit....sorry....

 

[Smallman]

Hi Greg

Have a look at the attached. I have provided two alternatives without the use of Dynamic Charts. Both Will miss the second item in the series in Different ways.

Take care

Smallman

 

[Sajan]

Hi Greg,
See if the following article helps you:
Charting with non-contiguous data

Cheers,
Sajan.