Friday, 30 May 2014

IF(O2="12x7x4", "12x7x8", "oops")

After a brief discussion with my Boss yesterday there was a possibility that I might need some help today …

… so first thing this morning I dragged the Bullet from the garage and headed in to the Depot. On my way in to work I stopped off for fuel and as I wandered back to the Enfield I was accosted by an elderly gentleman with a strong local accent. "I used to have a real one of those" was his opening gambit and he then went on to regale me with tales of classic motorcycles and the speed of which his Royal Enfield was capable. To be fair he was nice old chap and his dismissive initial comment was softened when he cast his eye over her and said that she looked really good …

… and I had the final word when I left the forecourt. I simply swung my leg over and hit the starter button and she fired up instantly. There was no struggle to kick over a 500cc single … and there were no drips of oil on the ground either.

Once at work I settled myself in and eventually addressed the problem I was facing. I had 1200 lines of data and these entries contained one of eleven possible values. I needed to convert the data cells to a new set of values. At this point it is probably worth mentioning that I am more of a words and actions type of person than the sort that revels in complex spreadsheet formulas. I have heard of vlookups and I know what they do … but I'm fucked if I know how to create one!

Anyhow, back to the problem in hand. I was aware that I could use nested IF statements to perform a fast and accurate conversion and the natural estimator in me knew that an hour spent fathoming this would be time well spent. I wasn't overly confident of flying solo but settled to the task in hand and the first attempt simply returned "Formula Error" messages. This was bloody frustrating as if I used a much shorter set of IF statements it seemed to work just fine. In the end I had to build up my set an element at a time and within a few minutes I was done. The formula was copied an pasted in to 1200 cells and the data was converted.

I was quite chuffed with my small success, especially as I had not needed to consult an MS Excel Guru who inhabits a cubicle a little way down the office. I had also got this out of the way by lunch time and could return home and spend the afternoon working from home. However, before I left I took the time to remove the power cable from my secondary screen and lock it in my desk … It appears that the Cuckoo just hasn't got the message!

The afternoon waned and 30% arrived home a little after five o'clock. We took a ride out to the Sale Room in Littleton, met up with Moneypenny and took a wander around the lots. It looks like tomorrow's auction won't be the most exciting sale we have ever attended but there are a couple of lots that might be worth watching.

Unfortunately the 15th Century Fireback is a) a replica and b) too wide.

No comments:

Post a Comment