Excel histogram question

ResourcefulDB
ResourcefulDB used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello ResourcefulDB,

Can you describe what you mean?

Regards,

Patrick
Most Valuable Expert 2011
Awarded 2010

Commented:
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.

Author

Commented:
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Most Valuable Expert 2011
Awarded 2010

Commented:
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.

Author

Commented:
Nope, the scale tab does not  help me to move the label of x axis to the right side a half tick.
Most Valuable Expert 2011
Awarded 2010

Commented:
In the X axis format dialog, use the setting to position Axis on tick marks instead of between tick marks.

hth

Author

Commented:
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
Most Valuable Expert 2011
Awarded 2010

Commented:
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.
Most Valuable Expert 2011
Awarded 2010
Commented:
Not sure if Excel 2002 has it, but in Excel 2003 there is an option "Value (Y) axis crosses between categories" on the Scale tab of the X axis format dialog. This will shift the tick marks from between the data points to right underneath the data points.
hth

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial