Solved

Super impose a line graph on a bar graph

Posted on 2014-10-01
6
169 Views
Last Modified: 2014-10-02
Hi,

I have some data from 5 regions on a bar graph, it shows average values of deals for each region

I would like to somehow show the median values on the same graph to visually show the difference between Average deal size and median deal size

I have attached the file - its chart Avg vs Median and the data for median is in tab "Data" N16 - We are only interested in Totals for each region "ASP, EU, LA, ME, NA"

Many thanks
RD-Analysis-light.xlsx
0
Comment
Question by:Seamus2626
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
The total median deal size is actually in cell O14 on the "Data" sheet.  Do you want to see a horizontal line showing that value on the Avg. vs. Median chart?

-Glenn
0
 

Author Comment

by:Seamus2626
Comment Utility
Yep, exactly....thanks Glenn
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
Here's my first attempt.  I have a gut feeling that there's a better method, but I was trying avoid modifying your original data set if at all possible.

It wasn't possible to create a line over the original chart because you used separate series.  So I created a new chart, first as a line chart for the regional data, then added a second series for the median.  I changed the chart type to column for the regional averages and changed the fill for each bar.   I had to manually create the "Median - 16.1" label, but I think there's a trick to getting that value added there.
line and column chart-Glenn
EE-RD-Analysis-light.xlsx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:Seamus2626
Comment Utility
Thats brilliant Glenn! Please though, if there is a better method re-arranging my raw data, i would love to see it!
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
I did make a couple of modifications.  First, I added a series label cell for the Median Line above the series (Data sheet, G14).  Second, I created a standardized formula to pick up the median value on for those region-categories NOT "CMB", "FI", or "GBM".  These are in cells G16:G35 on the Data sheet.  

-Glenn
EE-RD-Analysis-light.xlsx
0
 

Author Comment

by:Seamus2626
Comment Utility
Thats very kind of you Glenn!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now