HAVING Keyword in SQL (or How to Calculate Loyalty with One Sentence)

This was a lot easier than I thought it would be.  For some back story – but without revealing too much about my industry - in our company, we have individuals who provide us with a great deal of business.  However, they provide our competitors with a great deal of business as well.  They are not contracted to give us all of their business, and there’s really nothing we can do aside from creating solid business relationships in order to assure that they do give us at least most of their business.

So in order to easily determine which individuals are giving us most of their business (and we define that as over 70% of their business), I wrote something very similar to the following simple SQL statement (for MySQL):

1. SELECT individual_id, count(1) AS total_volume,
2.  sum(case business when 'Us' then 1 else 0 end) as our_volume
3. FROM main_data_table
4. GROUP BY individual_id
5. HAVING our_volume / total_volume >= 0.7;

Note: the line items are not part of the SQL code – they were added to make explaining it easier.

I don’t normally do a lot with the HAVING keyword, but I suppose I ought to.  It comes in really handy in this example.  Here’s the breakdown of what’s going on:

1 : This is a stupid way of doing it (not the actual way I chose) but it essentially gives you a total number of line items for the individual_id.

2 : Two things are going on here.  First, we tell MySQL that when the line item represents our business (i.e. the center where the transaction happened was one of our centers), count it as 1.  Otherwise, count it as 0.  That way, when they are aggregated (using the sum() function), the result is the total number of line items for the individual_id that were completed at one of our centers.

5: This limits the results to those individual_id’s that produced at least 70% of their business at one of our centers.  You must use the HAVING keyword when you want to limit the results to a calculated aggregate qualifier.

How I Feel Whenever I Submit a Possible Technological Change at Work

Here is an excellent article at McSweeney’s that just about perfectly expresses how I feel whenever I try to get management to agree to a change in processes, especially when technology is involved in the improvement.

http://www.mcsweeneys.net/2008/11/13hodgman.html

By the way, if you don’t already know, McSweeney’s is very guj.

The Greatest and Best Fish in the World: A Tribute

Having not written anything guj for what seems like ages, something happened over the Labor Day weekend that was incredibly non-guj, and for which I will write a tribute post.  My beta fish, Brutus, crossed over.

Brutus was never a very intellectual fish.  When poised with the problem of how to fit his mouth around the morsels of food I gave him, he failed at an alarming rate.  As he would lunge towards the surface, fully expecting to direct his mouth around the pellet, he would, more often than not, completely miss his target.  This caused the then tumultuous water to reverse the buoyancy of the pellet and drive it down to the murky rocky depths.  Brutus never even seemed to notice.  He would sit at the surface, waiting for another pellet from me, his dutiful master.

When he stopped eating as much, I never thought too much of it.  “He’s getting old,” thought I, “and has had enough of foolishness.  He must have learned that if he grazes the bottom of the tank, he will be able to suck the nutrients out of yesterday’s forgotten foodstuff, and will continue to live forever based on this new plan.”  Ah, but there was a nagging voice deep in my subconscious reminding me that he is indeed getting old, and perhaps this loss of appetite could mean other, more serious things.

I am not a veterinarian, nor do I think a veterinarian can do anything to cure an aging beta fish.  I do not harbor any regret for not having done more to sustain his little life.  All I could do was sit at my desk and watch him slowly decay, his breathing becoming more and more labored, his attitude more and more lethargic.  But I do curse this world for creating something so fragile, and forcing me to love it.

Brutus was a gift from my wife.  She wanted me to have a companion at my cubicle.  His presence seemed to brighten any day.  He was so eager to swim up and look at me when I arrived in the morning.  Yes, this was really just anticipation of the food pellets soon to rain into his tank, but it was pleasant nonetheless.  As days turned into months and finally years, his presence on my desk became a fixture, and I couldn’t imagine working if it weren’t beside him.

There were a few scares.  He stopped eating for about a week a year ago.  I was sure he was a goner.  Many of the symptoms I witnessed over the past few weeks were witnessed then as well.  But somehow, miraculously, he began to eat again, and I nursed him back to health.

When I changed jobs to a different building down the street, I entrusted Brutus to a coworker at the first job, while I moved and got settled in the second job.  When I finally retrieved Brutus from this woman, his tank smelled like a forgotten pond in a redneck’s back yard, and he seemed absolutely miserable.  I cleaned his tank, gave him some food, sat and talked with him, and he was happy.

