Drawing a histogram with large data

[Mary22]

Hi everybody,

For better explanation of question, please take a look at the attached sample. I would like to draw a histogram of count of case (y axis) vs length (x axis).
Actually, I would like to show the count of cases that their lengths are 200-400, 401-600, 601-800, 801-1000, 1001-1200, 1201-1400, > 1400.
(the number near the word of case is not matter. As I explained, the counts of cases that have length say 200-400 is my interest).

It's obvious that these number located on x axis. However, I don't know how I can to show this distribution. Could you please help me out on this issue?


Thanks so much in advance and looking forward to hearing from you.

[MrShorty]

I would suggest a tutorial like this to start: http://www.wikihow.com/Create-a-Histogram-in-Excel

[Mary22]

Thanks for your response. I know drawing simple histogram like what explained in your link. My issue is a bit complicated, please take a look at the attached sample and help me out if it's possible.
Thanks

[MrShorty]

I do not understand what is "complicated" about your issue. It should be as easy as:

1) Enter the bins values as explained in the tutorial (400, 600, 800,...).
2) Call the histogram tool and follow the instructions for entering the bins range and the data range, and the output options as described.

I might suggest that you add a histogram using these steps and then explain why that is not correct, and what you want it to do instead. I think that will help us understand what you are looking for.

[popipipo]

Take a look at this.

[Mary22]

Thanks friends. As I explained in the post, I would like to have 200-400, 401-600, 601-800, 801-1000, 1001-1200, 1201-1400, > 1400 in the x axis (not 200, 400, ..). My mean is something like the attached histogram, (assuming contig is the same with case (in my sample) in the picture). I'll be happy to hear from you.

Best

[MrShorty]

In addition to the bins column (400, 600, ...), you can add another column of text strings ("400-500", "501-600",...). The histogram utility will need to bins column to perform the counting. Then, you can edit the chart to use the column of text strings for the horizontal category axis data (see the Select Data dialog on the Chart design menu).

If you don't want to hand enter the axis label strings, you can use the concatenate function. Using popipipo's example, column E might have a function like =concatenate(F2,"-",F3) and copied down.

[Mary22]

Thanks MrShorty. Please accept my apologize if the question are so basic for you; it's a big for me as a beginner.

Based on what you suggested, just the format of text on x axis of popipipo's example was changed as I wanted (see attached picture). However, I would like to make sure if the meaning of the graph is right, for example we can say about 16000 cases have the length of 200-401? It's very important for me.

One thing that popipipo missed in the graph was showing the cases that their length are more than 1400, which have to show as "> 1400". Could you please help me to show this? Again, the correct number of cases with the length of more than 1400 is critical, here.

Thanks for your help

[popipipo]

Is this better?

[Mary22]

Many thanks friend, it's exactly what I'm looking for. Your help was really kind of you....I approved it.

[MrShorty]

Since it appears that your data are sorted on length:

row 2 to row ~16000 is 200 to 400 and
the 1400's start about row 48000 to the end (row 57000 or about 9000 rows),
so, popipipo's COUNTIFS() function seems to be correct.

[popipipo]

Yes it is correct!

[Mary22]

Yes Friend, I checked the correction using simple filtering. Thank you very much for following the issue.