Technology

Fighting with formulae in Excel

Excel. Love it, or hate it, there are few of us in white collar jobs who don’t have to grapple with it from time to time and, earlier this evening, after OneNote had yet again decided that syncing shared notebooks via our ageing SharePoint platform as not going to work, I needed to come up with a new way of storing actions and decisions for the management team I’m one member of.

I would have liked to create a SharePoint task list but I don’t have the necessary permissions, or the inclination to find out who can give me them (and then to justify why I need to do this). Which led me back to the tool upon which so many business processes are built: Excel.

On the way, I grappled with some formulae, so I decided to knock up some notes for the next time this happens – or the next person who needs to do something similar…

Nested IFs, SEARCHes and ISERRORs

My Excel workbook is simple: two tabs (“Decisions” and “Actions”).  The Decisions tab looks after itself: three columns with a date, a decision, and the rationale behind it.  Actions is a little more complex (read over-engineered).  I have date, status, action, owner, target date, and notes. Then I want the status to be auto-populated based on the notes (If the notes say complete, then status is “Completed”; if the notes include the word cancelled then the status is “Cancelled”, otherwise the status is “Open”).

This was the first challenge.  Excel doesn’t have an ELSE in its IF function, but you can nest IFs as follows:

IF(logical_test, [value_if_true], (IF(logical_test, [value_if_true], [value_if_false]))

(or use one of the LOOKUP functions).  I combined this with the SEARCH function to look for instances of the word “Cancelled” in the notes and everything was working well with results returned for a match on “Complete”, or an instance of “Cancelled” but then the catch all value if false always returned a #VALUE! error.

As SEARCH will always return #VALUE! if the search string is not present, I started to look for workarounds.  Some googling told me that I need to test for an error condition with the ISERROR function but that didn’t seem to solve the issue. So I reversed the logic – check for the error first andthen take action accordingly:

=IF(F2="Complete", "Completed", IF(ISERROR(SEARCH("Cancelled",F2))=TRUE,"Open", IF(SEARCH("Cancelled",F2), "Cancelled")))

It may be easier to understand this in some form of pseudocode:

IF cellvalue="Complete"
{
status = "Completed"
}
ELSE
{
IF cellvalue doesn't contain "Cancelled"
{
status = "Open"
}
ELSE
{
IF cellvalue contains "Cancelled"
{
status = "Cancelled"
}
}
}

Conditional formatting based on cell contents

The next thing I wanted to do was to format each item in the action list according to it’s status and/or date. I wanted to colour completed actions grey; mark cancelled actions as grey and strikethrough the font, then add a red/amber/green status on open actions according to the target date. Conditional formatting did the trick here but I wasn’t sure how to highlight a row based on the contents of one cell in the row.

I found the answer on the HowToGeek site and key to this is not worrying that the reference to the cell used to test the logic is a single cell – Excel is smart enough to work out that you need the same column on the next row when evaluating the condition there.

So, I ended up with six rules, three of which use the TODAY function to evaluate whether the task is overdue or not (or due today):

Formula Format
=$B2="Cancelled" AaBbCcYyZz
=$B2="Completed" AaBbCcYyZz
=$E2="" No format set
=$E2<TODAY() AaBbCcYyZz
=$E2=TODAY() AaBbCcYyZz
=$E2>TODAY() AaBbCcYyZz

It may all be a bit OTT, but I for one work in a very visual manner – I need documents to show me something, not just rely on reading the text.  Hopefully this will work out to be a useful tool for us…

Motoring Technology

Short takes: managing Bluetooth devices in a Volkswagen; seating Micro SD cards in a Hudl

In an attempt to close some tabs in my browser and transfer some notes to blog form, another “short takes” post…

Deleting paired Bluetooth devices from a Volkswagen MFD

The advice is for a 2012 Passatt but it worked for my Tiguan and probably for a Golf, etc. too (there’s a maximum of 4 connected devices – although only one can be active at any one time):

  1. “Go to the Phone on the center information panel
  2. Go to Users and you will the the phone names
  3. Scroll to the phone you want to remove
  4. Push the OK button on the steering wheel
  5. You will be offered some options, scroll down to Delete and hit OK”

Incidentally, Know Your VW is a useful site (although it is intended for the North American market).

Patience required inserting a Micro SD card in a Tesco Hudl

After buying my Tesco Hudl a few weeks ago, I decided to get a memory card to expand the on board capacity (e.g. cary more music/video with me).  Of course, Tesco is the last place I want to buy accessories like that and I picked up twice the capacity for half the price (or something like that) at MyMemory.co.uk.

The problem came when I wanted to insert the card into the Hudl.  For a while I seriously thought I had a faulty tablet that would need to be returned (a bit of a shame after getting it just how I wanted it, including rooting and customising…) – I kept on pushing the card in but it would spring straight out again.  It turns out that the Hudl’s card slot is very deeply recessed and some long nails (or even a knife) might be needed to push it in far enough and get it to stay in place!

 

 

Exercise Technology

Short takes: special cyclists’ edition

As this post goes out, my beloved Bianchi C2c Via Nirone 7 should have just emerged from the workshop after its first service.  Strava says I’ve ridden it for around 1200km and, as I rack up some miles in my training for several events this year (London-Paris in June, Wakefield to Manchester over Holme Moss in July, and the Ride London-Surrey 100 in August), it seems a good to point to take it back to Epic Cycles to give it a once over…

I’ve also got a few open tabs in my browser with cycling-related bits and pieces I mean to blog about… so here’s a special cycling-themed “short takes” blog post…

Editing GPX files

Every now and again, it’s bound to happen… you forget to stop the cycle computer/app on the smartphone and the resulting GPS eXchaneg format (GPX) file has a block in the middle where you were waiting for your mates to arrive/sitting in a coffee shop/whatever.  Then there are times when the GPS goes haywire and thinks you did 87.8kph down a hill, or when it just straightlines a corner. In those instances, you might want to edit the file.

Thankfully, GPX files are not binary – they are just another XML schema – and the OpenStreetMaps Wiki has advice for editing GPX files in a text editor.  Hack around to your hearts content, then upload to your social sharing site of choice.

Searching for bike serial numbers

We’re off to Centre Parcs later this year, and I needed to provide details of our bikes (useful for insurance purposes too).  Once again, I was searching for the serial number for my mountain bike and, once again, it was eluding me so, whilst it’s unlikely to apply to everyone who reads this blog, here’s the link to Trek’s advice on where to find your bike’s serial number.

 

Technology

Time for another #MKGN

Woah… where did the last three months go?  It’s Milton Keynes Geek Night (@MKGeekNight) again tomorrow and I haven’t blogged about the last one yet (for that matter I can’t find my notes – in Evernote, OneNote, Keep, or anywhere else).  It’s a good job I don’t rely on this blogging lark for a living…

Luckily, the MKGN team have details of past talks on the website and the audio was recorded for posterity too.  As always, all of the speakers were great – but I particularly enjoyed hearing Christian Payne (@documentally), who I’ve followed for a while on Twitter, gave a really engaging talk about Storymaking.

So, who’s talking this time around?  Keynotes are from:

  • Andrew Clarke (@Malarkey) on the (I imagine somewhat provocatively titled) subject of “take your stinking paws off my design you damn dirty developers”.
  • Relly Annett-Baker (@RellyAB) on “future perfect tense: creating good content for an impefect web” (I think Relly has been promised before – so looking forward to hearing her at MKGN #8).

Then, there are 5 minute talks from:

  • Jeremy Taylor (@jdt_me), talking about “distributing the future more evenly, with JavaScript”.
  • James Bavington (@jamesbavington), on the topic of “knowing you know nothing”.

And, whether you’ve attended MK Geek Night in person, or enjoyed the recorded talks on Soundcloud, maybe you’ll consider voting for MKGN as “Grassroots event of the year” in the .Net Awards 2014?

Right… now lets hope Virgin Trains get me back from Manchester in time…

Waffle and randomness

The importance of good communications for project success

Once or twice a month, I travel to Manchester for work.  I usually get around by tram (Metrolink) when I’m there – there’s a stop close to our office and its convenient for travel to/from my hotel and the railway station.

Manchester’s tram system is being upgraded at the moment and, last week, I was amused by posters asking passengers to “bear with us whilst we make Victoria posh”:

As much as my southern sensibilities (actually, I’m from the East Midlands) cringe at the idea of “making something posh”, in fairness to Metrolink, they do have a great series of communications around their project (and whoever is responsible clearly has a sense of humour). One of my favourites is reproduced below:

“Dear [customers]
It can be fast.
It can be slow.
You can measure it in feet, inches, weeks, months and years.
And, occasionally, in leaps and bounds.
It’s going to take a little time.
And a lot of hard work.
But, rest assured, it is moving forwards.
Creating something better for us all.
So thank you for your patience.
And while our network is undergoing this transformation, we’ll keep you up-to-date with information.”

I like that poem, and I started to think about other applications for its use… something to consider for my next IT transformation project, perhaps – because good communications are vital to project success (and so many updates that I see are just dull walls of words).

Technology Waffle and randomness

Fixing the Apple iOS SSL bug on a jailbroken iPhone, without upgrading

My iPhone (4S) is jailbroken.  I won’t go into the details of how I did it as it changes with every release but I currently run iOS 7.0.4 and I used the Evasi0n method. Unfortunately, Apple has a pretty shocking bug in that version of iOS which means SSL transactions are not secure.

I didn’t want to go to the hassle of upgrading the OS, then jailbreaking again and, luckily, there is a workaround for fixing the iOS SSL issue on jailbroken iPhones without updating to iOS 7.0.6 (or 6.1.6) - although by the time I get this post written, 7.1 may well have hit the streets…

  1. Launch Cydia
  2. Switch to the Manage page, then select Sources
  3. Add Ryan Petrich’s repo (URL is http://rpetri.ch/repo)
  4. Go back to Manage and select Sources
  5. Search for SSLPatch and install as usual
  6. Restart SpringBoard when prompted

To test the patch (before and after), use the Goto Fail Apple SSL bug test site.

Technology

Removing Tesco customisations from my Hudl

Just over a week ago, I bought a Tesco Hudl. It was an impulse purchase but the reviews were good considering the low price point (Gizmodo, Engadget) and that £119 price tag meant the risk to me as a consumer was low (considering what I paid for an iPad 4 years ago, which admittedly is a premium device but has hardly stood the test of time…).

After a couple of days I knew I’d made the right decision – I’ve been disappointed with Android on my phone but on a tablet it’s really usable (the Hudl uses Android 4.2.2 JellyBean) – and Tesco have provided an almost stock distribution. Even so, there are some “customisations” – a few apps and widgets to try and encourage more Tesco shopping, and a [T] launcher button in the bottom-left corner of the screen.  The apps and widgets are easy enough to move out of sight, but I really wanted to lose the Tesco Launcher [T]…

Rooting the tablet

Step one is to “root” the operating system – i.e. to give myself full access to all of the files and folders on the device.  Paul O’Brien (@paulobrien) has a great post on his MoDaCo forum on rooting the Hudl.

I fired up my Linux netbook (it still has some uses!), downloaded the ROMraid archive for the version of the Hudl software my device was running (JDQ39.20131016.200812) and extracted it but running sudo ./flashroot.hudl.linux.sh returned command not found.

After scratching my head for a few minutes I realised I also needed to chmod 755 flashroot.hudl.linux.sh to make it executable.  I tried the script again but this time the response was cannot execute binary file – it seems that the version of rkflashtool.linux included in the ROM download was for 64-bit Linux and my netbook only has a 32-bit installation.

Plan B

Being a good geek, I have a pile of media waiting to be “sorted out” including some Linux live CDs, so I fired that up on another PC and this time managed to flash the Hudl (I used an Ubuntu 13.10 live CD – CentOS 5.5 refused to play).  If you’re having trouble getting the device into the right state, then check out Matt Foot (aka @glossywhite)’s advice on MoDaCo:

  1. Turn Hudl off.
  2. Connect via USB to PC.
  3. Hold down Volume Up (+), push the reset button for one sec (recessed hole with gold dot in it) and continue holding the Volume Up button.
  4. To check that this has put the Hudl in flashing mode, and to verify that it isn’t merely in charging mode, release Volume Up after around 10 seconds and tap once on the power button; if you see a battery, it has failed, so try again.

I found that the Live CD didn’t want to execute anything from a USB stick or local hard disk in the PC (Windows file systems), so I copied the following files to /home/ubuntu:

flashroot.hudl.linux.sh
rkflashtool-linux
system.backup.img

I also chmodded and to make them executable, then ran sudo ./flashroot.hudl.linux.sh

At this point, I saw lots of lines like:

rkflashtool: info: writing flash memory at offset 0×00000000

(up to 0x00341fe0) and then:

rkflashtool: info: rkflashtool v5.1
rkflashtool: info: Detected RK3188…
rkflashtool: info: interface claimed
rkflashtool: info: rebooting device…

The device then restarted in charging mode, and I powered it up as usual. Paul’s instructions say to install SuperSU from the Google Play Store but it seemed to be installed already; however I did update it.

Removing Tesco customisations

As I mentioned at the top of the post, the Hudl comes with a Tesco Launcher icon in the bottom-left of the screen and is also pre-loaded with a number of apps for Tesco services. Paul O’Brien has posted one method of removing the Tesco Launcher [T] but I decided to use a variation of Mr Akufu’s method, with Paul’s hacked SystemUI.apk file (which can be used with the JDQ39.20131016.200812 firmware):

  1. Extract SystemUI.apk from SystemUI.hudl.zip.
  2. Using your method of choice, transfer the file to your device – I used Dropbox, and then selected the option to Export, then Save to SD card (Internal Storage) but others have suggested Bluetooth, or ADB.
  3. Install Rooted SSH/SFTP Daemon from the Google Play store and start it.
  4. Connect using your SSH client of choice (I used PuTTY) over a Wi-Fi connection to the Hudl.
  5. Login as root, with password abc123
  6. Issue the following commands:
    su
    mount -o remount,rw /system
    cp /system/app/SystemUI.apk SystemUI.mybackup.apk (I didn’t do this but it would have been sensible…)
    cp /storage/emulated/legacy/SystemUI.apk /system/app/SystemUI.apk
  7. After this, the Hudl should reboot.
  8. Following restart, the Tesco button should be gone – use Root Uninstaller to disable (not remove) the Hudl Updates package.

If you want to go further, there’s another MoDaCo post that talks about removing all of the Tesco application packages… and more information on which Android packages are safe to remove at Android Central.

Technology

iOS Mail app lost its settings? Try this…

Over the weekend, my iPhone suddenly “forgot” how to access my email. I hadn’t changed the configuration (just left it on the kitchen counter for a while!) so I was pretty confused.

I tried to add an Exchange account for my Office 365 connection but it said I already had one and, sure enough… it was still there in Settings. So I turned to Twitter, where @TheoCarpenter came back with a suggestion:

Sure enough, that did the trick (if you don’t know how to kill tasks on iOS 7, this post will help) and within a few seconds I was reading email again. Thanks Theo!

Technology

Unable to install Xbox Live Arcade games on the 360′s hard drive

I’m not a big gamer, but I do have an Xbox 360 and there may be some video evidence on the ‘net of me sampling a game or two at an MVP event a few years back…

These days, much of the console’s use is from my children and, last weekend, my 9 year-old son was keen to buy Minecraft for the Xbox 360 after spotting it on sale in Sainsbury’s.  After coming home and researching, including prices elsewhere, we decided that it was a good deal and I took him back the next day to buy a copy.  One happy little boy.

Unfortunately, when we got home, I was having difficulty installing the game on the Xbox’s hard drive. I do this to avoid wear and tear on the DVD drive in the Xbox, as well as to keep down the noise whilst playing. I expect to supply the optical media to verify that we have a copy (for any piracy purposes) but, from then on, our games run from from the hard drive.

Unfortunately, that option didn’t seem to be available for Minecraft and it seems that it’s the same for all Xbox Live Arcade games. Ludicrous – bizarrely it’s possible to buy the game online and download it to the Xbox via that route (and it would have saved some of my son’s pocket money)!  Unfortunately I don’t think this counts as “faulty” for return to the retailer…

Technology

An evening with Sony, at their 2014 consumer electronics product preview

A couple of weeks ago, I found myself heading to Brooklands, site of the world’s first purpose-built motor racing circuit, except that the purpose of my visit was not to experience any form of motor sport, but to spend an evening with Sony, at their UK headquarters, taking a look at the consumer electronics products that the Japanese giant is bringing to market in 2014.

Working for another Japanese technology giant, as I do, it’s easy to forget just how big Sony is in the consumer electronics space.  Happily, in the UK, we don’t really compete (except maybe around PCs – and even then we focus on different markets). As news since my visit suggests that Sony is looking to dispose of its Vaio PC business and transform the TV business into a wholly owned subsidiary (perhaps to resolve the issue of the innovators’ dilemma?), I’m happy that I could learn about the sound and vision, photography and computing devices that Sony is bringing to market this year without any conflict of interest.  And Sony started the evening off by telling us how they are concentrating on the user experience – on the best picture and sound quality – be that for televisions, cameras, projectors or other digital devices.

TV – forget 3D – 4K is where it’s at

It has to be said that Sony’s 4K TVs are stunning.  I first saw 4K Ultra HD images whilst visiting The Design Museum late last year and my trip to Brooklands re-enforced my view – whether it’s for watching films or sport.  Quite how I’ll be able to receive a 4K signal at my house is another issue (I have “up to 8Mbps” ADSL2) and whilst I like the idea of a 65″ TV, our living room is not really large enough… but hey! (It should be noted that Sony’s X-Reality processing engine can upscale some content too).

Sony X9Sony explained some of the technologies that their mid-high end 2014 TVs feature and it’s clear that it’s no longer just about being “super slim”.  The quest for enhanced picture and sound quality includes a range of technologies such as:

  • X-Reality PRO image enhancement for increased realism, texture and a more refined output
  • Triluminos imaging (launched last year – but now with increased colour range and much improved viewing angle – as shown in this image from Sony with, from left to right,  a 2013 Sony TV, a 2014 Sony TV and a Samsung TV)
  • X-tended Dynamic Range – improved brightness, whilst retaining detail and colour.
  • Long duct speakers with a new wedge shape to increase speaker capacity and sound quality – including software to adjust the settings depending on whether the panel is wall or table mounted (wall-mounted units use the wall for reverberation – I pity the neighbours!)
  • Front mounted speakers on some models for better sound direction, a magnetic fluid system as coolant and conductor for efficient sound transfer – and an RF-connected subwoofer option for those who don’t want a 5.1 system.
  • ClearAudio+ sound processing, to separate dialogue from sound effects, reduce/increase sports commentary volume, or provide virtual surround sound.

And, when one Sony representative was asked a question about the future of 3D TV:

“Our focus is 4K”

I think that says it all really (the 3D glasses for my TV have never come out of their box)!

Smart viewing

I have a mid-range Samsung TV, which, on the whole I’ve been very pleased with but I do have to admit that the SmartHub is a little less smart than I would like at times. Clearly Sony seems Samsung as a leading competitor (their competitor comparison units are all Samsungs!) and, from what I saw of the developments in Sony’s Bravia software, it seems that they have a much better user interface – and an interesting approach to control with their “One-Flick”gesture-based remote (a standard remote is provided too). Whilst some of the apps seem a little gimmicky (e.g. “Football mode” for “less ghosting and more immersive viewing” because of Sony’s tie up with the 2014 World Cup), the usuals are there too (iPlayer, etc.) as well as Sony’s Music Unlimited and Video Unlimited services.

The features I found most interesting were Social Viewing (integrating social media use with television-watching, albeit with some issues around content filtering) and Photo share (using the TV as a hub to share images between devices, scanning a QR code or using NFC to connect, with no app required).

As for the full range of 2014 Sony TVs, rather than rely on my notes being correct, why not get it straight from the horses’ mouth, as it were.

Getting connected

I also had some time to spend in Sony’s “network room” and whilst I have to say I was pretty impressed with the range of Vaio laptops in touch and non-touch forms (including the lightest ultrabook in Europe), all with NFC and some featuring ClearAudio+ (which really made a difference using the PC speakers), the potential sale of Sony’s PC business and my own professional IT links made these no more than a “ooo – that’s nice” view…

There were Xperia phones and tablets too but the real items I found of interest were the wearables – a Smartband that pairs with an Android phone for “life logging” and the SmartWatch 2 which acts as a remote screen for an Android phone, but also runs some of its own apps.  Wearables are big right now and I find this a particularly exciting market – it will be interesting to see how Sony’s devices take off…

 

Digital imaging (and a date for my diary)

A few years ago, Sony bought Konica-Minolta’s digital imaging business and they’ve clearly used it to good effect, expanding the Sony range to cover everything from digital compacts to high-end DSLRs (and of course expanding their own range of digital still and video cameras).  As a Nikon DSLR-shooter, I found the range confusing, with seemingly competing models using two different lens mount systems:

  • The A-mount is effectively the old Konica-Minolta system.
  • The E-mount is used by the modern, small form factor cameras.

Thankfully there are converters available, which means A-mount E-mount users can use adapters for Nikon and Canon lenses.  I guess I’m a bit of a Luddite too – I like a solid full-frame DSLR with high quality (often heavy) glass up front and am unconvinced by the new ranges of small cameras with interchangeable lenses (possibly because I got burned by Minolta in the late-1990s with an interchangeable lens APS film camera!).  Having said that, I increasingly find myself using the camera in my pocket (my phone) and it was interesting to see how Sony is enhancing the user experience with seamless integration between devices, including built-in NFC and Wi-Fi communications, together with iOS and Android PlayMemories apps for a range of photography uses).  I was also impressed to see that Sony is really moving ahead with behind full-frame cameras – be that the DSC-RX1, the prosumer ?7/7R or the ?99. Indeed I’d be happy to have an RX1 as my carry-everywhere camera (albeit a rather pricey one!)

On the video front, Sony has always been a leader and I was impressed to see both the NEX-VG900 full-frame interchangeable lens camcorder and the AX100 – a 4K Handycam targeted at home film-makers, using a 1″ sensor and class 10 SD card storage (that reminds me, I really should find a way to stream all of the raw footage off my collection of DV tapes onto a disk somewhere!).

Oh yes, and that date for my diary? Sony sponsors the World Photography Awards, and the 2014 exhibition will take place at Somerset House in London from 1-18 May.

Wall of sound

The last demonstration of the evening was focused on audio.  I didn’t check out the high resolution audio systems (although I heard others doing so, and they certainly sounded good) – I was interested in something portable – like the Bluetooth and NFC SRS-BTS50 or the higher-end SRS-X5 unit.  After all, when all you’re playing is compressed MP3 files, or music streamed from Spotify, it’s amazing how good it can sound on a small speaker setup. Then there were earphones, modern Walkman digital music players (I didn’t know that brand still existed but it seems you can get everything from a USB stick to an iPod competitor and even an MP3 Walkman built into a set of headphones!), clock radios, docking stations, DAB radios, all in one Hi-Fi systems – the works.

<tl;dr>

I’m pretty impressed with Sony’s consumer electronics plans for 2014.  Sure, what geek wouldn’t be interested in huge super-high definition TVs, some smart PCs and wearable tech, a selection of imaging devices that meet the needs of most, if not all, consumers and some seriously big sounds. But it’s more than that.  Maybe I drank the Sony Kool-Aid but I really did leave with the distinct impression that Sony is out to create a user experience that transcends devices and simply delivers the best picture and sound quality.  If I didn’t already have a Samsung Smart TV, Apple and Samsung phones, a Lenovo PC, Nikon cameras and an Xbox 360, I might well be persuaded to make my next consumer electronics purchase one from Sony…

[Update 19 Feb 2014: corrected statement re: adapters for third party lenses with Sony cameras]

%d bloggers like this: