& Let Us Begin! – Demonstrating Excel Logic
I am asked more and more often to give intermediate-advanced Excel classes. Aimed at people who are relatively comfortable with Excel and ready to go to the next level. Most of these students have made attempts at figuring out new formulae themselves, and usually ended up a bit frustrated!
For the last year, I have begun each class showing the “&” formula which joins values together. It is not a formula that would be used every day, or even very often. So why show it to them?
It is probably the easiest and quickest formula to demonstrate computer logic in relation to formulas. A few minutes spent on this formula usually helps the students get a better understanding of why longer, more difficult formulae don’t work.
Too often we curse our computers for ‘not working’, more often than not, the computer is working but unfortunately there isn’t a mind-reading app yet that will perform the task the user wants but has not asked for.
Here is a simple demonstration:
- Cell A2 has the number 5
- Cell B2 has the number 6
- Cell C2 has a formula in it, joining A2 and B2 (5&6 = 56)
- Cell D2 is displaying the formula that has been entered in C2
This makes sense to most people – 5&6 is 56. Straightforward so far. However, if we change this to words, like a name, our formula looks different
A2 has the first name, Alan. B2 has the surname Anderson. C2 gives us “AlanAnderson” though. What happened here? Why didn’t it give us “Alan Anderson”? The answer is simple – because we didn’t ask for “Alan Anderson”. We asked Excel to join A2 and B2 and that is exactly what it did.
If we want a space between each name, then we ask Excel to join A2 & blank space & B2
When we ask for what we want, we get it.
In our first attempt to join 2 names, the formula worked, but the user didn’t.
Have you ever heard of the acronym GIGO (Garbage In Garbage Out) or SISO (means the same thing, but the S stands for something else)? It’s time to take the garbage out!!
