After the outrageous success of our last blog post, we rushed to bring you more pet centered excitement. This time we use Excel to answer the question “So how old is my cat in human years?”
I found this information easily enough on the internet, this is a mixture of several websites, none of which seem to just have the data like we’ve got it here. So eventually got 2 columns of data Cat Year and Human Year, that’s a good start. For some of you, this will be enough I expect, but this post is really for people that want a bit more insight into the ageing process in cats… maybe??
Next, we’re going to take a closer look by using Excel.
Interestingly I’ve just found a chart of Human vs cats years here. The figures disagree a bit but I may revisit this new data later on in this blog.
On with the show, Right-click on one of the cells with data in, and select Quick Analysis. Most of the time this is enough
Select the charts tab and the line graph. You should get this chart, you can change the title or whatever.
Right-click on the blue line and select ‘Format trendline.’ This is how we’re going to find some simple information about this curve, about this relationship between the ageing of humans and cats.
On the right-hand side of your screen, Excel will try to guess what time of line you are analyzing. In this case it correctly guesses that you are looking at a linear graph (straight-line graph in English…)
We want to display some information about the trendline so near the bottom select the two tick boxes as shown.
‘Display Equation on chart’ – we want to see what kind of line Excel thinks we are looking at and this is the easy way to do it. A trendline is the way a computer can analyze a set of data and make a guess at any underlying mathematics that may be exhibited in the graph. A pretty impressive thing to do really. That maths actually ‘works’ amazes me sometimes.
‘Display R-squared value on chart’ .. we’ll get to that in a bit..
OK we’ve got a result: Excel tells us the equation of the line y=4x +15 if I simplify it a bit. This is a very common kind of equation and its meaning is quite simple. 4 is the gradient of the curve. So for every year, I move along the human age axis(the x-axis) I move up 4 years on the cats age axis (the y axis) .. you’ll see figures like this on roadsigns warning us. Well, our gradient is 1:4 .
Then we have what is called the intersect which has a value of 15. This means that if when a cat is born, its already 15 years old. I’ll leave you to ponder on this one…
Finally, we have what’s known as the R-squared value for the line. This is a measure of how good a fit, Excels trendline is, to the actual line.
Rsquared equaling 0.9 means that the trendline computed by Excel from our data, is actually a very good match to the actual data we collected.
Finally, here is a web app to make that vital computation for you.. accurate to many decimal places I’m sure…