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

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
Philip-David TruscottAssistant ProfessorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Philip-David TruscottAssistant ProfessorAuthor Commented:
Dear Teylyn,
Here is an example file.
Best Wishes,
PhilHoursOfWork.xls
0
 
Philip-David TruscottAssistant ProfessorAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Philip-David TruscottAssistant ProfessorAuthor Commented:
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
 
Philip-David TruscottAssistant ProfessorAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
All Courses

From novice to tech pro — start learning today.