This started as an April Fools joke from Bill Szysz and Mike Girvin, but it brought back memories of Schoolhouse Rock from 1973 and the story of Little Twelve Toes. In today's video, a secret decoder ring in Excel using BASE and DECIMAL.
Also: you can now assign fill color and font colors using Hex color codes in Excel.
This video includes alternate numbering systems like Base 12, Hexadecimal, and Base 36.
Download the workbook from: https://www.mrexcel.com/download-center/2020/04/Excel_Decoder_Ring.xlsx
Also: you can now assign fill color and font colors using Hex color codes in Excel.
This video includes alternate numbering systems like Base 12, Hexadecimal, and Base 36.
Download the workbook from: https://www.mrexcel.com/download-center/2020/04/Excel_Decoder_Ring.xlsx
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2330: Excel Secret Decoder Ring.
Hey welcome back to the MrExcel Netcast. I am Bill Jelen.
Today. I'm recording this April 2nd. It will go live April 3rd, but yesterday. April 1st, Mike Girvin sent me this formula and he said he got it from Bill Szysz. I looked at it, I was on my phone right? I don't have Excel handy. Well I have Excel on the phone, but I wasn't going to try and do it there and I looked at this and I saw that 36 and I was instantly transported back to 1973. Now I couldn't solve it just by looking at it because I'm not good at doing this division in my head, but if you're old like me, you remember 1973 watching TV on ABC, Saturday morning cartoons, little song, to teach multiplication by 12. But before they got to the multiplication tables, they talked about this planet where everyone had 12 toes and how they could count really fast [ by 12's ] and in that system the digits were 7, 8, 9 dec, el, doe. I couldn't remember "Little 12 toes", but I Googled "Dec, El, Doe" and boom I ended up at the illustration. That little jingle - 47 years ago, taught me about alternate number systems. Michael Eisner from Disney said kids will never understand it, but we understood it. Now today. The place where we run into this a lot is in colors. If you're designing a web page and you're trying to create web safe colors, the colors today are in hex and that stands for hexadecimal, which means it's a 16-digit number system. So instead of little 12 toes, this would be little 16 toes.
Oh, hey, something else just came out this week, interrupting the video here. These Hex color codes. I'm going to copy that and if I want to use either the fill color or the font color when we go into more colors, it used to be that you had to convert those to RGB codes.
They have a hex box right here, so do a paste.
And say OK.
And then here copy with ctrl C.
And More Colors.
Custom. And paste the hex codes right in that right? So how cool is that? You don't have to do the conversion to RGB. all right back to the story.
And think about how hard it would be to have dec, el, doe on your computer keyboard.
Smartly they used 7, 8, 9 and then A, is 10. B is 11. C is 12.
F is 15 and then.
10 in this system is actually 16. I remember, just fresh out of college, working in COBOL, and when the program would blow up in the middle of night, you'd have to go in and read the hex dumps. And you're reading this hexadecimal to try and figure things out. In hexadecimal, it's pretty simple. You have the numbers 0 through 9 and A through F, and so if I have this number bead BEAD. Take the least significant digit that's D, which is the 13th number. The least significant digit is always a one. If 16 is raised to the Zero Power, that is one.
And then A is a 10. So take that 10 * 16 raised to the first power. And then the next digit.
E. 16 raised to the second power is 256. So 14 * 256. Here I'm doing all this math.
And it adds up to 48813. Well, it turns out there's a great function in Excel that will just do this for us, called the DECIMAL function. Take any hexadecimal number or word and you can very quickly convert it back to 48813 without having to do all this math.
And if you have 48813, you can then use the BASE function.
Saying that we're in a a world where everyone has 16 fingers and toes and it converts it back to a number.
Now this is kind of cool. If you could spell things with those six letters. There's not a lot of things you can spell with six letters, BEAD is one. You know that you can Google for words you can spell with hex.
But when I saw Mike's formula here and that 36 I said, Oh well, that whoever did this is really, really clever because it's not just letters A through F.
We are going to have digits zero through 9 and then the letters A through Z. A through Z. So in this system you can spell anything. Alright, so here's Mike's formula. I press Control+shift+quotation mark.
What is that? That's Ctrl+Ditto!
Whatever is directly above me. It'll bring it down and leave it in edit mode. That is text up there in B4. But because I press Control Shift Ditto, it's going to come down and spell April Fools Day. All right now we're a couple of days late for this. But the cool thing about this is if you know the DECIMAL function and the BASE function, you can go either way here, right? So I was talking to Mike and he is not as old as I am. He doesn't probably remember the Little 12 Toes in 1973.
And so you know, I sent him something about Ovaltine.
Which was the secret decoder ring in the movie Christmas Story.
But we digress. Mike says OK, I want "Ovaltine is chocolaty rad.
and then I said according to Mike, Excel Is Fun Girvan. So we have these words. These are numbers here in a base 36 numbering system. To convert those back to real numbers, the DECIMAL using 36. So we get all of those numbers and then what we're doing here with the new dynamic arrays. Is asking for the base of each of those numbers in the 36 numbering system, and we can pass them all as a single array constant. So you can type anything here. And by the way, download this - down in the YouTube description you download this workbook. Once we have that workbook.
Type anyone here in these orange cells if you need something longer, just insert some new cells in the middle and copy the decimal function in and then this concatenates everything together. So now Control+Shift+ Quotation Mark, Enter.
Ovaltine is chocolaty rad according to Mike Excel is Fun Girvin. So you would send this formula to someone in an email. They would copy that go to Excel, enter the formula and the secret message will come out.
Well, that was 7 minutes of your life you're never going to get back. Thanks to Bill Szysz for sending that to Mike Girvin, sending it on to me and bringing back this awesome memory of Little 12 Toes. If you like the tips on EVERY OTHER day, please Subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below. My new book MrExcel 2020 Seeing Excel Clearly. Click that "I" in the top right-hand corner.
I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
Hey welcome back to the MrExcel Netcast. I am Bill Jelen.
Today. I'm recording this April 2nd. It will go live April 3rd, but yesterday. April 1st, Mike Girvin sent me this formula and he said he got it from Bill Szysz. I looked at it, I was on my phone right? I don't have Excel handy. Well I have Excel on the phone, but I wasn't going to try and do it there and I looked at this and I saw that 36 and I was instantly transported back to 1973. Now I couldn't solve it just by looking at it because I'm not good at doing this division in my head, but if you're old like me, you remember 1973 watching TV on ABC, Saturday morning cartoons, little song, to teach multiplication by 12. But before they got to the multiplication tables, they talked about this planet where everyone had 12 toes and how they could count really fast [ by 12's ] and in that system the digits were 7, 8, 9 dec, el, doe. I couldn't remember "Little 12 toes", but I Googled "Dec, El, Doe" and boom I ended up at the illustration. That little jingle - 47 years ago, taught me about alternate number systems. Michael Eisner from Disney said kids will never understand it, but we understood it. Now today. The place where we run into this a lot is in colors. If you're designing a web page and you're trying to create web safe colors, the colors today are in hex and that stands for hexadecimal, which means it's a 16-digit number system. So instead of little 12 toes, this would be little 16 toes.
Oh, hey, something else just came out this week, interrupting the video here. These Hex color codes. I'm going to copy that and if I want to use either the fill color or the font color when we go into more colors, it used to be that you had to convert those to RGB codes.
They have a hex box right here, so do a paste.
And say OK.
And then here copy with ctrl C.
And More Colors.
Custom. And paste the hex codes right in that right? So how cool is that? You don't have to do the conversion to RGB. all right back to the story.
And think about how hard it would be to have dec, el, doe on your computer keyboard.
Smartly they used 7, 8, 9 and then A, is 10. B is 11. C is 12.
F is 15 and then.
10 in this system is actually 16. I remember, just fresh out of college, working in COBOL, and when the program would blow up in the middle of night, you'd have to go in and read the hex dumps. And you're reading this hexadecimal to try and figure things out. In hexadecimal, it's pretty simple. You have the numbers 0 through 9 and A through F, and so if I have this number bead BEAD. Take the least significant digit that's D, which is the 13th number. The least significant digit is always a one. If 16 is raised to the Zero Power, that is one.
And then A is a 10. So take that 10 * 16 raised to the first power. And then the next digit.
E. 16 raised to the second power is 256. So 14 * 256. Here I'm doing all this math.
And it adds up to 48813. Well, it turns out there's a great function in Excel that will just do this for us, called the DECIMAL function. Take any hexadecimal number or word and you can very quickly convert it back to 48813 without having to do all this math.
And if you have 48813, you can then use the BASE function.
Saying that we're in a a world where everyone has 16 fingers and toes and it converts it back to a number.
Now this is kind of cool. If you could spell things with those six letters. There's not a lot of things you can spell with six letters, BEAD is one. You know that you can Google for words you can spell with hex.
But when I saw Mike's formula here and that 36 I said, Oh well, that whoever did this is really, really clever because it's not just letters A through F.
We are going to have digits zero through 9 and then the letters A through Z. A through Z. So in this system you can spell anything. Alright, so here's Mike's formula. I press Control+shift+quotation mark.
What is that? That's Ctrl+Ditto!
Whatever is directly above me. It'll bring it down and leave it in edit mode. That is text up there in B4. But because I press Control Shift Ditto, it's going to come down and spell April Fools Day. All right now we're a couple of days late for this. But the cool thing about this is if you know the DECIMAL function and the BASE function, you can go either way here, right? So I was talking to Mike and he is not as old as I am. He doesn't probably remember the Little 12 Toes in 1973.
And so you know, I sent him something about Ovaltine.
Which was the secret decoder ring in the movie Christmas Story.
But we digress. Mike says OK, I want "Ovaltine is chocolaty rad.
and then I said according to Mike, Excel Is Fun Girvan. So we have these words. These are numbers here in a base 36 numbering system. To convert those back to real numbers, the DECIMAL using 36. So we get all of those numbers and then what we're doing here with the new dynamic arrays. Is asking for the base of each of those numbers in the 36 numbering system, and we can pass them all as a single array constant. So you can type anything here. And by the way, download this - down in the YouTube description you download this workbook. Once we have that workbook.
Type anyone here in these orange cells if you need something longer, just insert some new cells in the middle and copy the decimal function in and then this concatenates everything together. So now Control+Shift+ Quotation Mark, Enter.
Ovaltine is chocolaty rad according to Mike Excel is Fun Girvin. So you would send this formula to someone in an email. They would copy that go to Excel, enter the formula and the secret message will come out.
Well, that was 7 minutes of your life you're never going to get back. Thanks to Bill Szysz for sending that to Mike Girvin, sending it on to me and bringing back this awesome memory of Little 12 Toes. If you like the tips on EVERY OTHER day, please Subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below. My new book MrExcel 2020 Seeing Excel Clearly. Click that "I" in the top right-hand corner.
I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
