formula

[arnoldmiranda]

Hi,
I have a series of datapoints which are basically the "in" time for my volume, this inflow is through the day and we have a time stamp that recods it in "4/14/11 1:52 PM" format

I wanted to see an arrival pattern, so basically the volumes that have come in per hour, so for each date in a month I have 24 hours, the formula I wanted is to calculate the incoming volume in each hour

Any help would be appreciated

 

[excelsishya]

Would using pivot on your data and grouping by year ,month,day and hour will
that work for you.

 

[arnoldmiranda]

Unfortunately, not something I am aware of. I did try using a pivit but my knowledge is limited

 

[excelsishya]

See sample below,

ip

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 148px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="font-weight: bold;">Time</td> <td style="font-weight: bold;">Volume</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">4/14/2011 13:52</td> <td style="text-align: right;">20</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">4/14/2011 14:52</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">4/14/2011 16:52</td> <td style="text-align: right;">30</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">4/14/2011 16:59</td> <td style="text-align: right;">50</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="text-align: right;">5/14/2011 13:52</td> <td style="text-align: right;">60</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: right;">5/14/2011 14:52</td> <td style="text-align: right;">80</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">5/14/2011 16:52</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="text-align: right;">5/14/2011 16:59</td> <td style="text-align: right;">20</td></tr></tbody></table>
Run pivot on above data alt+d+p

finish

add time colum to row

right click
group
year month day hour

then add volume to data/value field.


<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 101px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 51px;"> <col style="width: 35px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Sum of Volume</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Years</td> <td>Months</td> <td>Days</td> <td>Time</td> <td>Total</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">2011</td> <td>Apr</td> <td style="text-align: right;">14-Apr</td> <td style="text-align: right;">1 PM</td> <td style="text-align: right;">20</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">2 PM</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">4 PM</td> <td style="text-align: right;">80</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>
</td> <td>May</td> <td style="text-align: right;">14-May</td> <td style="text-align: right;">1 PM</td> <td style="text-align: right;">60</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">2 PM</td> <td style="text-align: right;">80</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">4 PM</td> <td style="text-align: right;">30</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Grand Total</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">280</td></tr></tbody></table>