Babylonian math and modern addition

Babylonians, who were very early astronomers, inherited a rather interesting counting system from the Sumerians, one that worked in Base 60, if you can believe it. It was basically derived from counting each of the segments of the fingers on one hand, not including the thumb (3 x 4) and then using all five fingers on the other hand to count each set of 12. Five times 12, of course, equals 60.

60 is a very useful number because it has so many factors: 1 through 6, then 10, 12, 15, 20, 30, and 60. It also has common factors with 8 (2 and 4) and 9 (3), and can easily create integer fractions with multiples of 5 and 10. For example, 45/60 reduces very easily. First, divide both by 5 to get 9/12, then divide both by 3 to get 3/4. It works just as easily in reverse — 60/45, 12/9, 4/3 which equals 1 1/3.

If you’re ahead of me, then you’ve already realized a very important place where we use 60 a lot.

Now, I would argue that the system is actually Base 12 counted in groups of 5, but the outcome is rather interesting, because to this day it forms the basis for some pretty basic things: Euclidean geometry and telling time.

A minute has 60 seconds and an hour has 60 minutes, of course. A circle has 360 degrees, which is 60 times 6. It’s a fortunate coincidence that an Earth year worked out to be so close to that in number of days — 365.25. And in case you’ve ever wondered why we add one day every four years, like we will this year, that’s the reason why. Our 365 day calendar loses a full day in that time, and we put it back by tacking it onto the end of February.

I still think that it was more Base 12 times 5, because there are some significant dozens that pop up, again thanks to the Babylonians. There are a dozen constellations in the zodiac, each one taking up 30 degrees of sky, giving us 12 months.

Of course, you can’t write “12” in Base 12 — those digits actually denote what would be 14 in Base 10. So how do you get around there only being 10 digits if you want to write in bigger bases?

If you’ve done any kind of coding or even HTML, you’re probably familiar with the hexadecimal system, which is Base 16. There, the convention was established that once a digit hit nine, the rest would be filled out with letters until you incremented the next digit up. So, once we get to 9 in Base 16, the following digits are A (10), B (11), C (12), D (13), E (14), and F (15). F is followed by 10 (16), and the whole process repeats following the rules I’ve described previously.

Now you might wonder, how did they do single digits in Base 60, and the answer is that the Babylonians didn’t. In fact, they sort of cheated, and if you look at their numbering system, it’s actually done in Base 10. They just stop at 59 before rolling over. They also didn’t have a zero or a concept of it, which made the power of any particular digit a bit ambiguous.

And yet… Babylonians developed a lot of the complex mathematics we know to this day, including algebra, a pretty accurate calculation of the square root of 2, how to figure out compound interest, an apparent early version of the Pythagorean theorem, an approximation of π accurate to about four digits, measuring angular distances, and Fourier analysis.

Yeah, not too bad for an ancient civilization that didn’t have internet or smart phones and who wrote all their stuff in clay using sticks, huh? But that is the beauty of the ingenuity of the human mind. We figured out this stuff thousands of years ago and have built upon it ever since. The tricks the Babylonians learned from the Sumerians led in a straight line right to the device you’re reading this on, the method it’s being piped to your eye-holes, the system of satellites or tunnels of fiber optics that more likely than not takes the data from source to destination, and even the way all that data is encoded.

Yay, humans! We do manage to advance, sometimes. The real challenge is continuing to move forward instead of backward, but here’s a clue. Every great advance we have made has been backed up by science. Within our own living memory — that of ourselves, or the still living generations who remember what their parents and grandparents remembered — we went from not being able to fly at all to landing humans on the Moon to launching probes out of our solar system, all of it in under one century.

We have eradicated or mitigated diseases that used to kill ridiculous numbers of people, are reducing fatality rates for other diseases, and are increasing life expectancy, at least when the voice of reason holds sway. For a while, we even made great advances in cleaning up the environment and quite possibly turning the tide back in favor of reversing the damage.

But… the real risk is that we do start moving backward, and that always happens when the powers that be ignore science and replace it with ignorance and superstition, or ignore the advances of one group because they’re part of “them,” not “us.”

To quote Hamilton, “Oceans rise, empires fall.” And when an empire falls, it isn’t always possible for it to spread its knowledge. What Babylon discovered was lost and found many times, to the point that aspects of it weren’t found again until the time of the ancient Greeks or the Muslims, or the Renaissance.

In order, and only in terms of math, those cultures gave us geometry; algebra and the concept of zero; and optics and physics — an incomplete list in every case. European culture didn’t give us much in the way of science between the fall of the Roman Empire and the Renaissance, while the Muslim world was flourishing in all of the parts of Northern Africa and Southern Europe that it had conquered, along with preserving and advancing all of that science and math from fallen old-world civilizations.

