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

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

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

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

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.

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

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

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

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

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

I have also made a column chart with the resulting frequency table.

Best Wishes,

Phil HoursOfWorkWithBins.xls

All Courses

From novice to tech pro — start learning today.

Then you'll create a set of cells that calculate the mean (average) and median of the values in the "hourly" sheet:

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

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.

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:

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

Lastly, change the column width of your bars (like 50-100%) and add a legend to note the lines:

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