Solved

In excel how do I show a histogram with a vertical marker for the mean and median values

Posted on 2014-09-15
10
8,617 Views
Last Modified: 2014-09-18
Dear Experts,
Let's say I have a column of 800 values and I want to display them in the form of a histogram.

That seems fairly simple.  I can just select the cells and then click on "Insert --> Charts -->Column Chart"

However, I want to display vertical lines on the histogram to indicate the mean and median values.

How can I achieve that?
Best Wishes,
Phil
0
Comment
Question by:Philip-David Truscott
  • 5
  • 4
10 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 40324260
Hello,

I take it that with "histogram" you are not plotting all 800 data points, but an aggregation of the data.

You can use a combination chart with an XY chart plotted on the secondary axis to create lines.  The exact steps would be easier to describe if you could post a sample file and also let us know your Excel version, since there are some differences. Also, it would help if you already have formulas for the mean and median calculation in place.

cheers, teylyn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40324314
Well, a histogram would likely be a derivative of the raw data from the 800 cells - say a distribution set of values in a set of bins.  Displaying the mean and median of the original values wouldn't be really practical...where would you plot them?
histogram plot
As shown here, it really isn't even possible to plot such lines...assuming that's what you're asking.

However, if you wanted to plot the mean and median values as horizontal lines across ALL the raw data (assuming you create a chart as you described in the original question) then this isn't hard; you just set up a two additional columns of data that return the AVERAGE (mean) and MEDIAN of the original data and them plot them using a different chart type.
mean median - line plot
If you'd like more details, please specify which type of plot you're looking for and I'll be glad to elaborate.

Regards,
-Glenn
0
 

Author Comment

by:Philip-David Truscott
ID: 40329072
Dear Teylyn,
Here is an example file.
Best Wishes,
PhilHoursOfWork.xls
0
 

Author Comment

by:Philip-David Truscott
ID: 40329088
Dear Glen Ray,
The sort of display I am after is more like your TOP image, the one with the vertical blue lines.  

Could I do something like this:

a) Have  a column with all my raw data (say in column a). Let's say the values
b) Have a column just with the mean and median (say in column b)

Is there some way to label the data in column b so that it appears different?  For example a vertical blue line as in your example?
Best Wishes,
Phil
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329110
Philip-David,

So, reiterating Teylyn's and my question, are you wanting to plot a histogram of this data, and, if so, what are your bin values?  There are none in your example data.

Although it's possible to combine line and bar charts, I'm not sure how an X-Y chart can be overlaid on a bar chart - which is what's needed to generate the vertical lines.

Also, I'm not sure how a hybrid bar chart could be created to show two thin, vertical bars representing the mean and median.  But I'll give it a go.


Regards,
-Glenn
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329162
This is the closest I've been able to produce so far:
 hours of work - histogram and mean-median lines
Even though I was able to combine a bar chart with an X-Y chart, the issue becomes obvious when noting the position of the bars on the x-axis; they do not line up ON the bin values, but rather in between.  That may not be a big deal; in some sense, this is correct as the bars represent the number of values "in between" each x-axis point.

It looks like you're using Excel 2003 or earlier, so the histogram functions are set up for that.

Regards,
-Glenn
EE-HoursOfWork.xls
0
 

Author Comment

by:Philip-David Truscott
ID: 40329199
Dear Glenn,
I am not sure I know how to go about combining a bar chart with an X-Y Chart.  Could you give more detail?

The effect you have achieved looks absolutely fine.
Best Wishes,
Philip
0
 

Author Comment

by:Philip-David Truscott
ID: 40329252
In answer to the question about bin sizes I have shown bin sizes in this new version of the spreadsheet.

I have also made a column chart with the resulting frequency table.
Best Wishes,
Phil HoursOfWorkWithBins.xls
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40329316
Generally-speaking, you'll first create an X-Y chart using the histogram bin and frequency values for your x and y components.  This will generate a chart like this (choose the example x-y plot with lines only  and no points)
Step 1 - set up XY chart on histogram data
Then you'll create a set of cells that calculate the mean (average) and median of the values in the "hourly" sheet:
Step 2 - Create Mean and Median valuesThe formulas are:
=AVERAGE(hourly!A:A) (for mean)
=MEDIAN(hourly!A:A)  (for median)

To get these to plot as two vertical lines you'll need y-values of zero and a high number, higher than the largest histogram frequency returned.  I chose 300 in this example.

Next, you add new series to the chart.  Here's the set up for adding the Mean; you'd do a repeat of these steps to add the Median:
Step 3 - Add Mean and Median series
Here's the key step:  Right-click on the original histogram series and select "Format Data Series" from the shortcut menu.  Change the "Plot Series On" setting to "Secondary Axis".  An identical axis will appear on the right.  Close this window.
Step 4 - Secondary Axis for histogram data
While the series is still selected, right-click on any point again, and select "Change Series Chart Type".  Find the column type on top of the list and select it.  You'll see something like this:
Step 5 - change series chart type
Now, change the x-axis scale by double-clicking it and setting the minimum to 0, the maximum to 100, the major unit to 5 (same as bin increment):
Step 6 - Set x-axis
Lastly, change the column width of your bars (like 50-100%) and add a legend to note the lines:
Finish - formatting and labeling
I did all this in Excel 2010, so I might be missing some parts of the chart interface, but it should be pretty similar.

Regards,
-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40329324
By the way, I missed another step:  Make sure that both Primary and Secondary y-axes are the same range (0 to 300).  I forgot to scale them in the above steps.
0

Featured Post

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

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

12 Experts available now in Live!

Get 1:1 Help Now