Create stacked bar graph with values on dynamic x-axis??

Greetings!

Attached is the image of what I'm trying to create. Its use it to provide a graphical explanation of how comparable real estate listings are performing relative to a Subject property. This will be used when meeting with potential homesellers prior to choosing an appropriate asking price when the home comes on the market.

Basically, I have real estate-related data containing 1-4 sold properties, 1-4 unsold properties, and 1-4 current active home listings.

What I need are essentially 3-4 graphs (see picture), with the SOLD graph on top, ACTIVE graph in the middle and UNSOLD graph on bottom. The left-most starting point on the x-axis should be the value of 'price_low', a variable I created in Excel; the right-most starting point should be the value of 'price_high'.

I can't seem to figure out how to arrange the data to make it display correctly... I don't want the vertical value to be displayed via the vertical component of the graph...all vertical height would be essentially equal to "1", with the x-axis value of each property corresponding to its price.

On the attached pic, you can see an example...the green lines would indicate 3 sold properties at $204,000, $206,000 and $209,000; the yellow lines in the middle graph would indicate active properties listed at $216,000, $224,000, and $229,000; the red lines in the bottom graph would indicate the appropriate Unsold values.

I've been wracking my brain on this for hours....could somebody help make sense of this for me? Thanks much!

:::a

Originally Posted by teylyn

Have a look at the attached.

Lots of fine tuning can still be done, but maybe you don't need a 1:1 replica of the screenshot.

Working with stacked column charts, dummy series for spacers formatted with no fill and no border.

Overlaid XY chart for the black grid, series markers formatted with no marker and error bars.

cheers, teylyn

Faaaaantastic, teylyn. Very-much appreciated. I've never used an XY chart and would've never thought to set it up like you have it. Brilliant. I'll play around with some of the settings and try to work in some of the dynamic formulas to the calculations. Overall, you pretty much read 98% of my mind on this.

Thank you!

:::a