Help needed creating charts based on date

[bitflip]

Hi guys, I have been tasked with creating a dynamic pie chart which can be altered to display data based on day month or year.

The database I have to work with uses a comma deliminated file which is quite large and continuously growing.

Could you guys help a out a newbie who has been up all night trying to figure this thing out

Here is what the data looks like once imported into Excel.



http://imageshack.us/photo/my-images/837/databaser.jpg/

 

[bitflip]

To add a little more information, I have been tasked with working on a Retierment Homes nurse call system logs to make them more user friendly. I am pretty much a newbie when it comes to Excel but I know how much this will help the manager to improve services to the residents.

I was thinking it would be helpful to have the data displayed in pie chart based on the Room and Call Type, perhaps with user selectable date ranges.


There are 5 different nurse calls types recorded:

ALARM CALL
DOOR ALARM
EMERGENCY CALL
PATIENT CALL
ZONE CALL

 

[Trevor G]

Welcome to the Forum,

Based on your sample data, do you want to create the chart from column C and F?

I have just done this no problem. Select First set of data then hold down control key and select second column then use Insert Tab and Pie Chart

Sheet10

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 36px"><COL style="WIDTH: 111px"><COL style="WIDTH: 92px"><COL style="WIDTH: 71px"><COL style="WIDTH: 141px"><COL style="WIDTH: 73px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Item</TD><TD>AT</TD><TD>CALLKIND</TD><TD>LOCATION</TD><TD>MSG</TD><TD>DURATION</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:27</TD><TD>PATIENT CALL</TD><TD>WARD-1</TD><TD>HOSPITAL ROOM H 25</TD><TD style="TEXT-ALIGN: right">00:00:01</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:32</TD><TD>PATIENT CALL</TD><TD>WARD-1</TD><TD>HOSPITAL ROOM H 25</TD><TD style="TEXT-ALIGN: right">00:00:11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:33</TD><TD>ZONE CALL</TD><TD>WARD-1</TD><TD>AMBER WING</TD><TD style="TEXT-ALIGN: right">00:00:05</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:34</TD><TD>PATIENT CALL</TD><TD>WARD-1</TD><TD>BED 205</TD><TD style="TEXT-ALIGN: right">00:00:36</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[bitflip]

Hi Trevor, thanks for the warm welcome. I was wondering will the chart you have produced automatically update as new data becomes available? The database they gave me to work with is literally hundreds of rows and growing. I have seen mention of offsets or something similar.

Sorry to get more complicated that is possibly needed, but is there some way to alter the chart depending on the date? I was thinking it would be good to compare days of the week or even months. For instance, we could check up on data from the weekends when the management is not on site and also monitor which days/weeks or months are busy times and require more staff.

 

[Trevor G]

First lets get the chart working and you can use Offset formula. If you requrie a sample workbook send me a private message with an Email address in and I will send you a sample workbook with different ways to create charts.

See sample below

Charting Using Offset Formula

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Dynamic Chart using offset formula</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD>Dates</TD><TD>Sales</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">40392</TD><TD> </TD><TD style="TEXT-ALIGN: right">02/08/2010</TD><TD style="TEXT-ALIGN: right">34</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">03/09/2010</TD><TD style="TEXT-ALIGN: right">45</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">05/10/2010</TD><TD style="TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">06/11/2010</TD><TD style="TEXT-ALIGN: right">67</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">08/12/2010</TD><TD style="TEXT-ALIGN: right">78</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">09/01/2011</TD><TD style="TEXT-ALIGN: right">89</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">10/02/2011</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">14/03/2011</TD><TD style="TEXT-ALIGN: right">342</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">15/04/2011</TD><TD style="TEXT-ALIGN: right">456</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">17/05/2011</TD><TD style="TEXT-ALIGN: right">500</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B7</TD><TD>=OFFSET($D$6,,,COUNT($D$6:$D$34))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

How this works?​

1. The Range has been set through a formula called Offset and Count Range("B7")=OFFSET($D$6,,,COUNT($D$6:$D$34))
2. I am also using named ranges for the Dates and Sales (Date1 and Sales1)
3. As you add more data to the table it will automatically add the new data to the Chart
4.I am setting the Format of the Axis to show only Month and Year. Select the Axis and then Ctrl + 1, Number category and Custom using mmm yyyy and clicking Add
5.I am setting to use random colours and also show values​

6. As you add more data in to the Dates and Sales it will add to the Chart

 

[bitflip]

Hi I have a question regarding this code:

=OFFSET($D$6,,,COUNT[COLOR=#008000]($D$6:$D$34)[/COLOR])

Why does the COUNT($D$6:$D$34) go only to row 34? Is this a user defined number... for instance why not choose 65536.

When you use the named ranges for dates and sales... do you use something similar to the above code?

 

[Trevor G]

That is just a sample to see it it helps you.

Which verison of Excel are you using? In 2003, 2007 and 2010 you can use a Table and the table can populate the chart and when more data gets added it just updates

I have created a workbook with several different methods to create charts which might help you.

Also for the drilling down option, have you thought of using a PIVOT Chart you can do some nice things with that with a little bit of extra code, so when you double click it will filter to another sheet and chart etc based on the selected data.

 

[bitflip]

I am running Office 2007 but need to keep this backwards compatible with 2003 as they use both versions.

I have made a pivot table but honestly it is a bit crude, to put it politely haha. I guess there is a first attempt for everything and not knowing much about Excel doesn't help.

Sure having a look at your workbook would be great as I am pretty much learning by example right now. The difficulty is making other peoples examples fit my problem. Kinda like putting a square peg through a round hole .

 

[geospatial]

When I do my charts I generally use a pivot table based on a dynamic range. That way all you have to do is refresh your pivot table and everything will be updated.

For the dynamic range I choose the entire database with

=offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),6)

Once you name that dynamic range you can make a pivot table from that range, and then a pivotchart which will give you the quick filter ability to make the charts you want.

 

[Trevor G]

I am running Office 2007 but need to keep this backwards compatible with 2003 as they use both versions.

I have made a pivot table but honestly it is a bit crude, to put it politely haha. I guess there is a first attempt for everything and not knowing much about Excel doesn't help.

Sure having a look at your workbook would be great as I am pretty much learning by example right now. The difficulty is making other peoples examples fit my problem. Kinda like putting a square peg through a round hole .

If you can view YouTube take a look at this tutorial to see if it helps

http://www.youtube.com/watch?v=-Uu2WqDxLdk


Also take note of geospatial repsonse as well.