Did you know the world’s oldest living cat is now 31 years old? My first thought was: “that’s old alright! But how does it translate to human age? Must be really old.”
Turns out, yes it is. Although it’s not that straight forward as many of us learned in the past, but not too difficult either.
Here’s the general formula (latest):
CAT AGE: The first 2 years of a cat is equivalent to 25 years of a human. After that, every calendar year of cat’s life is equivalent to 4X the human age.
It got me wondering though, what about dogs? I remember it being like 7x?
Actually, it’s even more complicated than determining a cat’s age because we have a new dimension to consider: size of the dog! The size (S, M, L) progresses age differently according to the formula described by WebMD.
So, I could easily write a script or simple code in any of my favorite high-level languages (C/C++, JavaScript, Go, Python…) or even proprietary languages (Construct, Scratch, etc.) but I wondered how would a poor Excel analyst guy deal with this! Could I enable a non-programmer to do this using simple Excel formulas?
YES, I can! Here, I show how to do just that.
DISCLAIMER: I’m an analyst in a fast-paced real-world, so I work with the data to best deliver a story in the most efficient way (may not be most exhaustive as a statistician’s)…besides, people are waiting on my findings! Nor do I want to be known as a coder. I am a computer scientist who knows a bit about everything across the spectrum regardless of tools/frameworks/syntax. I deliver real-world solutions: Technical<->Business<->People. This is my personal blog about my own musings, not course offerings!
So, let’s start with determing a cat’s age (which is a simpler formula).
Review the formula in CAT AGE section above. So, I created a constant K for the first 2 human years (column C), this is durable because if next month, the authority changes it from 25 to say, 17, my formula will still work without any change. Then all you have to do is enter the number of years you think the cat’s age is (in human calendar) in column B1, and the result is shown in B2.
So, the Excel formula is 
For the dog, I could do the same way with a little tweak, but instead, as nerdy as I can be, I wanted to do it the harder way! Using the chart from Purina’s site (using WebMD data), let’s say this is all we have to go by…
Okay. You see that through 5th year, the formula is identical, and after that it starts to diverge by size…with larger sized dogs aging faster than smaller ones.
I tabulated the data from the image into Excel as follows so we can do some arithmetic on these values…
Then I create an area for you to enter 2 parameters: Size, and Age (as we know it). The inputs are shown in green cells, and the result that Excel calculates is shown in blue below.
The Dog Size field is a drop-down list that you choose from:
Then you choose the dog’s current age, as in our calendar year, also using a drop-down (so that it exactly corresponds the official chart shown above):
Therefore, we can choose 2 different, independent parameters that will yield us the final result: Dog’s Age (in human-equivalent age). So, just pick any combination of Age and Size, and we can tell you the dog’s age using the exact chart constraints above.
The Excel version of the solution/formula is a combination of 2 MATCH() functions (because: size, age) and mapping them to the chart value of age per the size dimension specified using INDEX. And it is: 
The rows and columns as I have them using this formula are shown in the image below for reference:
And there you have it! You didn’t even had to write a single line of code! But you created a global tool that ANYONE can use whether you’re a cat or dog lover (or both, as I am).
FUN FACTS
The cat’s name pictured is Nutmeg. Owners Liz and Ian Finlay took the cat in back in 1990 as a stray.
EXERCISE:
Calculate a cat’s age using SWITCH instead of IFS.
[ Attribution: News on Cat. ]
