Align two graphs of the weight development of my two puppy dogs (Rottweilers)

[Swan]

Hi all,

We are the happy daddy & mummy of two little Rottweiler puppies (next to two seniors, an 11 years old gentleman and an 11 years old Mrs ).

We are tracking the weight development of both of the little boys (one is now 7,5 months old, the other one is 6,5 months old). There is a difference both in when they came to our house (the 'oldest' one came quite early after being born, the second little boy stayed with his breeder a little longer before we found him and adopted him), as well as to when we did weigh them (sometimes every three days, then once a week, then, as today, only again after 12 days). I want to create meaningful graphs about it, which take care of the date differences. I would like to be able to compare the two little boys with eachother, as far as their weight development goes (so, ideally, I would like two lines in one graph).

Currently I have two tabs in which I keep the data. Unfortunately, my knowledge of Excel is way to basic to find out how to do what I want. Would anybody perhaps be willing to give me some tips? I'd be in your debt for that

I have attached my Excelsheet for review.

Thank you in advance for any help ,

Bye,

[ben_hensel]

Hmm... so, you have an interesting situation from a charting perspective.

Two data series, where:
x1: time (date)
y1: time-dependent value
x2: time (date)
y2: time-dependent value

So the x1 and x2 are similar values that overlap and can easily be graphed together, but we can't simply ignore x2, because for t1 = x1 = x2 there is a y1, but no y2, and vice versa; t2 = x1 = x2; x1 doesn't have a value but y2 does.

Anyway I pulled copies onto one tab, and then used VLOOKUP to put y1 and y2 against a wider date column, which spans x1 and x2. It's not really what I'd call an elegant solution but it's pretty straight-forward, I think.

[Swan]

Hmm... so, you have an interesting situation from a charting perspective.

Two data series, where:
x1: time (date)
y1: time-dependent value
x2: time (date)
y2: time-dependent value

So the x1 and x2 are similar values that overlap and can easily be graphed together, but we can't simply ignore x2, because for t1 = x1 = x2 there is a y1, but no y2, and vice versa; t2 = x1 = x2; x1 doesn't have a value but y2 does.

Anyway I pulled copies onto one tab, and then used VLOOKUP to put y1 and y2 against a wider date column, which spans x1 and x2. It's not really what I'd call an elegant solution but it's pretty straight-forward, I think.

Wow Ben, that's fast and exactly the graph I was looking for; thank you very, very, much

But I have to admit, the Excelsheet was kind of a mess, so I will clean it up a little. Might I ask you to have a look at it again after that? "I'll be back", as a certain guy used to say

Thanks again Ben, much appreciated (!)

[Swan]

Well, that was faster than I would have thought, Ben

I've cleaned up the Excelsheet in order to delete all 'rubbish' from it. In addition, I have added a new column, which I call 'slope' and am hoping I picked the right word for it (I'm Dutch ). What I mean with that is the speed of growth, as we want to try to see if their growth is slowing down or not (it is my hypothesis it will start to slow down from the age of 8 months old). This 'slope' is simply the difference in weight from week to week. It might be nice if this could be added to the graph.

Once again thank you very much; I will take your sheet and study it to try to see what magic it is that you did

[ben_hensel]

What you've got isn't the slope, it's the delta-Y, the change in the y value for each term to next. Slope also involves the delta-X; it's basically deltaY/deltaX, formulated (y2 - y1) / (x2 - x1). Well actually it's more like, the least squares sum of the distances of the data points perpendicular to a theoretical line but whatever, because:

Excel already has a SLOPE() function so you don't have to do all that math.

That delivers basically the same information as adding a linear trendline.

You can put those values on the same graph but because they're like an order of magnitude smaller you should probably add a secondary vertical axis.

I slapped some stuff onto the tab with more data (see attached) just to like kinda show what those look like. It's kinda busy but whatever.

"When their growth slows down..." well, you can do stuff like, find inflection points with numerical methods pretty easy when you have nicely paired data columns.

For putting the two series of data together, I took advantage of the !N/A error from the VLOOKUP getting ignored by the chart to keep the formulas really simple, but if you're going to do more calculation, that's not really a good idea.

Anyway there's a lot of directions you can go with this, it's probably a good idea to imagine what you want the chart to look like and THEN build towards that, rather than moving the goalposts around.

[jpr73]

Hi,
May be a solution with pivot table and pivot chart
The data from SAM and MAX used for PT are grouped in table to be dynamic
Next use the pivot table wizard to assemble the data and build the pivot table
Then add the pivot chart and format with primary and secondary axis
You can offset the curve of the secondary axis by increasing the maximum value for better reading of the chart
Hope this helps
Best regards

[Swan]

Thank you both for your help, much appreciated

I will go and process your information

[Barrett89]

Great excel work. I really appreciate that. Keep it up. Actually, I'm just thinking that what may the objective
to keep the records rather graphed records for the weights of both cool boys. I'm sorry for such silly question
but I didn't do it ever. Rather, I extremely love and care my little Siberian Husky!
pomeranian treats