Excel Charting Challenge Q, special

[MacroEcon1337]

Hello!

I'm wondering if a certain chart can be created in excel. The base data (blue) contains information about bacteria concentration levels for various depth ranges/cohorts/blocks. I have no problem charting the base chart in excel. The problem is, I want to shade a specific region under the curve for a target band in red (see picture).

Example: I want to shade the region between 10,000 and 32,000 feet (in red). The image below shows exactly what I am shooting for (a red overlay between 10,000 and 32,000).

Anyone have an idea for how this chart could be created in excel? Where all i need to do is change the Target Range (shown as 10,000 to 32,000 below) and have the chart dynamically update? All ideas/suggestions are greatly appreciated!

- MC

[TABLE="width: 500"]
<tbody>[TR]
[TD]Depth Cohort[/TD]
[TD]Bacteria Concentration[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]0-5,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]5,000-10,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]10,000-15,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]15,000-20,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]20,000-25,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]25,000-30,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]770[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]30,000-35,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]35,000-40,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]950[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]40,000-45,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]45,000-50,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1225[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]50,000-55,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1150[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]55,000-60,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1150[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl4591, width: 146"]60,000-65,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1100[/TD]
[/TR]
</tbody>[/TABLE]


MC

 

[mole999]

I would think you need to replicate the range and just overlay it, though for columns you can set the colour by element. Check out the Peltier site, many workarounds there

 

[MacroEcon1337]

I would think you need to replicate the range and just overlay it, though for columns you can set the colour by element. Check out the Peltier site, many workarounds there

Gracias Mole - I was unaware of Peltier. But I see one option on there that looks very promising.

Leaving this thread open in any case anyone knows a solution that is more native/natural to Excel. But I feel modestly confident i could tackle using one of the Peltier workarounds.

One major problem that persists is -- if i plot the whole range out its like 50,000 rows and substantially increases my file size, slows calculation speed. I was hoping to somehow compress the data into an VBA array, then populate the chart via an array.

I will continue to pursue Peltier --- thank you for that lead,

MC

 

[mole999]

to make it dynamic, you might consider hiding columns of data and only plotting that which is visible

 

[MacroEcon1337]

Hidden or not, a 100,000 rows is crimping my style

(I believe rows is what i would need to hide, not columns --- since there are only 2-5 columns).

I will post back here with a final solution, if i find one. Ty again Mr. Mole