Problem merging two charts (Scatter Bubble + Line)

[max_pedreira]

Hello,

Hope you're all great!

For the past few days I've been scratching my head and scraping the internet for an answer to a problem that seemed seemingly simple.

What I've been trying to do is combine a scatter and a line graph into one!
Below is what I'm trying to achieve.

But so far I've been unable to do it...

Below is the data I want to transpose to the graph.
The blue table is for the bubbles: "Sales" for the size of the bubbles, "Discount" for the y axis, and "Level" for the x axis.
The orange table is for the line: "Discount" for the y axis and "Level" for the x axis.

My goal with this graph is to visually despite which customers fall below the discount threshold for each level (and which don't).

Can someone help me?

Many thanks in advance!

 

[myall_blues]

I don't believe it is possible to combine bubble plots with other forms. That said there is a way to achieve what you're after but it is a little cumbersome.

First create the bubble chart from your first table - I changed the column order just to make it easier but you do you.
Next, create three separate new series, each consisting of two points, from the data in the second table. To do this, right click in the chart area and click 'Select data...', then on the left hand side under legend entries choose Add. Then add three more series each consisting of two rows as below.

For the first it is rows 2 and 3, for the second rows 3 and 4, and for the third rows 4 and 5.
On the chart, click the + sign on top right, click the arrow next to Trendline, click Linear and then choose Series 2. Repeat for series 3 and 4.
Format each trendline series as you want them to look.

Regards

Murray

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Problem merging two charts (Scatter Bubble + Line)

Hello, Hope you're all great! For the past few days I've been scratching my head and scraping the internet for an answer to a problem that seemed seemingly simple. What I've been trying to do is combine a scatter and a line graph into one! Below is what I'm trying to achieve. 866265

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.