Yeah, for some funny reason back then, their religion supported science. Meanwhile, in other places a certain religion didn’t, and the era was called the Dark Ages. That eventually flipped and the tide turned in Europe beginning in the 16th century. In case you’ve ever wondered, that’s exactly why every college course in “modern” history begins at 1500 C.E.

Sadly, the prologue to this is the Italian war criminal Cristobal Colón convincing the Spanish religious fanatics Fernando y Isabel to finance his genocidal expedition originally intended to sail west to India but unfortunately finding some islands next to a continent in the way, on which he raped, pillaged, and slaughtered people for his own amusement. Or, in other words, the Dark Ages didn’t end until Colón and those Spanish rulers were dead and buried, meaning January 23, 1516, when they fed the last of them, Fernando, to the worms.

Oh, except that humans continued to be shitty as they sailed west even as science back home advanced. Dammit. And that’s been the back and forth since forever. What we really need are more people committed to the “Forth!” while determined to stop the “Back!”

Or, at the very least, push the science forward, push the bullshit back.

Stupid Excel tricks #1: INDEX and MATCH

Enter the matrix… math

There is an entire class of functions in Excel that take things to a whole new level, and they are called matrices. Maybe you ran across this in math in school and have forgotten, maybe not, but the idea with a matrix is that it takes one grid of numbers of X x Y dimensions and uses operators to manipulate it using another grid of numbers that may or may not have the same dimensions.

The great part is that to use these functions in Excel, you don’t need to know any of that. Like I’ve mentioned before, it’s exactly like using a cookbook. Plug in the ingredients as specified, voila, the dish pops out the other end.

Maybe you’ve used the functions VLOOKUP and HLOOKUP, or maybe not, but they can be useful if you want to match exactly one criteria in a table and if the data you’re looking up is somewhere to the right of that criteria. So it’s perfect if you have something like a unique account number on the far left and want to use that to look up a name or phone number to the right of it:


This tells Excel to take the value in cell M2, compare it to all of the values in column A of the named range, then look up the value in the sixth column counting from the column defined in the second variable (in this case, F) where the first column is equal to M2. “FALSE” just means to use an exact match, whereas “TRUE” would mean to use an approximate match.

Again, this is great if you’re searching something with unique values in both places — there is only one account number, and only one data point associated with it.

Now what if you have multiple entries for the same person with different account numbers, or multiple sizes and colors of a product with differing prices, or you need to search on more than one data point in different columns, or your table was set up with the criteria you want to use somewhere to the right of the data points you’re searching?

Welcome, matrix functions! These are two nested commands that work miracles together. The first is INDEX, and what it basically does is point to a column with data that you’re going to pull stuff from, then follow that up with the criteria you’re going to use to do that. You can see the difference from the LOOKUP functions right off the bat, because those start with the single data point you’re going to use to search the data. The INDEX function starts with the place you’re going to get the answer from.

The MATCH function is the matrix math, and it allows you to specify multiple criteria matched to different columns in the source data. The nice part about it is that you can have as many different criteria as you need — first name, last name, account number; size, gender, color, style; title, author, binding, edition; and so on. And each of these can point to any particular bit of data you need — monthly cost, price, location, phone number, address, and so on. Any bit of data in the table can be found this way.

If you want to put a physical analogy on it, it’s this. LOOKUP functions are a librarian with a sliding ladder that moves horizontally or can be climbed vertically. But the way it works is that they first move it or climb it in the direction you specify until it hits the target word. Then, it slides or climbs the other direction however many rows or columns you specified, and has now targeted exactly one cell with the answer. Oh — and it can only move to the right or down from that initial search cell.

On the other hand, think of INDEX and MATCH as a whole bunch of librarians who have set out all over the same bookcases, but are simultaneously searching the rows and columns, and calling back and forth to each other to indicate what bits they’ve found that match.

If you work with any kind of inventory or any data sets where people’s info is broken down (as it should be) into separate first and last names and account identifiers, then you need to know these functions, because they will save you a ton of time. And the basic way they work is like this:


(Note: All column and row designations here are arbitrary and made up, so they don’t matter.)

That might look complicated, but it’s not. Let’s break it down. The first part, referring to the E column is the “Where” of the formula. That is, this is the column you’re pulling your data from. For example, if you want to use size, color, and style to find price, then this would be whatever column has the price data in it.

Next, we nest the MATCH function, and this lets INDEX know that what comes next will be the instructions it needs. The “1,” inside the parenthesis is a flag for MATCH, telling it to return one value. After that, each nested thing — and you can have as many as you need — follows the form “Single cell to look at equals column to search.” So, as seen here, for example, in the search data, column W might be the first name, and cell W2 is the cell corresponding what we’re looking at. Meanwhile, column C in the target data might also include first names, so what we’re saying is “Look for the single value of W2 down the entire column of C1 to C1405. The dollar signs are there to lock it as a fixed range.

All of the other parentheticals here follow the same pattern. Maybe X is the column for last name in the source and D is where the last names are in the target; and AA is account number, as is J.

The two other interesting things to note in building matrix equations: The single cell and the column are joined by an equals sign, not a comma, and this is important because, without it, your formula will break. What this tells Excel is that whatever the matrix pulls out of single cell must equal what’s in the column at that point.

The other thing to notice is that between the searches within parentheses, there aren’t commas, but rather asterisks, *, which indicate multiplication, and this is the heart of Matrix math.

What this tells the formula is to take the results of the first thingie, apply those criteria and pass it along to the second. In other words, if the first evaluation turned up nothing, that is mathematically a zero, and so it would quash anything coming from the second and third functions. On the other hand, if it comes up as a one, then whatever the second formula turns up will stay if there’s a one, dump if not, and then pass on to the third, fourth, etc..

Lather, rinse, repeat, for as many steps down the process you’ve created. A false at any point in the matrix math will kill it and result in nil. Meanwhile, as long as the tests keep turning up positives, what will fall out of the ass end of it is the honest legit “This data is the true data.”

Funny how that works, isn’t it? The only other trick you need to remember is that after you’ve entered this formula, you need to close it out by hitting Ctrl-Shift-Enter to let Excel know it’s a matrix formula. Then, if you want to copy it, you can’t use the usual Ctrl-C, Ctrl-V. Instead, you have to highlight the column with the formula at the top, then hit Ctrl-D. Voila… the whole thing repeats down the column.

And there you have it — a way to search multiple criteria in a row in order to find a specific data point in a table. You’re welcome.

Being a basic bit

Zeroes and Ones are the building blocks of what’s known as binary, and the juice that our digital world runs on. Another way to think of it is this. In our so-called Base 10 world, we deal with ten digits, and depending upon what you’re counting, you can either go from 0 to 9 (things) or 1 to (1)0 (dates, place order). Since 9 is the highest digit, when any column hits it, the 9 next rolls back to 0 and the digit to its left increments up, initially from 1.

So after the number 9, we get 10. After 19, we get 20, after 99, it’s 100, and so on. Also note that 100 happens to be 10 x 10, 1,000 is 10 x 100, 10,000 is 100 x 100, etc. This will be important in a moment.

In the binary world, things roll over faster. In fact, the only digits you have are 0 and 1, so counting works like this: start with 0, then 1. But 1 is as high as we can go, so after 1 comes 10, which, in binary, represents 2.

That might seem strange, but here’s the logic behind it, going back to 10. What is 10, anyway? Well, it’s the number that comes after we’ve run out of digits. Since we’re used to base 10, it doesn’t require any explanation to see that 10 always comes after 9. At least in base 10. I’ll get to that in a moment, but first there’s a very important concept to introduce, and that’s called “powers.”

The powers that be

No, I’m not talking Austin Powers. Rather, raising a number to a power just means multiplying the number by itself that many times. In its basic form, you’ll often see Xn. That’s what this means. It’s just a more efficient way of writing things out:

            2 x 2 = 22 = 4

            3 x 3 x 3 = 33 = 3 x 9 = 27

            10 x 10 x 10 x 10 x 10 = 105 = 100 x 100 x 10 = 10,000 x 10 = 100,000

Here’s an interesting thing about powers of 10, though. The end result will always have exactly as many zeros as the exponent, or power that you raised 10 to. 109. Simple: 1,000,000,000. If it’s 102, 100, and so on.

And the two fun sort of exceptions that aren’t exceptions to keep in mind:

            X x 0 x N = N, aka X0 = 1

            X x 1 = X1 = X.

101 is 10 with 1 zero, or 10; 100 is 10 with no zeroes, or 1.

In other words, any number to the power of zero equals 1, and any number to the power of 1 equals itself. And there you go, that’s all you need except for this: When it comes to determining what the power is, we count “backwards” from right to left. The last digit before the decimal takes the 0 power, next to the left is 1, next over from that is 2, and so on.

Everything in its place

Since places correspond to powers, in Base 10 we would call the digits, right to left, the ones, tens, hundreds, thousands, ten-thousands, hundred-thousands, and so on places. In binary, you’d have the ones, twos, fours, eights, sixteens, thirty-twos, etc.

Makes sense? Then let’s look at a four-digit number in binary: 1776.

But here’s an interesting trick: in computer logic, it often becomes much easier for the circuits to literally read in the digits backwards in order to do these steps upwards in the proper order. This saves the step of having to figure out how long a string is before assigning the proper power to the most significant digit, which is the last one on the left.

