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
9,814 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:Ingeborg Hawighorst
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

9 Experts available now in Live!

Get 1:1 Help Now