I’ve been pretty intrigued by Apple computers for a long time and the Mac Mini that I have hooked up to my TV since 2010 only increase my interest. Last time I got a new laptop (early 2011, I know crazy!) the only thing that stopped me from a Mac was a company policy.
This time though, policy gone and I immediately started looking at differences between Macbook Pro and Air. Still think they got good hardware paired with an interesting OS. One complaint though on OS X, I often feel that I’m supposed to either use the interface exactly like Apple want me to, or be prepared to hack away in the terminal. There’s no in-between and for a power user (as I would call myself) but not that keen on going back to commandline instead of GUI that gets a little crippling.
Even so, I wanted to go for the full OS X experience, using it 100% instead of only on my second computer. So why did I instead order a Dell XPS Infinity? In the end what tipped the scale was…Office. My work consists somewhere around 80% of production or reading different Microsoft Office Documents. To confine myself to an OS where the latest version is Office 2011 seems nothing short of stupid. Sure, I got the online version through my 365, there’s a fully working Apple production suite, or one of the free options like Open Office or Google Apps but I tend to end up preferring Ms Office.
As long as Microsoft manages to stay ahead of the competition with Office as well as keeping the latest versions exclusive to Windows…that software is my number 1 priority.
Today I had one of thoose lovely “Ah! Never considered that before!”. I got a question from a colleague that had a formula that looked like this:
Note that this is no hardcore crazy hackaway thing. Just a common “simple when you think of it”. I’m not claiming to be giving away awesome wisdom here. :)
Apart from not beeing very easy understandable it required a lot of handy work to stay up to date since columns where added periodically.
What it was supposed to do was check if all the cells between G3 and AK3 were empty, write “Missing” if they were and “Present” if they weren’t. If you have the energy to check you will notice that not all cells between G3 and AK3 was included. Some were lost when new columns were added.
Easy! I thougt, and entered: =IF(G3:AK3=””;”Missing”;”Present”)
Unexpected trouble. As far as I can remember I never tried using a range with the IF function that way before, lucky for me since it doesn’t work (neither do named areas).
I figured that I have to calculate a value from these cells so I asked what would be in the cells if present? Unfortunately the answer wasn’t digits. If it were numbers a simple: =IF(SUM(G3:AK3)=0;”Missing”;”Present”) would work.
Still, this should not be too tricky…and it wasn’t. As soon as I stoped thinking on how to identify the cell content at least (yes, that took me some beard scratching minutes).
Instead I count them. This is what I came up with:
=COUNTA simply counts the number of cells with values. Since I specify a range Excel will also update the formula to includedcells created within that range.
As with most questions about Excel, the solution was easy, you just have to think about it first :-)