So, to calculate, we’ll count it from right to left, which will make it easier to follow what’s happening. Let’s go with 6771 for ease of use. The 6 is in the zero position, so it represents 6 x 100, in which case this is 6 x 1, meaning just plain old 6.

Next, a 7 times 101, which is just 10, so this spot is worth 70 and we’re up to 76.

Next, 7 times 102, which is 100 times 7. Add that to the rest, it’s now 776.

Finally, a 1 in the spot multiplied by 103, which is 10 x 10 x 10, which is 10 x 100, so… 1,000. Slap that on the rest, and there you go: 1776.

This works exactly the same way in any other base. So let’s look at a typical binary number: 1011 1110. As humans, we could deal with doing the whole thing backwards, but again, let’s make it easy for the machine, feed it in right to left, and watch the sequence in action:

Digit (D) 0    1    1    1    1    1    0    1
Power (p) 0    1    2    3    4    5    6    7
2^p       1    2    4    8    16   32   64   128
2^p x D   0    2    4    8    16   32   0    128
SUM       0    2    6    14   30   62   62   190

Or in base three or trinary, let’s look at 21221121, entered again in reverse:

Digit (D) 1    2    1    1    2    2    1    2
Power (p) 0    1    2    3    4    5    6    7
3^p       1    3    9    27   81   243  729  2187
3^p x D   1    6    9    27   162  486  729  4374
SUM       1    7    16   43   205  691  1420 5794

Now, let’s take a look at an interesting property in Base 10 and see if it translates over.

Dressed to the nines

In Base 10, any number divisible by nine also has all of its digits add up to nine. You can easily see this with the first few pairs of two-digit multiples of nine: 18, 27, 36, 45, 54, and so on. The tens digit goes up by one while the ones digit goes down by one, and that makes perfect sense. Why? Because when you add nine, what you’re really doing is the same as adding 10 and then taking away one.

It doesn’t matter how big the number is. If you can add the digits up to nine, then you can say it’s divisible by nine. To just pull a number out of thin air, I guarantee that 83,764,251 is evenly divisible by nine. I could also put any number of nines anywhere in that number and it would still be divisible, or put the digits in any order. And if you have a number that has all of the digits from 0 to 9 in any order, then it’s divisible by 9.

So does this property hold for other bases? What about Base 8? In that case, we should expect seven to be the magic number. I’ll spare you the torturing of Excel I did to run a  test, but the answer is: Yes. If a number is divisible by seven in Base 8, then its digits add up to seven. Here’s the list from the Base 8 equivalent of 1 to 99 (which is 1 to 77): 7, 16, 25, 34, 43, 52, 61, 70. Now none of those numbers in Base 10 is divisible by seven, but in Base 8 they are. Here’s how and why it works.

When you divide a number in Base 10 by 9, you start on the left, figure out how many times 9 goes into that whole number, carry the remainder to the next digit, and repeat the process. So to divide 27 by 9, you start by dividing 20 by 9. This gives you 2 times 9 = 18. Subtract 18 from 20, you get 2. Carry that over to the next place, which is 7, add 2 and 7, you get 9, which is divisible by 9. Add the 2 from the first result to 1, and your answer is 3.

Did you notice anything interesting there? It’s that you happened to wind up with the number in the Base digit twice. Two times 9, with the remainder of 2 adding to the other digit, and what was the other thing we noticed? That’s right. The sum of the digits is 9, so what’s left when you divide the ten’s digit by 9 has to add to the one’s digit to total 9.

This is true in any other base. Let’s look at our Base 8 example of 34. We can’t cheat by converting to Base 10, so the 3 tells us that 7 goes into the number three times. But since 3 times 7 is 3 less than 3 times 8, that’s our remainder. Add that to the 4 to get 7, and boom, done. In Base 8 34/7 = 3+1 = 4. Convert the Base 8 to Base 10 to get 28, and voila… 4 times 7 is 28. The answer is the same either way when you reduce it to a single digit.

A spot check bears this out with other bases, so it would seem to be a rule (though I’m not sure how to write the formula) that for any Base, B, and any number evenly divisible by B-1, the digits of that number will add up to B-1.

That’s the funny thing about whole numbers and integers. They have periodicity. What they do is predictable. Multiples of any integer will appear at regular intervals without jumping around no matter how far towards any particular ∞ you go. Irrational numbers and primes, not so much. But it’s good to know that the basis of digital computing is so reliable and regular. In fact, here’s a funny thing about binary: Every single number in binary is evenly divisible by 1 because all of the digits of every single number in it adds up to a number divisible by… 1. And a Base 1 numbering system is impossible, because the largest possible number in it is 0. It also breaks the whole Base rule above, because nothing can be divided by 0. Base 1 is the Black Hole of the numbering system. The rest should be pretty transparent.