creaet a graph from the hours of a time field
List the hours 0, 1, 2, .... 23, 24 somewhere (or group these in twos
or fours, but spanning from 0 to 24) - suppose you have this in column
X, with your date/times in column D and the number of tickets in
column N. Then you can put a formula like this in column Y:
=SUMPRODUCT((HOUR(D$1:D$2000)=X1)*(HOUR(D$1:D$200 0)<X2)*(N$1:N$2000))
and copy this down for as many rows as you have hour-blocks (except
for 24). This will give you a count of the number between each hour-
block.
I have assumed you have up to 2000 rows of data - adjust this if you
hve more (but you can't use a complete column prior to Excel 2007).
You can draw your graph from the results.
Hope this helps.
Pete
On Mar 10, 11:44 pm, "michaelxhermes via OfficeKB.com" <u39868@uwe
wrote:
Advice please
I have a column in a sheet with the time - date and time but formatted to
show the time
I have a number of tickets against each time - the time the tickets were
bought
there can be many records all for different dates
I would like to generate a simple graph showing how many tickets were
purchased in each hour period or even each 4 hour period
Can anyone please point me in the right direction please
I have looked the chart wizard and pivot tables but I cannot figure out how
to group and count the ticket numbers in to the time slots?
I hope this makes sense
thanks
Michael
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200803/1
|