Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have data like the following: Both Hour and Count are columns in an excel file.

Hour    Count  
17  79  
18  122  
19  123  
20  142  
21  150  
22  78  
23  15  
13  33  
14  33  
15  40 
16  33
17  56
18  46
19  35
20  67
21  65
22  45
23  36

What I want is to create a chart that shows over a period of 1 to 24 (hours) the total count. What's the easiest way to do this.

The chart should have a horizontal axis that runs from 1 to 24; and a vertical axis that goes from 0 on up.

In the case above the values should be combined like:

1 - 0
2 - 0
3 - 0
4 - 0
5 - 0
6 - 0
7 - 0
8 - 0
9 - 0
10 - 0
11 - 0
12 - 0
13 - 33
14 - 33
15 - 40
16 - 33
17 - 135
18 - 168
19 - 158
20 - 209
21 - 215
22 - 123
23 - 51
24 - 0
share|improve this question
    
Where is hour 1? –  soandos Mar 23 '12 at 0:04
    
Not there. I'd like excel to chart a zero. –  Chris Lively Mar 23 '12 at 0:41
    
You are going to have to be a lot clearer. Where is the data, what do you want on your axes, etc –  soandos Mar 23 '12 at 1:14
    
Do you mean by total that the hour 17 figures should be totalled together? Can this be done beforehand by the data source? –  Paul Mar 23 '12 at 2:07
    
@Paul: totalled together, can't be done before hand. –  Chris Lively Mar 23 '12 at 4:16
show 1 more comment

1 Answer

Ok,

Assuming the hour is in column A and the count is in column B.

In column D, put the numbers 1-24

In column E, put the following formula:

=SUMIF($A$1:$A$18,CONCATENATE("=",D1),$B$1:$B$18)

This will product your second table above.

Then add a chart, and use $E$1:$E$24 as the data range, and $D$1:$D$24 for the categories. In the image below I have ordered the original data, but it isn't necessary.

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.