-

Jump to content



Sign up for a free account!

Signing up for an account is fast and free. As a member you can join in the conversation, enter contests and you won't get the popup ads that guests get. Click here to create your free account.

Photo
- - - - -

Excel question: Charts


This topic has been archived. This means that you cannot reply to this topic.
7 replies to this topic

#1 of 8 Vince Maskeeper

Vince Maskeeper

    Lead Actor

  • 6,504 posts
  • Join Date: Jan 18 1999

Posted March 10 2004 - 03:30 AM

Guys,

I have an excel table where I have charted my weightloss progress. In the same chart I have also counted calories, fat, carbs and physical activity (along with weight and measurement).

The table has a row for each day since January 23rd, and a column for the various things I was keeping track of (Waist, Stomach, Chest, neck, Weight, Treadmill Time, Distance, MPH, Situps, Pushups, Calories, Fat, Carbs).

Things like calories I chart everyday, however things like measurements are done once a week or so (sometimes less than, sometimes more- was just done when I had extra time in the morning).

I would like to take this data up to this point and make some line/area charts. For example, I'd like to make a chart with measurements and weight over the course of the last 2 months.

However, since i only have values for one row for every 6-7, everytime I try to make a chart it counts these empty values as 0-- making the chart look more like a pulse monitor (all the way at 0 for a week, then jumps to 235, all the way at 0 for a week then jumps to 233, etc.)

I don't know excel very well- but I would like to find a way for it to ignor the empty values, but kind of keep the space for those days-- so i can see the full 60 days but it will only plot points for the days I have values.

Any help?
Need an introduction to home theater? Check out our FAQ and Primer!!

#2 of 8 Andrew Pratt

Andrew Pratt

    Producer

  • 3,813 posts
  • Join Date: Dec 08 1998

Posted March 10 2004 - 03:55 AM

Vince you could use a bar chart instead of a line.

#3 of 8 Jeff_Blanch

Jeff_Blanch

    Agent

  • 32 posts
  • Join Date: Dec 12 2001

Posted March 10 2004 - 04:37 AM

One way to do it is to plot as an XY scatter. If you want to see a line you can then add a polynomial trendline. It won't be perfect, but if you want to keep the days, it will always think the empty days are data points.

#4 of 8 Vince Maskeeper

Vince Maskeeper

    Lead Actor

  • 6,504 posts
  • Join Date: Jan 18 1999

Posted March 10 2004 - 10:37 AM

I'll just do it by hand, no biggie.

Seems odd that technology can't help me, lol.

-Vince
Need an introduction to home theater? Check out our FAQ and Primer!!

#5 of 8 Mike Sogge

Mike Sogge

    Stunt Coordinator

  • 85 posts
  • Join Date: Nov 24 2000

Posted March 10 2004 - 02:23 PM

Using the XY scatter plot with interconnecting lines...

- For the data sets which have values for each day, create a series that uses all the dates for the X value, and all the data values for the Y values.

- For the data sets which have sporadic values, create a series that has uses only the dates which have corresponding data values for the X values (make multiple cell selections with the CTRL key). Do the same for Y values.

Doing this will allow you to track multiple datasets on the same chart w/o creating goofy results.

#6 of 8 Dan Mercier

Dan Mercier

    Stunt Coordinator

  • 56 posts
  • Join Date: Aug 24 1999

Posted March 11 2004 - 07:35 AM

Vince, if you want you could always post the spreadsheet
data here and some of us could take a crack at it for you.
I think Mike pretty much has listed what you have to do but
I also have a few idea's I would need to try.

You don't mind posting your weight to all of us do you?
Just kidding. I'll play with this when I get off work as I
need to do some graphing for other spreadsheets I have any.
I'll get back to you with anything I find.

Dan
Current Home System.
Dead Infocus LP350, Pioneer 525 dvd, XBOX, Pronto, Denon AVR 3300, Energy C8's
Energy AC300 (center), Bose AM5 surround, All of which sits in the basement unused until i replace the projector.

#7 of 8 Vince Maskeeper

Vince Maskeeper

    Lead Actor

  • 6,504 posts
  • Join Date: Jan 18 1999

Posted March 11 2004 - 08:02 AM

Well, the sheet is here:
http://www.musiciana...om/htf/diet.xls

and I'm looking to basically create this
Posted Image


and then a second chart for just weight, but also incuding ALL dates, not just ones with values.
Need an introduction to home theater? Check out our FAQ and Primer!!

#8 of 8 Kelvin Tucker

Kelvin Tucker

    Stunt Coordinator

  • 59 posts
  • Join Date: Sep 17 1999

Posted March 11 2004 - 05:43 PM

Charting Tips
I was able to plot a line chart correctly by changing your black entries into "=NA()" entries. Seems that Excel will not plot those, in general.

This may be what you need to almost get there. M$ does not make things like this easy, and the only way I know to go all the way is to program it in VBA ....
-K-