Graphing cumulative sign-up groups over time

[qawsqaws]

Hi,
I'm using Excel 2010 and need some help making a graph.

I have 3 groups of people (13-15, 16-18, 19-23), and for each 1 person in each group I have a time they signed up.

Sample input:
13-15
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=108><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 width=108 align=right>02/09/2011 11:25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 13:46</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 15:23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 15:23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 15:23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 15:33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=17 align=right>02/09/2011 16:22</TD></TR></TBODY></TABLE>

16-18
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=108><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=108 align=right>02/09/2011 15:39</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>03/09/2011 17:02</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>05/09/2011 15:13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>12/09/2011 13:23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>15/09/2011 13:42</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>15/09/2011 14:08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>15/09/2011 14:17</TD></TR></TBODY></TABLE>

19-23
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=108><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=108 align=right>02/09/2011 21:29</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>04/09/2011 08:21</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>04/09/2011 10:43</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>06/09/2011 11:01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>06/09/2011 15:06</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>08/09/2011 07:51</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>08/09/2011 14:26</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>08/09/2011 15:07</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>15/09/2011 14:52</TD></TR></TBODY></TABLE>

I also have a "sign-out" time for only a very few people, which would act as a "negative" against the cumulative total per group (so for example, if by 08/09/2011 07:51 I had a total of 14 entries for 16-18, then 2 minutes later someone 'signed out', I would want that to show on the graph as 13 total - so entries on the 'Sign out' list act as a -1).

What I am wanting is a simple graph that looks like a scatter chart with smooth curves, with date along the horizontal (using an interval of 1 day) and a cumulative total vertically; the graph should have 3 lines on, one for each group, which shows the cumulative total of number of people who are signed up to that group at that point in time (so I'd be looking at 3 rising curves crossing over each other).

I don't really understand pivot tables/charts, which I suspect may be needed - so can you please talk me through how I create my graph, and let me know any other info I need to provide? Thanks.

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Age</td><td style=";">Time In</td><td style=";">Time Out</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">13-15</td><td style=";">16-18</td><td style=";">19-23</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/25 10:49 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/22</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/23 9:26 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/23</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/22 4:57 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/24</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/25 3:26 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/25</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/23 4:25 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/26</td><td style="text-align: right;;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/22 3:43 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2011/9/27</td><td style="text-align: right;;">10</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/23 4:25 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/23 9:00 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/25 12:04 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/23 1:36 AM</td><td style="text-align: right;;">2011/9/25 9:20 AM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/26 10:38 AM</td><td style="text-align: right;;">2011/9/27 3:26 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/24 6:26 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/24 2:43 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/24 3:26 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/22 9:04 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/23 7:21 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/22 11:10 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/25 10:49 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/22 6:08 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/23 9:55 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/22 5:06 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/23 4:36 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/26 9:26 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/23 1:36 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">16-18</td><td style="text-align: right;;">2011/9/24 4:31 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">13-15</td><td style="text-align: right;;">2011/9/23 10:47 AM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">19-23</td><td style="text-align: right;;">2011/9/22 10:41 PM</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$B$2:$B$28<$E2+1</font>)*(<font color="Red">$A$2:$A$28=F$1</font>)</font>)-SUMPRODUCT(<font color="Blue">(<font color="Red">$C$2:$C$28>=$E2</font>)*(<font color="Red">$C$2:$C$28<>""</font>)*(<font color="Red">$A$2:$A$28=F$1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Select E1-H7 and create a line graph. That will give you what you want I believe.

 

[qawsqaws]

Hi Sal,
Thanks for the response - this almost gives me what I want, but the "Time out" part doesn't work correctly. If I add in some "time out" dates, it reduces the count for all the preceeding days too, i.e. if I add in a "time out" date of 12/09, it decreases the count by 1 on all days prior to 12/09 instead of just on that day and all following days.

In the formula you gave, in cell F3 I have adapted it to:
=SUMPRODUCT(($B$2:$B$200<$E3+1)*($A$2:$A$200=F$1))-SUMPRODUCT(($C$2:$C$200>=$E3)*($C$2:$C$200<>"")*($A$2:$A$200=F$1))

-is this correct? And then in G2 it's:
=SUMPRODUCT(($B$2:$B$200<$E2+1)*($A$2:$A$200=G$1))-SUMPRODUCT(($C$2:$C$200>=$E2)*($C$2:$C$200<>"")*($A$2:$A$200=G$1))

So, if I change the formula in F2 instead to:
=SUMPRODUCT(($B$2:$B$200<$E2+1)*($A$2:$A$200=F$1))-SUMPRODUCT(($C$2:$C$200<=$E2+1)*($C$2:$C$200<>"")*($A$2:$A$200=F$1))

then I think this is what I am wanting. (I added the +1 so I get a correct day value, i.e. if 3 people have a "time out" of sometime on 12/09, I want that day to show 3 less people)

Thanks for all the help!

 

[Sal Paradise]

Quite possible you're right -- especially if it's working that way. Glad you got it solved!