• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Need Excel Chart Help

I have a set of data that looks like the following.   I want to  create a time series chart of the dogs vs. the cats.  I expect I may dynamically need to create this.

animal            weight (lb)        Weigh Date  
-----------------------------------------------
dog                12                     25 JUN 07
cat                 9                       27 JUN 07
dog                45                     14 JUL 07
cat                 5                       15 JUL 07
dog               70                      23 JUL 07

I would use the pivot chart except I can't create a time series chart with a pivot table.

How do I do this?  Example?  
0
tmonteit
Asked:
tmonteit
  • 6
  • 5
  • 2
2 Solutions
 
Patrick MatthewsCommented:
I would try converting your dates to a string like this:

=TEXT(C2,"yyyy-mm-dd")

and using that new column for your "time series" axis.  It will sort in the right order for you.
0
 
harfangCommented:
For MS-Chart, the data should look like this:

Date              dog       cat
25 Jun 07        12
27 Jun 07                      9
14 Jul 07         45
15 Jul 07                        5
23 Jul 07         70

This is done with a query like:

TRANSFORM Last([weight (lb)])
SELECT [Weight date]
FROM YourTable
GROUP BY [Weight date]
PIVOT animal

Use that as Row Source for a standard MS-Chart chart. You can select time-series if it doesn't guess automatically, or use an XY scatter plot if everything fails.

Cheers!
(°v°)
0
 
Patrick MatthewsCommented:
Markus,

Oh ye of little faith :)

Assuming what we want is the weight for dog on the most recent (latest) date, this array
formula gets it:

{=MAX(IF((A2:A6="dog")*(C2:C6=MAX(IF(A2:A6="dog",C2:C6,""))),B2:B6,""))}

Being an array formula, enter it without the curly braces, but use Ctrl+Shift+Enter instead
of Enter to finish it off.

Regards,

Patrick
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
harfangCommented:
Oh, this was an Excel question? Must have been tired -- (^v°)

Still, this seems to be the answer to http:/Q_22714871.html and does nothing towards creating a chart...

tmonteit,

> I can't create a time series chart with a pivot table.

That is *very* annoying, isn't it? I don't understand why they didn't make that feature a priority, given the target users of Excel...

Anyway, you can create a pivot table, then select it, and paste it as values. Then you can create a normal chart, allowing time series, based on that data.

Another solution would be to create columns starting in D2:

    = IF(D$1=$A2,$B2,#N/A)

Type "dog" in D1, "cat" in E1, and drag the formula down and to the left. Then you can use the range C1:E6 to create a normal chart, allowing time series.

Good luck!
(°v°)
0
 
tmonteitAuthor Commented:
I like the idea of some VBA code that automatically builds the Excel chart.  

How would I dynamically build a series from a list and put it into a chart?
 
0
 
harfangCommented:
Just as I said.

You can also build the actual expression of a new series. Something like this:

    With ActiveChart.SeriesCollection.NewSeries
        .Values = "={1,2,3}"
        .Name = "=""new"""
    End With

But I guess this isn't exactly what you want, is it?
(°v°)
0
 
tmonteitAuthor Commented:
harfang, I think you're hitting close...

Here's how I envision the code working.  I start with a table of data:

note this is a named  called "raw_animal_data".

animal            weight (lb)        Weigh Date  
-----------------------------------------------
dog                12                     25 JUN 07
cat                 9                       27 JUN 07
dog                45                     14 JUL 07
cat                 5                       15 JUL 07
dog               70                      23 JUL 07

And a button that says "make chart".

How do I do the following with VBA?

1.  Make a chart?
2.  Grab all the 'cat' data and 'dog' data and build 2 series.
3.  Populate the series on the chart as 2 lines in a timeline graph.
0
 
harfangCommented:
That's another "set of sleeves", if you forgive my French.

Which method are you opting for? Creating the new columns? ... generating a Pivot and retrieving the values there? ... building a VB script to generate stand-alone values for a chart?

The basic approach to building macros in Excel still is to record while you proceed with your idea and then adjust that (heavily, but it still gives you pointers to the proper syntax).

I your shoes, I would probably not go for a full macro approach, unless you need to repeat the process every day, naturally. Do it by hand first, a couple of times, record it also several times to get a feel for what you are trying to do.

(°v°)
0
 
tmonteitAuthor Commented:
If VBA were as easy as Java a script wouldn't be a problem my script would be done by now.  I would build a hashmap indexed by 'cat' and 'dog' and whatever other animal i found.  The map would reference a series of data points.  I could then plot each set of data.  Just the excel piece of this makes it very difficult.

The problem is really frustrating.  I simply want a timeline chart of this data that has separate lines for 'cat' and 'dog'

animal            weight (lb)        Weigh Date  
-----------------------------------------------
dog                12                     25 JUN 07
cat                 9                       27 JUN 07
dog                45                     14 JUL 07
cat                 5                       15 JUL 07
dog               70                      23 JUL 07

The macro is appealing because I add to the raw data every day and I want the graph to automatically update with a new time series point.   It doesn't require me to have extra spreadsheets that do wierd things.

Is there another way I should be doing this?
0
 
harfangCommented:
Have you tried my suggestion at {http:#19554641}? If not here is the relevant spreadsheet. Seems easy enough (less than 2 minutes).
https://filedb.experts-exchange.com/incoming/ee-stuff/4143-Q_22715584.zip

> Just the excel piece of this makes it very difficult.

Yes. But you are probably trying to use Excel as a programmer's development platform. It isn't. But it's very good for quick exploration and analysis of data. Truly, less than two minutes. Took me longer to write this post and to upload the file than to recreate it.

(°v°)
0
 
tmonteitAuthor Commented:
harfang,  the ease of your suggestion didn't make sense until U posted the file.    Wow!  exactly what I needed.  Thanks.

Also.  I didn't know you could share files with EE.  that's the first i've seen that used.  how did you manage it?
0
 
harfangCommented:
Thanks!

ee-stuff is some sort of spin-off from EE; I don't know the details exactly. Anyway, you can connect to http:www.ee-stuff.com and use your EE login. The "Expert" tab has a link to upload a new file (paste the full question URL in the first box, browse for your file for the second box). The list of allowed extensions is given just above.

Quite convenient, as the files are linked to the EE question numbers. (And we can hope that future readers will still have access to the files).

Cheers!
(°v°)
0
 
tmonteitAuthor Commented:
This was a very helpful response.  I've split points among most helpful responses.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now