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")
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 :-)
Windows 8 first impressions
Almost time for a new version of Windows! Microsoft has decided to stick to the numbering they started with Windows 7 so this time it's simply Windows 8. If the name is something along the lines what we expected the new interface however isn't.
I installed the Consumer preview a while back (perfect timing, the winter did an encore while my partner was out of town). Installation is really simple and you shouldn't need any instructions there though it might be worth mentioning that it will require you to reinstall all programs and possibly some drivers as well. Personal files will be left untouched if you choose it but make sure that you have a back-up just in case.
First thing that greets you when you start Windows 8 is a new login screen. A nice update that looks good but it's still just a login screen. What happens behind it is more exiting however. In Windows 8 you can add your MS Live account, just like you would add your Apple-account to your iPhone/Pad or Mac or your Google account to your Android device. This is actually quite cool and will probably do a lot for Microsoft in their struggle to have an eco-system just like Apple. Windows is the most installed computer operative system in the world and what they are trying to do (in my humble opinion) is to establish a feeling that you really should get a Windows Phone device to go with your computer (and a tablet of course...) instead of going to your local carrier and buy the new iPhone or Android. It's to early to predict exactly where this will lead but already in this preview you can start synchronizing settings, installed apps (from the Windows Store) and more will likely follow. Next step here can be very exiting with even more information being accessed in the cloud .
Now, the new UI: Metro. We can call it the new Start-menu but that wouldn't be enough, we could call it the new desktop but that would be very confusing since you still have a desktop. We should compare it to the UI on Windows Phone but since that won't tell you much (unless you happen to be one of the early adopters, it's interesting but not a big sale success yet). To me it's a logical evolution, from Windows Vista with the Sidebar, Windows 7 and its widgets, iOS app concept and Androids desktop.
You get a tile-work consisting of normal shortcuts and active shortcuts which works a bit like widgets, displaying information from the app (latest e-mail or message and such). This takes some getting used to and I still find myself looking for the start-menu from time to time when I want to launch something but as soon as I stop using an older version at work I'm guessing that this will change. It looks a bit more modern, should adapt very well to touch screens (tablets anyone?) and more than that: Microsoft actually tries to bring something new, they don't act on the competition from Apple by making glossy icons and changing the taskbar to a dock, they try to evolve how you work with your computer. Kudos! This won't necessarily give them praise from the standard customer but in the end it might be exactly what Microsoft needs to dodge another "but why should I have to update again" debate. Somehow there is still something missing, it feels not beautiful. Some work to do yet for Microsoft.
The new Metro UI
All in all, my first couple of weeks with Windows 8 has been quite nice. I find myself using small clever functions in the UI (like the Alt+Tab similar feature of the top left corner) that didn't thrill me the first time I found it. Much can and probably will change before Windows 8 is released in the end of 2012 but I already look forward to it.
However, Metro is not without flaws and I have found myself quite annoyed a couple of times. One thing that bugs me is the decision to make the setting entirely context based. That means that depending on the app your in at the moment you get different options from the settings link. Just like when working on a mobile device. Not only a bad thing but even on my 13" laptop screen I can spare room for a link to the Control panel regardless of which app I'm in. By the way, you find it if you select settings in the "activation area" when your in the regular desktop.
The active area is shown on the right
Annoyance number two, the gap between desktop and Metro sometimes feels huge. Searching after the app or program you want to run is not very intuitive and finding the same app in the regular desktop as in Metro is not always simple. More than that, some apps can work differently in Metro and desktop for example Internet Explorer hides the address bar and navigation buttons in the Metro view. Not only that, IE will run in different instances if you start it both in Metro and desktop, Chrome also acts a bit weird and tends to start double instances now and then if I started it from Metro. So far I haven't found a lot of Metro-fied apps that works great with mouse and keyboard but that can change well before release.
A lot of first impressions :)
If you're running the preview and want the normal star-menu back:
Run -> regedit; HKEY_CURRENT_USER\Software\Microsoft\ Windows\CurrentVersion\Explorer and change RPEnabled from 1 to 0
Mac Mini, incoming!
My new baby
It is however a beautiful Mac Mini that hopefully will bring my never ending media playback issues to an end. I’ll give you the background today and the update on how it went when I get setup and tried it out a bit.
I have tried multiple ways to playback my media. I don’t think I’m being unreasonable, I just want to be able to play both my digital videos as well as the ones on disc. If possible I would like to be able to play my music as well, all of course through my Home Cinema system (not that fancy but now that I got it, it sure as hell shall be used). So far I’ve tried…
TvIX 6500 HD: A cute little box that was my last attempt at salvation. It should’ve been able to give me Full HD (1080p) both from the internal HD as well as over the network from my server. Well, it didn’t. Not very reliably at least. Xvid/Divx and DVD images worked just fine though. Managed to sell it without a major loss so, problem solved. Music interface was so bad that I only used it a few times, hence a separate solution for that, and no disc drive for DVDs and Music.
Home Cinema system with DVD player: Aside from not playing my digital videos it has so far spent most of the time since my investment back in the shop for repairs. Reason for that is that first the DVD playback wouldn’t work (or the remote), got it back with a new remote. Remote works fine, plays DVDs the first few tries and then both CD and DVD playback stopped working. Back to the shop. The biggest drawback however is that I need another device to play music or digital video.
XBOX360: I have a Microsoft Windows Home Server. One would think that MS figured out that their users might want to use their shining new XBOX as a Media Center (Hey, that’s what everyone did with the old XBOX). But no. XBOX360 is quite lousy as media center. Doesn’t play xvid/divx without some hacking (works but…strangely hard to accomplish), doesn’t play DVD images at all and the music interface is as slow as a turtle without legs. Microsoft, I did everything you wanted me to, got the server, Windows 7 and the XBOX360 and still I need workarounds or 3rd party software to free my files. Not good marketing strategy.
HTPC: Tried some different solutions (running the PC version of XBOX Media Center was one) under different OS version (XP, Vista and Windows 7). The media center capabilities are still crippled and I often found a need for a full keyboard to make things work. What I haven’t tried is to buy a full HTPC of the shelf. Since my experiences from running both stationary and laptop computers as HTPC isn’t exactly great I’m not that keen to pay the huge stack of money that a decent looking, decently quiet, remote controlled and working HTPC costs (in Sweden you’ll have to pay roughly 800-1000$). The reports on usability issues, regular playback problems and angry family members not being able to start a simple DVD didn’t encourage me either.
So what do I use today then? Feels ridiculous but I’m back to using my old XBOX. Yes, that’s correct, the one launched in 2000-2001 something like that. It’s huge, ugly, sounds like a tractor, can’t play any HD material and cheap me hasn’t got the cordless remote. I use the regular joypad instead. It does however presents all the capabilities I ask for in a media center, plays (almost all) DVDs and digital video files. Since it’s really quite outdated it’s not exactly foolproof but it works most of the times, it even plays videos in compressed archives. The music interface is not awesome but works, it’s just that the joypad isn’t that great as a remote.
I write to extensive for a blog, shame on me and poor readers. That was the background, the next time I might have become a Mac fanboy, who knows? :)
>Windows 7 – what to like
>
After a short vacation I'm back.
Those of you that are returning visitors (and you actually are a couple, thanks for that) know that I'm looking damn hard right now to write about something that don't come from the company in Seattle which I won't mention by name this time (one post without it should be doable don't you think?).
So I figured it's time for the post about Windows 7, it's really a must for every techie blogg.
I've been using Windows 7 on one of my computers since the Beta and have been running the Release Candidate since, well it's release. So why haven't I posted anything before?
Simple, it ain't that much to write home about.
It's fast, it's sleek, it's shiny and I absolutely love it.
With that said, little has changed on the outside. That's not entirely true either, they changed the taskbar (love it) and they gave the start-menu a tweak (nice) but it's all minor changes to a lot of things. Combined they give a new and very nice feel to it that in my opinion is quite hard to put your finger on.
One neat thing that I'm guessing that everyone using it haven't noticed is the new way to work with windows. You can maximize a windows by dragging it to the top of the screen and vice versa but that's not something I use everyday. What I do love is the ability to "pin" windows to the sides. By just dragging a window to the edge of your screen you can make it take up ½ the screen, do it on both sides and you just got yourself a split-desktop. This is something that I find very useful on large screens, try it! I read an article in The New York Times a while back stating that big screens increase efficiency. I was a bit sceptic then, the research was initiated by NEC for gods sake :) But with this small function it just got easier to actually use that big screen of yours so maybe on some tasks.
Touch-ups in the following areas that annoys me with Vista:
· UAC (you know, all those pop-ups asking you if: you really meant to do what you just did, and then: are you sure? yes dammit I wouldn't have clicked yes the first 5 times if I wasn't sure!)
· Sleep, Sleepmode, Shutdown, Have a nap, Go fore a pizza. Who remembered the translation of all that Vista options? This is much clearer in W7. (This might have been a bigger issue on non-english Vista, I don't have any English version installed anymore to check with)
All in all, go for it! I most definitely will, there are no down-sides with W7 compared to Vista. It even requires less from your hardware. If you're an XP-fanboy well...if you can run it you should still try it, you won't be disappointed! Microsoft just announced that they relaesed Windows 7 to manufacture, also known as: Windows 7 rtm. October is the month if I'm not out in the blue!
The picture at the top is an Icon in Windows 7, if the dear Microsoft don't want it here in this very positive article...well you just let me know.
Can't find the NYTimes article, just a reference: http://nwitimes.com/app/inbusiness/?p=489
@ETysen at Twitter
Categories
Archive
- May 2013
- April 2013
- March 2013
- August 2012
- April 2012
- February 2012
- July 2011
- March 2011
- February 2011
- November 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- April 2009
- March 2009