This is Part 2 of the blog Let’s play with all the presidents (Excel)-Part1. Please read Part 1 there first to follow along.
To find the zodiac sign of a president we need to map his month and day portion of his birthday to a table that map the month and day to a zodiac. I’ve created the following table based on the zodiac signs’ ranges. Note that I didn’t have to put the start and end for each because I’ll use fuzzy matching in Excel to find the sign.
Although I haven’t checked for all date combinations, but from my test the above table worked perfectly for my need. Then to add a little interest, I added the symbols as Unicode chars so I can treat them as “text” in formulas.
Once I tested it out with some sample dates, I plug the POTUS data table to this via Vlookup. In my setup, to get the name of the sign I used:
=VLOOKUP(VALUE(TEXT(VALUE(J6),”M/D”)),Signs!$A$2:$B$14,2,1)
and to get the matching glyph, I used:
=VLOOKUP(K6,Signs!$B$2:$C$14,2,0)
where the sheet containing the zodiac table shown above is titled “Signs”.
Once I copy the formula down and across, I get ALL the required results for all presidents as follows:
Mission accomplished.
Now the rest is really for visuals and interactivity. I wanted to add some interactive charts and pivot tables. With the heavy lifting already done and my entire table constructed, I can slice and dice that in a multitude of ways. I just show here a few ideas…hope they’ll give you enough tips to try many others.
Charts & Pivots
The following chart allows user to select any president and get the number of years passed since his birth:
The following chart shows the summary of how many presidents fall in which zodiac signs. It appears most presidents have been Scorpio and Aquarius (tied at 5 each).
The following allows interactivity. User can select any sign (by name or symbol) and it’ll show how many presidents fall in that sign. User can also select multiple signs to compare as shown below:
The following shows (interactively and summary) which weekdays had the most birthdays of presidents. Monday and Thursday are tied at 8, meaning most presidents were born on that day (8 of them for each).
The following shows (interactively and summary) which months had the most birthdays of presidents. October had the most (6 presidents were born on the most), followed by November and and August (tied at 5 presidents).
So, there you have it…you can do many more analysis just based on the data extracted from just one simple string!