Now he lies at the bottom of the tank – resting, peacefully, finally.  He is not struggling to breathe, and he is not forcing himself to rise to the surface in a futile attempt at eating.  I don’t have the heart today to flush him.  Maybe tomorrow.  Hopefully soon, because this is kind of depressing looking at a dead body all day.

I want him to stay peaceful, and I don’t want to think of his little fish body being blasted through miles of plumbing to end up in a water treatment plant.  He’s better than that.

Goodbye Brutus, old man.  It was a pleasure getting to know you.

Progressive Status Indicators in Excel

This is one of those posts that is really only for my own benefit. I recently decided to tackle the idea of rather than just putting red, yellow, and green indicator lights on a spreadsheet to indicate how close we were to meeting budget in certain areas, I would programatically allow Excel to determine how “green” or how “red” we were, with yellow in the middle.

So, in other words, if red is RGB(250,0,0), yellow is RGB(250,250,0), and green is RGB(0,250,0), how do we gradually go from red to green depending on how far away from budget we are? By the way, for those who don’t know, the RGB number format is simply three numbers from 0 to 250 that represent how much of each color red, green, and blue the computer ought to use to compose a color. Adding red light to green light makes yellow light, so RGB(250,250,0) means you want to use pure red and pure green, but no blue in order to get yellow.

My first task was deciding how to measure the distance from budget. After a lot of calculations, I realized it should simply be how far from 100% of the budget we were. That leaves a scale of 0 to 100. At 100, we want to just display green, and at 0 we want red.

My second task was fading from red to green by going through yellow. My best solution was to split the task in half. If the actual value was less than 50% of the goal, we would worry about fading from red to yellow (we would only need to mess with the green color). If the actual value was more than 50%, we would fade from yellow to green (only mess with the red color). In other words: If 50% of budget, green = 250, red = calculated value. Blue is always 0.

Since there are 250 steps of color in the RGB scale, but only 125 for each half (red to yellow, yellow to green), then for every percent of budget, we want to add or remove 5 steps of color.

That’s all I need to explain, I think, in order to remember this later. If you have any questions, I will gladly answer them. What follows are two real-world examples taken straight from my current project.

Sub looper()

    Dim mydiff As Double
    Dim i As Integer

    For i = 5 To 10
        mydiff = (Cells(i, 23) - Cells(i, 24)) / Cells(i, 24)
        Call update_indicator("shp" & i - 4, mydiff)
    Next i

End Sub

Sub update_indicator(strShape As String, dblVar As Double)

    Dim intR As Integer
    Dim intG As Integer
    Dim intB As Integer

    intB = 0

    If dblVar < -0.5 Then
        intR = 250
        intG = 250 - (((Abs(dblVar) * 100) - 50) * 5)
    ElseIf dblVar < 0 Then
        intG = 250
        intR = 0 + ((Abs(dblVar) * 100) * 5)
    Else
        intG = 250
        intR = 0
    End If

    ActiveSheet.Shapes(strShape).Select
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(intR, intG, intB)

End Sub

Phenomenology: The Urinal

This is part of a series on phenomenology – loosely defined for this blog’s purposes as the unbiased study of the world with the intent of best understanding the ontology of existence. My goal is to humorously examine some of the lesser-examined aspects or circumstances of our world in order to keep the mind open and receptive.

Note: this is a repost of a journal entry I made several years ago. It is only meant to kick-start the series, and should not be taken seriously.

Upon entering the men’s room, one is generally faced, depending on the particular layout and size of the restroom, with a row of sinks, a row of urinals, and a row of stalls. It is usually a good idea to abstain from entering these stalls, as there is oft found the fecal matter of a supposed Harley rider, construction worker, or truck driver. When entering the space of the urinal, you will usually find a split horizon of urinal elevations, one for adults and one for children. The former can use both elevations, but the latter is generally confined to the lower-placed urinals unless tactical maneuvering is employed.

A urinal is rectangular shaped – more tall than it is wide. At the bottom of the urinal is a bowl which collects the urine. Sometimes (though not often enough) there is a two inch tall or so cylinder which is a scented “cake” called a “urinal cake” that neutralizes the urine odor. At the top there is a pipe springing forth vertically and attached is either a lever or a motion detector both affecting the same flushing system. Occasionally you will come across a urinal that works. By this I mean that when flushed, the urine will successfully leave the bowl. More often than not, there will be urine awaiting you (whether or not the urinal works).

When you approach the urinal you will find that the bowl is carved to fit your legs; thus, when standing directly in front of the urinal, you are able to position yourself such that your business is actually inside the urinal, and as such you are able to determine your own vulnerability of peeping eyes. Some men stand as much as half a foot away from the urinal, confident that their neighbors will keep their eyes forward, while others will stand as close to the urinal as possible (so close, in fact, that their business might actually scrape the wall of the urinal). Sometimes there will be a mini-wall partition, known affectionately as a “splash guard” that is high enough to block one’s neighbor’s peering eyes, yet low enough to maintain sight of the face of said neighbor. The most rare form of such partition is a full-wall partition, which juts out from the wall from the floor to the ceiling. So far, the only sightings of these have been at Hooters restaurants.

When removing your penis, it is best to do so smoothly, with no strange movements that might be misinterpreted. If wearing boxer shorts or boxer briefs, I recommend simply unzipping, fishing, and revealing, but keep the fishing to a minimum. If not, you will need to unbutton your pants and pull down the undergarment, which amounts to a sometimes devastating amount of extra time.

There is some controversy regarding how to stand while relieving yourself. In my opinion, it is best to stand with feet shoulder width apart, your right hand commanding the stream and your left hand in your pocket, on your waist, or resting on the upper urinal area. While going at it, it is best to remain with your eyes and face forward, either maintaining a constant vigil of your crotch or staring at the wall directly in front of you. Some men are comfortable with and actually encourage talking during the deed, and some of these are equally comfortable with eye contact during these conversations. However, in order to make it a pleasing experience for the widest participating group, it is best to remain silent and static.

Following urination, a follow-up jingle is necessary (though you will likely fall prey to the men’s room mantra: “No matter how you jump and dance, the last two drops go down your pants.”). Then a flush and reintroduction of penis to pants. I do this simultaneously, but the less experienced may need to do one at a time. In this case, I recommend flushing first, because while the water is running, you have a window of opportunity to settle yourself before the next guy in line gets antsy. There are many methods of reintroduction, the simplest of which is a manual relocation of the penis. Some of the more creative may choose to hold the zipper open while maneuvering their hips such that the penis falls naturally back into place. But whatever your style, do it quickly and move with confidence, lest you be perceived as an amateur. You then may choose one of the following three options: leave the restroom without washing your hands; run a little water over your hands and dry them by air, paper towel, or by running them through your hair; or thoroughly wash your hands using the supplied soap and dry them via one of the above methods. When leaving the restroom, try not to focus on the fact that regardless of your hand-washing choice, countless other men chose the first option and used the same door handle you are now using.

Getting Information From the Web Using Excel VBA

What we’re trying to do today is get a value from a webpage and return it to a custom function within Excel. I’m going to use a very simple example, and it should be inferred that this is only for very simple tasks. The information you seek to return to your function will need to be presented on the webpage the same way every time you visit it, because all you’re doing is skipping the web browser, bringing the background contents of the webpage into a string, and parsing the string to pull out the data you want.

What I mean by the “background contents” is the code behind all webpages. When your browser calls up a URL, it receives a bunch of code, which it uses to render the page to you. If you’re unfamiliar with HTML code, you may have a bit more difficulty with this, but all you really need to be able to do is recognize where your information is always kept within this code. This sounds more complicated than it is, really.

For my example, I want to create a function called “temperature” to which I can pass a city and state, and from which I can gather the current temperature of that city. I live in Orlando, FL, so that is the temperature I want to know.

Important: The first stumbling block in this project is the need for a special reference from within Excel VBA — the Microsoft Internet Transfer Control reference. You may need to download msinet.ocx (you can find it pretty easily through popular search engines) in order to add this reference. To add a reference, click Tools -> References from within the VBA window, locate the reference, and select it. If the reference is not available, download msinet.ocx, put it somewhere easy like \Windows\System, and then locate the file via “Browse…” from the references window.

The code for my example function follows, and I will explain everything in the following paragraphs:

Function temperature(strCity as string)

  strCity = Replace(strCity, ", ", "%2C+")

  Dim myURL As String
  myURL="http://search.msn.com/results.aspx?q=current+temperature+" _
  & strCity

  Dim inet1 as inet
  Dim mypage As String

  Set inet1 = New Inet
  With inet1
    .Protocol = icHTTP
    .URL = myURL
    mypage = .OpenURL(.URL, icString)
  End With
  Set inet1 = nothing

  Dim intStart As Integer, intEnd As Integer
  intStart = InStr(mypage, "<span class=""wea_temp""></span>") + 22
  intEnd = InStr(intStart, mypage, "&")

  temperature = mid(mypage, intStart, (intEnd – intStart))

End Function

I’ll take you line-by-line through this somewhat lengthy example function. To begin, the name “temperature” and the parameter strCity means that when you input this function into a cell within Excel, you would type “=temperature” followed by the city and state in quotes and parenthesis, i.e. ‘=temperature(”Orlando, FL”)’.

The second line takes strCity and replaces the comma with “%2C+”. The reasoning for this is to properly construct the URL. In the language of a URL, %2C means “comma”, and the + denotes a space. You can’t have spaces or punctuation marks in URLs.

Then we declare myURL, which is what we would have typed into the web browser. If you were to go to msn.com and search for something (like ‘current temperature Orlando, FL’), then looked at the address bar, you would see something like this. In fact, that’s how I constructed the string, and that’s how I urge you to go about this as well. We add our city and state – gathered from the function parameter – to the end of the string.

The next part gets a little vague for me, because I haven’t had much experience with the Internet Transfer Control. Like most controls, you need to first point a variable to it (”Dim inet1 as inet”), and then initialize it (”Set inet1 = New Inet”). In our example, we need to worry about three aspects of the Inet control – Protocol, URL, and OpenURL. The first two are parameters, and in our case will be icHTTP for the protocol, and the URL we have already constructed as the URL.

The third (OpenURL) is the method we’re using to hit the URL. The two parameters you need to pass to it are the URL to hit and what to do with the result. Since we’re only interested in the webpage itself and not any files that might be downloaded, we just want to return the value to a string, hence, we tell it to use the icString data type. icString is a data type unique to the Internet Transfer Control, and unfortunately, I don’t know much about it except that it can be transferred to a standard string data type.

We complete the web hit with the statement “Set inet1 = nothing” in order to clear out our memory. This is just good practice.

This is the fun and somewhat difficult part. If you search in msn for the temperature in Orlando, FL and then take a look at the source of the website through a standard browser, you’ll see a lot of stuff that means nothing to you. Buried in all of this stuff is the current temperature. The easiest thing to do in this case was to search msn, get the actual temperature, and then search for that number in the source. I then noticed that the temperature occurs directly after the first instance of the phrase ‘<span class=”wea_temp”>’. The InStr function tells me what character within the HTML code begins that phrase, and as the phrase is 23 characters long, we add 22 to that number. Immediately after the temperature was the character “&”, so we perform another InStr function (passing our first value as the start point for that function) to get the first instance of “&” following the temperature. The temperature is then recorded using the mid function, which asks for the string, the start value, and the length of the string you’re asking for (which would be the position of the “&” minus the position of the first character of the temperature).

Like I said, that’s the difficult part. You have to be able to read through all that code to find your value, and you have to play around with the InStr and mid functions to get the right numbers. This is also why it’s vital that you only do this with static pages. I know msn.com will always return the temperature of the city and state I search for at the top of the search results, so I am confident that this function won’t break. But if msn.com put the temperature arbitrarily in the list of search results, this would be substantially more difficult.

By the way, I didn’t use google for this because google knew I was using their search engine without using a web browser and didn’t return any results. If msn ever catches on to this ability, they will probably pull the plug as well because if you are so inclined, you can create a search engine using only Excel and skip all the ads and popups that some sites like msn force on you.

NOTE: I just found out that this function does not work from my office computer, potentially because of a firewall issue. I will continue to diagnose the problem, but I apoplogize if this function will not work for your setting.

Religion as a Threat to Scientific Progress

In one of my new favorite websites, Secular Philosophy, a well-known contemporary philosopher by the name of Daniel Dennett wrote recently that religion is potentially the largest threat to rationality and scientific progress that we have in the world today. He claims other debilitating factors – for example, alcohol, television, or addictive video games – though powerful enough to negatively affect our critical faculties, are not as corrupting as religion. Religion, he claims,

has a feature of that none of them can boast: it doesn’t just disable, it honours the disability. People are revered for their capacity to live in a dream world, to shield their minds from factual knowledge and make the major decisions of their lives by consulting voices in their heads that they call forth by rituals designed to intoxicate them.

Dennett cries for the abolition of religion from all of humanity, for it is like a parasite holding its hosts back from truly realizing their full potential. He closes his argument with the following paragraph:

The better is enemy of the best: religion may make many people better, but it is preventing them from being as good as they could be. If only we could transfer all that respect, loyalty and intense devotion from an imaginary being – God – to something real: the wonderful world of goodness we and our ancestors have made, and of which we are now the stewards.

He is writing to a world-wide audience. However, it is only a small percentage of the world that is or could be performing actions that better or worsen it – definitely on the large scale, but even on the small. The large majority of people with whom I come in contact on a daily basis are not and will never change the world. Regardless of whether or not they go home at night and pray to a god or whether or not they attend church on their Sabbath.

Dennett makes believe that the average person is expending all of his vast energy banks on being faithful when he could be working towards bettering the planet. However, I submit that the average person is neither intelligent enough nor eager to expend any great deal of energy on something not immediately beneficial to him. The average person hasn’t graduated college – only 28.7% of Americans over the age of 25 had at least a Bachelor’s degree in 2007 (source: U. S. Census Bureau). The average person watches reality television and sitcoms. The average person shops at Wal-Mart.

There is nothing wrong with watching reality television and shopping at Wal-Mart, and there certainly is nothing wrong with skipping college altogether, for the education of arts and sciences gained these days can be superseded easily by reading a couple dozen books available freely from the public library. My point is merely this: if the average person was non-religious, then the only difference we would see would be in the lives of average people themselves. Even then, I have not found many religious people who spend any amount of time ensuring that they are even living their lives according to that religion.

I agree with Dennett’s writing, however, when applied to the movers and shakers of the world today. Dennett writes of a man in “Liberated” Afghanistan who is being held on death row having been charged with blasphemy. Even today we see many laws being made or not made in America based on religious foundations. Such topics as gay marriage and stem cell research are being debated using words such as “right” and “wrong” – these notions are defined by religion. If the leaders of the world were able to step back from the bias of their faith and dictate law and order based on pure reason and rationality, perhaps the world would be a very different and more advanced place.

Then again, humans may be unable to do this. It’s not as if one person decided he was going to invent this thing called religion and soon got the rest of the world to buy into it. All societies in every corner of the globe eventually created their own religion autonomously. It’s almost as if it is a fundamental need, either of human beings and mankind as a whole, or at least of a society that hopes to attain any sort of order. Humans needed religion to explain the unexplainable, and societies needed religion to define boundaries of actions.

I’m not prepared to take either stance – neither that religion should be replaced with pure reason, nor that religion is an institution that has produced more benefit than detriment. However, I do hold that the average person is not going to put down a Bible and pick up a book on Ethics if he was abolished of his religion. He would simply have more time to mow the yard and watch TV.

Heaven on Birth

The recent death of George Carlin got me thinking again about a few things in recent dialog between me and my brother.  In his blog, he questioned the existence of an afterlife, and a slight comment debate sprung forth followed by a lovely conversation poolside a couple of weekends afterward.  Essentially, his main argument is that the existence of an afterlife requires the existence of infinity, for it would not make sense for an afterlife to terminate at any point, because, well, why would there be an afterlife then?  Furthermore, since the concept of infinity is irrational, then the concept of an afterlife is irrational and thus illogical/impossible/etc.  The argument holds water if you are willing to accept that infinity doesn’t exist.

However, it is not this, but one of his lesser arguments that I find myself pondering this morning.  Here is where I would really like to quote him, but my workplace has banned my ability to open the Blurty website (it deems it not work-related).  To paraphrase, he wrote that it cannot be expressly implied that the afterlife would be any better than our current life.  This is due to the fact that, empirically, we are able to assume that a new place will be in many ways similar to any other place we have been, and that the only differences will be superficial.  For instance, I have never been to see the Great Wall of China, but if I ever go there, I know that though there will be a really big wall and people who are culturally distinct from that of which I am accustomed, there will still be hate, ugliness, greed, happiness, love, gravity, a blue sky in the absence of cloud cover, various types of soil, water, etc.  So, if it is the souls currently on Earth who will inhabit Heaven, why would we expect the absence of such things as hate and greed once we get there?

But even this is not what I mean to post about today.  In fact, it is something of an opposite.  What I mean to post about today is the Judeo/Christian faith that Heaven will be a utopia where everyone happily coexists and there is no anger or injustice or prejudice or fear.

Here is my question: why must we suffer a life on Earth if it is possible for there to be a life such as Heaven?

This all falls under a couple of predefined notions.  First, that God is benevolent, and second, that God is omniscient.  Again, these are both held very highly in the Judeo/Christian faith.  It is assumed that God knows the entire path of every soul, and it is assumed that God wants the best for His children.  Hence, God knows, when I am born, whether or not I will lead a righteous life, and whether or not my actions and beliefs will earn me a right to pass through the pearly gates.  In other words, He knows whether or not I will be in Heaven one day, yet He forces me to live out my 70-whatever years on this planet.  If I am Heaven-bound, and Heaven is so great, why am I here?

This is reminiscent of the many great questions of the past, such as why do bad things happen to good people, or, if God is all-powerful and all-loving, why is our world so shitty?  The so-called Argument from Imperfection uses this to try to prove that God does not exist.  It is argued that

1. If God exists, He is omnipotent and benevolent.
2. If He were omnipotent and benevolent, then the world we live in would be perfect.
3. The world is not perfect (there is hate, anger, greed, etc.)
4. Therefore, God does not exist.

This is one of many traps believers fall into once they accept that God is omnipotent and benevolent.  The answer to this?  Some have said that this in fact is the most perfect world that could have been created considering the fact that we as human beings are flawed.  Others have said that a “perfect world” is an impossibility, and if something is impossible, then even something all-powerful could not create it.  I don’t care if either of these is right, or if both are wrong.  This is not the point of this post.

The point of this post is that according to this faith system, the afterlife – Heaven – is better than this life.  In fact, it’s a lot better.  No matter what, then, God knows that He created a life on Earth that is inferior to the life we lead when we die.  So my question remains, why do we have to live this one?  I’m not trying to disprove the existence of God or an afterlife.  I’m just asking the question.

True, this life is not all that terrible.  There is a great deal of beauty in the world, and there are a great many emotions and traits that are altogether human in nature.  It can be assumed, then, that these more human traits will not be experienced in Heaven.  Such would seem unfortunate, but perhaps would not be.  They are, after all, beastly traits compared to those of our pure souls.  On the org chart of all of creation, humans would be just barely above primates, and far below even the lowest angel.  But it is these base characteristics that keep life entertaining - lust, envy, passion, enjoying the taste of food that is really bad for you, enjoying the sensation of drunkenness.  If we lived entirely pure lives, they would be fucking boring.  Surely the omniscient God must know this; surely He must have designed this.  So perhaps it is our time with these base desires that is the purpose of this life, for afterwards we are destined to an eternity of purity. 

An eternity of boring purity?  No thank you.  In that scenario, the afterlife does not seem superior to this life.  Hence, the afterlife would allow us these base desires as well.  In that case, there will be inhabitants of Heaven who will take it too far, who will rob, rape, steal, etc.  As such, my brother is correct, and the afterlife isn’t really all that great – it’s just a continuation of this life.

On the other hand, I suppose it’s possible that our pure spirit forms simply will have none of these base desires in the first place, and therefore won’t be missing anything.  We’ll just float around for eternity in a state of complacent happiness.  That doesn’t really sound all that great, either, but what do I know? 

All I’m wondering right now is if the afterlife is eternal and is wonderful and we should all be very happy to leave this world and go to that one, why even live the meaningless life on Earth?  Especially if the afterlife is eternal.  I mean, think about it.  I don’t even remember a lot of what happened last year.  Do you really think that as millennia pass, you’ll remember the experiences you had on Earth?  Not bloody likely.

Create Your Own Excel Add-Ins

Yesterday I showed you how to create your own functions using Excel VBA, or Visual Basic for Applications. Today’s post will take this to the next level.

If you’ve played with creating and using your own functions, you may have noticed that once you close that workbook, you lose the ability to use that function. This is because Excel uses its built-in functions first, then looks to what is saved in the workbooks that are open. Excel doesn’t by nature save VBA code into itself; it only reads what’s been saved into the individual files.

Here’s an example. Say you’re working on a physics project for school. You’re working out of your spiral notebook from which you’ve been working all year. When you need to remember the formula for momentum, you can just flip through your notebook, find where you wrote it down, and use it to calculate the problem you’re currently working on. If you were working in a different book, you’d have to go back to this book to flip through and find the formula. If you didn’t have the book with the formula written on it (assuming you lack the cognitive capacity to memorize it), you wouldn’t be able to solve the problem.

So the excel file in which we saved our CAGR formula yesterday is the spiral notebook you’ve been taking to class every day, and in order to remember how to solve that formula, Excel needs to keep the spiral notebook open.

But, there is a way to make Excel memorize the formula. It is done with add-ins.

Generally speaking, when a program accepts add-ins, they have to be programmed using a higher-level programming code than VBA, and they have to be compiled in a very specific manor such that the host program is able to read and employ them. In the case of Excel, you are able to create your own add-ins just by saving a certain Excel file as a “Microsoft Excel Add-In (*.xla)”, which is the last type in the “Save As Type” drop-down when saving a file.

The methodology here is that any VBA code you write – be it custom functions or macros – will be saved in this .xla file, and when you include it as an add-in for Excel…wait, I’m getting ahead of myself.

Let’s start from the beginning. Go ahead an open up a new spreadsheet in Excel. Don’t worry about the cells within this worksheet. You can type whatever you want there, or you can type nothing at all. What you want to focus on is the VBA window.

On the menu, select Tools -> Macro -> Visual Basic Editor. You want a spot to type stuff, right? So click Insert -> Module. Here is our blank canvas where we enter in our custom formulas and whatnot. We’ll use a simple formula for today’s example. Type this into your module:

Function stupid_formula (myinput as integer)
   stupid_formula = myinput ^ 3
End Function

If you can’t tell, all this function will do is cube what you pass to it. If you were to close this window out, go back into your spreadsheet and type “=stupid_formula(2)” into a cell and hit enter, the cell’s value would be 8. However, if you were to close this workbook, open a new workbook, and type that formula in, the cell’s value would be “#NAME?” because Excel already forgot what you wrote in it’s spiral notebook.

Ok, so you’ve written “stupid_formula” in your module, and you haven’t closed your Excel file. Here’s where the fun starts. Close your module window to get back to the spreadsheet with which you began this journey. Save it (via File -> Save) as a “Microsoft Excel Add-In”. Once you select that as the file type, Excel will default to the “Add-Ins” directory, which is probably a pretty good place to save these. For this post, I’m going to save mine as “guj_formulas.xla”.

Now we just need to register our file as an add-in. This will tell Excel to open this file each time Excel is started. So to follow through with the previous metaphor, every time Excel gets ready to work, it grabs its trusty spiral notebook with all of your formulas in it.

To do this, go to Tools -> Add-Ins…

Click “Browse”, and double click your file. It adds it to the list of Add-Ins, and goes ahead and checks it for you.

And that’s it! Every time you open excel you can use the “stupid_forumla” formula. Alternatively, you can enter the VBA window at any time to view the formulas you have saved as an add-in, and alter or add to them. You will need to save your work using the save function on the VBA window in order to realize these changes in future instances of Excel.

Have fun with it, and remember, Excel works for you, not the other way around.

Create Your Own Excel Formulas

Having just completed a very large project at work involving six horrendous weeks of brutal data pulling, formatting, and analysis, and resulting in two 400+ page books and two 150+ page books of stuff that nobody will ever really care about, I am now free to write a new post! Today’s topic is creating custom formulas for Excel.

Excel is nothing if not configurable, but few people have the technical acumen, the desire, or the time to spend customizing it. Hopefully, this post will help to boost the technical acumen of the reader, but as for the desire and time, you will have to be the judge. I think you will find that performing some simple tasks such as this will free up much more time in the future, and such should feed your desire.

Ok, let’s get crackin’. The example I’m going to use throughout this tutorial is for a formula which we use frequently around the office, but of which few people are aware. It is for the Compounded Annual Growth Rate, or CAGR. When you have two figures occuring in different years, say, the population of a county in Florida, and you want to know how much this figure grew or will grow in each year in between, you use the CAGR.

Now I know what you’re thinking. The growth rate should be as simple as taking the quotient of the the difference between the first and last numbers over the first number, i.e. (x-y)/x, and then dividing the result by the number of years in between.

What that will give you is, in fact, a simple solution to this problem. If the population of Orange County is 100,000 in 2007 and is slated to be 200,000 in 2012, then it will grow (200,000-100,000)/100,000, or 100%.  Divide this by the number of years, and you get 100%/5, or 20% per year. However, if the population truly grew 20% per year, then the interest would compound each year, resulting in a final population of 248,832. This is the same way credit card companies make money. This is also the way anyone can make money just by having money.

Anyway, what we want to get at is what percentage of the total population would grow each year in order to reach 100% in five years. This is calculated using the CAGR. The formula looks like this:

CAGR(t₀,t_n) = (V(t_n) / V(t₀))^(1 / (t_n – t₀)) – 1

Where V(t₀) is the start value, V(t_n) is the finish value, and (t_n – t₀) is the number of years in between.

Written as a function, accepting start_value, finish_value, and number_years, it would look like this:

CAGR = ((finish_value / start_value)^(1 / number_years)) – 1

Sort of a mouthful, and quite a bit to remember. Even when memorized, it’s quite a bit to have to type every time you want to use the formula. It would be nice of Excel to include this as a standard formula, but they don’t, and that’s probably because only a small amount of users would ever need it. There are probably thousands of formulas that some people use every day that I will never even need to know. That’s why Excel is customizable. So, on to the point of all of this.

Wouldn’t it be nice if instead of writing that formula over and over again, one could just type

=CAGR(A1,B1,5)

into a cell and get the yearly growth rate? Well, you can! And you do it using Excel VBA, which stands for Visual Basic for Applications. Visual Basic is a programming language, but as the name implies, it is very simple and intended for the average or slightly above-average user. Once you understand some of the basic syntax, you can do just about anything.

You get to the VBA section of Excel by clicking on Tools -> Macro -> Visual Basic Editor. Once there, you need to create a module, or a file containing programming code, in your workbook. Do this by clicking Insert -> Module. You now have a blank canvas in front of you, and you are ready to create magic.

For our CAGR formula, type the following into this blank canvas:

Function CAGR (start_value as double, end_value as double, num_years as integer) AS double
   CAGR=((start_value / end_vaule)^(1/num_years))-1
End Function

What does this mean? Well, the first line tells VBA what the name of your function is, and what parameters, or variables, to expect. We know that in our calculation of CAGR, we need to know both values and the number of years that separate them. So we tell VBA that we will be using these values. The first two are accepted as double, which means they are registered as double-precision floating point value, which is a fancy way of saying the number can be really big, or with a lot of numbers after the decimal point. It’s the biggest number type that VBA can work with, and it’s probably way more than most people will ever need. The number of years is registered as an integer, which means it can be any number up to 32,767 and can only contain whole numbers (it will be rounded if necessary). This should be more than sufficient for any CAGR calculations we will do.

The second line uses the name of the function (CAGR) and defines its value based on the parameters. Since it is the only line within our function, the last line (”End Function”) tells VBA to stop working, and just return the value of CAGR to the cell in which the function was entered.

Just close your VBA window to get back into Excel and start to have fun! Create a spreadsheet like this:

Then type in the formula to calculate the five-year growth rate (btw, this could be stored as a function in VBA as well, but you will have to name it something other than “growth” because Excel has a built-in function with that name):

Then type in our CAGR function in the adjactent cell:

And hit enter. Ta-da! We have our calculation!

Turns out it would actually grow 14.9% per year, and after five years, it will have grown 100%.

That’s it for now. The thing to remember about these saved formulas is that they’re only available in the workbook in which they are saved (or in other workbooks so long as that workbook is open), so you’ll have to recreate the VBA code each time you want to use it (however, once it is calculated, the value will remain in the cell whether or not the function is present). In the next post, I’ll show you how to save your VBA code as an Excel plug-in, so it will be available every time you open Excel.