Microsoft Excel Tip of the day: Using IF and cell ranges

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:

=IF((G3&H3&I3&J3&K3&N3&O3&Q3&R3&S3&T3&U3&Y3&Z3&AA3&AB3&AD3&AE3&AH3&AI3&AJ3&AK3)
=””;”Missing”;”Present”)

Excel question

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:
IF(COUNTA(G3:AK3=0;”Missing”;”Present”)

=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 :-)

News or rumours or both?

Day4 is a Swedish company specialized in motion graphic and videos for TV, Internet and

Screwed? Picture belongs to Day4

so on. A week ago they got an idea (that right now makes any marketing company a bit jealous). They decided to create a rumour and see what happened with it.

That rumour was about Apple making there own weird screws and you can read all about what and how they did it directly from them here. They managed to get the rumour mill spinning either way.

The interesting thing is not that they managed to get a whole community (almost) talking about something made up but the perceived level of truth. The blogg Cult of Mac writes about it after 12 hours and then the ball was in motion. Yahoo, Wired and Mac World are among the news agents picking it up. Still, it’s a rumour and writers are sceptic. The longer from the (made up) source however…They note that when readers share and comment on these articles in there own media streams like Facebook, Twitter and Google+ – almost all questioning is gone.

Very interesting, if not so much of a chocker. Who values the news before it reaches you? I know a couple of journalists, both in old-school-paper-media and web publications. They might not always admit it but the faster our information wheel spins, the less source control. My humble guess is that that’s one of the main reasons we see so many articles stating another competitor as their source. Maybe we didn’t check it, but it’s not our fault, they wrote it first – sort of. Not that the source is a guarantee for accuracy, the Swedish wire service TT quickly gets a nation wide spread on their news. Both correct and incorrect ones.

Gets you thinking doesn’t it?

Disclaimer: the part about who was sceptic and the perceived level of truth is the opinion of Day4 and has not been thoroughly checked by me Ironic huh? :-)

My new companion: Nexus7

If you’ve read my posts a month or so back, you’ll notice that I’ve been on the lookout for a tablet for a while. I decided to wait until Google I/O before deciding and suddenly I have 3 Nexus devices at home. Unfortunately it’s not released in Sweden yet but thanks to an awesome friend in the US that was solved with just some additional shipping charges.

Nexus 7 ships with the brand new Android 4.1 also known as Jelly Bean. All in all I’m very satisfied and will try to give you a quick write-up on why. This is my first tablet so this will be both, why do you (well, I) need a tablet and why I think the N7 is a good option.

When the iPad was introduced a lot of market analysts was, should we say sceptical? “Who will ever use that thing”, “Tried already by other companies, didn’t work” and so on was the verdict. Personally I was a bit more optimistic. I started using “handheld computers” at my first job. A glorious Palm m500 was replaced by a Tungsten that later became a TX. I used the calendar (not with a lot of meetings though, I was a technician after all), started reading books in PDF and played som games. Some e-mails was written but not many. After moving on with smartphones I had no trouble answering the question “Who will ever use a tablet/pad (or one of the many names)”. I will! I was however not that sure about the price Apple asked for it, and I wasn’t sure if enough “normal” users would like to use it, normal as in less geeky than I.

Fast forward a couple of years. I never got an iPad, I didn’t buy into the Apple ecosystem and considered it slightly expensive while at the same time the screen on my phone kept groving. Now that I got a tablet I regret waiting so long so here is why you should get a tablet if you have the possibility:

  1. Reading e-books is cheaper, easier and by far cooler than regular books :)
  2. Playing games are fun, and much more so on a bigger screen. Just played through classic RTS “Z Origins” and loved every minute (except when my grunts were mocking me for loosing).
  3. Reading news and articles on a tablet is awesome. Some papers and magazines has their own apps, others are adjusted and available via one of the many apps for magazine reading like “Zinio Magazine Reader“. If your favourite news site haven’t made any mobile version several apps does a great adjustment job for you. Right now I’m trying to decide which one to use and run three different ones on and of: “Flipboard“, “Pulse” and “Google Currents“. Favourite right now is Pulse but they all have their pros and cons.
  4. Blogging, mail and just about any writing at all is much much easier on the pad than on a smartphone. It’s not as smooth as on a laptop, but I seldom pick up my computer on the metro. With a nice sync-app everything is of course available on all your devices (Google Drive, Evernote and so on and so on, trying them out as well).
  5. Control other devices, in my case I use it as a remote for my Mac Mini connected to my TV. Get one with infrared port and you can control basically every piece of your TV/home theater setup.

There you go. Now we move on to why Nexus7 is a good choice. This is of course much harder for me to answer since I’ve only owned this tablet but..I’ll give it a try anyway and you just remember my limited experience with other tablets :-)

  1. It’s fast, really fast. I’ve been testing out both a Xoom and a Samsung tab before and this is way smoother (granted, it’s released much later).
  2. You get a whole lot of bang for your buck because the Nexus 7 is cheap, no really, it’s cheap. Starting at $199, compared to the new iPad $499 (yes, the iPad have features the N7 lacks, more on that later) Samsung Tab 2 7″ starts somewhere around $250-299. Finding USD prices from a Swedish IP is always a hassle :)
  3. I absolutely love the form factor. 7″ is perfect for me. As long as I carry a bag (90-95% of the times I leave home) I got it with me. A larger more expensive tablet might be left at home more often. Holding it with one hand is comfortable, not just for a couple of minutes. That’s great if you’re using it for reading. I need to be able to stand up on the metro, hold on to the rail with one hand and still read, like I can with a paperback without trouble. Big enough to be a huge leap in usability compared to my Galaxy Nexus but small enough to be carried and used all the time, to sum it up. The material on the back deserves a mention as well, smooth and rubbery without being sticky, very well chosen by the designers!

    One handed news! Here using Flipboard in portrait mode.
  4. Customization, this goes for all Android tabs. I will probably use my tab a bit different than you so why would we like them to look the same? Of course, you could just let it be and it will work just fine but the ability to change keyboard and features is a core feature for me. That includes widgets which are great on Android phones but is really awesome on a tablet.
  5. As a Nexus device it comes loaded with the latest Android version, the already mentioned Jelly Bean 4.1. It will also be updated directly from Google hopefully quick and easy :)

But… The Nexus7 isn’t perfect of course. If these are deal breakers for you, keep on looking.

  1. If you have an iPhone already, you have to consider the ecosystem factor. Syncing between devices, already bought apps etcetera.
  2. Films likes big screens. If you plan to watch a lot of video you might want to consider a bigger device.
  3. In order to keep the low price on the Nexus Asus and Google removed some common features. There is no 3G/4G/LTE version, you get Wifi and that’s it. There are no camera on the back, just the front facing one. Like all devices I’ve tried the front camera is aimed towards video chat and similar, not snapping good pictures. They also left out the ability to add memory through external card. An unfortunate trend that we see among phones as well.
  4. I would’ve loved an infra red sensor to get rid of my three remotes (sound, TV and IPTV Set Top box), just like the Sony Tab S and the just announced Samsung tab 10.1 has.

A bit longer than I intended, even though most of the post was written on the Nexus itself. That’s how nice typing on it is :-)

Make your Galaxy Nexus yakjuxw to a yakju

imageI might translate it later but so far this is only for Swedish readers who are still waiting for their Jelly Bean update (the forumpost linked in the bottom are in Swedish). Chances are that your phone has the build “yakjuxw” that will get the update later. Have no fear, transforming it to the international “yakju” is quite simple.

Just follow the instructions from the link and get ready for Jelly Bean: http://www.swedroid.se/forum/showthread.php?t=60862