Link to home
Start Free TrialLog in
Avatar of ResourcefulDB
ResourcefulDB

asked on

Excel histogram question

Hi All,

I am open to any suggestion to make a pretty histogram in excel. The buildin function from tools ->data analysis does work, but the x axis does not have good layout.

Thanks,
RDB
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hello ResourcefulDB,

Can you describe what you mean?

Regards,

Patrick
You can influence the X axis on a histogram by how you define your bins. After the chart has been created, you can format the X axis like any other chart.

Why don't you post an example and explain what you would like to see instead.

Avatar of ResourcefulDB
ResourcefulDB

ASKER

Sure. For example we have 100 numbers, they all between -10 to 10. We want to find out how many of them between -10 to -5, -5 to 0, 0 to 5, 5 to 10. and plot a histogram.

We use the tools->data analysis->histogram and select the 1000 number as data range. select the range -10,-5,0,5,10 as bin range. click on the chart output. Then excel does work for us and have two ouputs. one output is a table with two column, one column is bin, the other one is frequency. the other output a chart with the histogram.

First we take a look at the table. We notice that the column bin have the following numbers: -10, -5, 0,5,10, More. The frequency corresponding to 10 and More are always 0. The frequency in the -5 row actually means the frequency of the number fall in between -10 and -5. The frequency in 0 row actually means the frequency of falling between -5 and 0 and so on. It is confusing. but it is ok, we can always manually change -5 to something like "-10 to -5". So the first question is if there is a way to make the table appear not confusing without the manual process.

Secondly, we look at the chart, which is based on the data from the table. In the X axis, again, we have -10, -5, 0, 5, 10, More. The bin above -10 and More is always 0, which is not surprise from the table. Now that the bin above -5 is actually the frequency falling between -10 and -5. So, I am thinking if there is a way to move the label of X axis as a whole to the right half tick, so the bin is not just located above the number -5 but locate between -10 and -5, thus making a better visual.

RDB.
With regards to the chart, you can set the Y axis to cross the X axis at a certain value. Open the Format dialog for the X axis, and on the scales tab set the Value for "Vertical axis crosses at" to Axis Value and enter the point you prefer.
Nope, the scale tab does not  help me to move the label of x axis to the right side a half tick.
In the X axis format dialog, use the setting to position Axis on tick marks instead of between tick marks.

hth
where do we find that setting? in the scale tab? it does not look like I have this option when I open my format Axis. maybe a later version of excel has it. mine is excel 2002.

can you tell me where exactly we can find it and what version of excel you have?

Thanks,
RDB
I'm referring to Excel 2007/2010. It would help if you indicated your Excel version when you ask a question, because there are quite significant differences between the versions, especially in the charting dialogs. The earlies version I have available to me is XL 2003, but I will only be able to look that up in about 12 hrs from now. I will post back then.
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial