Articles

One of the major annoyances for me working with very large datasets you get from someone else is that you often don't know the format the file is coming in, not a problem really just take a peak in Notepad++ or Excel and you are away. But quite often these files are millions of rows in length and it is just not possible to work it out. So I've   more »

Well I suppose it was inevitable, when you suddenly realise you are getting older by the things you catch yourself doing/saying thinking getting more frequent. The latest thing I've noticed is that my propensity to fart in public has increased exponentially! It used to be the case that I'd internalise the pain of holding in a ripper for hours on   more »

This is one of those little quirks in excel that's annoyed me for a while, there must be some method in their madness but I've yet to come across anybody that thinks it is logically the way forward; so I've written one for you to use. The way the in-built function works is that it reports back the answer as how many months apart are the two dates   more »

If you've ever plugged a Raspberry Pi into a tv you've probably come across this issue - the displayed image either doesn't fill the screen or over-runs it. The usual bit of googling reveals that all you have to do is tweak a couple of overscan settings and you are good to go. It sounds easy but three Raspberry Pis in and I've only just found the   more »

My new adage of "You only know what you know how to google" - Martin Childs, 2016 stood true for this little puzzler, the answer wasn't apparent from simply googling it! All I wanted was to do something to a range input in a custom Excel function, easy you'd think - not in this case. I toiled for a bit of time before conceding that I'd just have   more »

First up I'm sorry about bathering on about this SSH thing but I to be honest I'm padding out the blog posts a little bit as there's one article I've written that I'm just not sure I want to publish, It's nothing bad, just not in keeping with the tone of this site so I've racked it up at the end of the post ready to go and keep adding posts in   more »

I came across my old shortcut on my windows machine to vnc onto the Pi which prompted me to have a go connecting to my new RaspberryPi3. I duly fired it up only to receive an error message along the lines of 'No configured security type is supported'. After a bit of research it turns out that RealVNC has upped their security protocols and TightVNC   more »

One of reasons why I originally held back on buying a SSL certificate for my website was that I wasn't sure that it'd work using a dynamic IP routing service - cos I didn't want to waste $15! Well you don't need to fret about it because I can confirm it works after I eventually got round to setting up a free LetsEncrypt.org one on my   more »

Here's a blast from excel past, it was actually one of the more interesting projects I worked on. We were designing food waste recycling plants for the food manufacture and retail industries. The task was to calculate the most efficient solution ofhow many recycling plants to build and where to build them based on the waste arising of each shop   more »

I love an analogy, there's nothing better than pushing your explanation across the line with an unequivocal corresponding example in everyday life. I think I've crossed the line this time though, I'd been asked to rate the presentation I'd just been given on a subject outside of my specific skill set; I responded that it was tricking given how   more »

For me being SSL secured has always been the benchmark as having a bonafide website. That little green padlock next to your websites URL doesn't really mean much for a blog, but I always wanted one. At the start of last year I started thing about trying to get one. Ever being a skinflint I wanted to pay nothing for it, but logic overcame   more »

My dream RaspberryPi project that I am slowly chipping away at is to create the ultimate alarm clock. It is a slow burner though as at it's inception I had none of the skills I needed to pull it off. I've now amassed all of the technical skills to pull it off and I'm just left with finalising the design and finding the time and the parts (not a   more »

So you've written a killer python script and you want to run it in the background as a service because when you close the terminal window/SSH session it kills it. I originally worked it out when I wrote my Twitterbot app, and was going to write it up seperately then but it took me so long to get it running in the background and I tried so may   more »

Things have moved on since the days of DSUM, we now have the likes of SUMIFS from Excel 2007 onwards. Can't even remember life before SUMIFS having never being a fan on DSUM. But with all things in life everything moves on, everything is never good enough and now SUMIFS's shortcomings are beginning to pop up. The other week the need came up for a   more »

One of the highlights of my summer was the adoption of a supplementary passtime, having recently picked up a copy of the National Trust's 50 things to do before you are 11 3/4 to inspire fun activities to be enjoyed with my niece and nephew I proceeded to introduce them to it. Unfortunately despite them falling very short of the cut-off turns out   more »

Had a right good chuckle to my self whilst on holiday in Cambodia, in the spirit of travelling light I'd left my electric toothbrush at home favoring the good ol' fashioned way of doing things. I've never been a fan of the manual way - always found it a bit too awkward and unusually for me being ambidextrous I suddenly realized that I'd never   more »

On yet another fun Saturday night out at National Cycling Centre to watch the Revolution Cycling Series I set off in search of a snack! I had often seen the ice cream stand at the Sapporo noodle bar, but had never tried the ice cream. I joined the queue to get myself an ice cream, deciding to get two scoops (despite declining the offer   more »

"You only know what you know how to google" - Martin Childs, 2016 This post was supposed to be about how create a custom Excel VBA function, the origination of the idea was despite my lifelong career of tinkering with excel I'd never come across or had the requirement to have a function cycle through the cell you input as a range to perform a   more »

There are some things that aren't easy to do in SQL as you'd like. I don't know why it has to make the seemingly simple things to complete in Excel so annoyingly tricky. Dynamic pivots is one of those things that is overcomplicated, sure there's plenty of tutorials on the t'internet , but what they all seem to lack is instructions of how to write   more »

Whilst we were out in Tanzania on Safari something wasn't quite adding up, every night at the hotels there was several Lamb dishes to be found on the menu. The thing was vexing me was that despite being in the country for nearly a week and spending at least eight hours a day driving through various types of countryside I'd yet to lay eyes on   more »

As much as I love Excel there are some circumstances where it just doesn't cut it - more than a millions rows and computer says no; some rediculouse vlookup and you could be there for hours. So step in SQL, it can do those aforementioned tasks in a matter of seconds. There are a couple of occasions though where SQL is not king, one of these is   more »

One of the common themes of the computer industry is to try to install a load of bloat on your computer under the guise of making it easier for you, well when I first got my Dell E310dw and had a flick through the setup guide to head off any such shinangans I noticed they had some sort of 'universal' installer. Trying not to fall for that I   more »

I'm always one for saving a bit of money and many years ago I came up with the plan that I could save a lot of money in printing costs if I didn't own a printer. It is a great idea, you'd be suprised at how much unnecessary printing you get up to and there's always the kick in the teeth of inkjet printer ink being more expensive than   more »

This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty. It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings. The   more »

Sorry to bore you with yet another Explode addin post, but I think this is worth it on a couple of fronts. Assingning a shortcut key to the explode plug-in makes more awesome - being able to devle into those dependants even quicker. Also seeing as as my initial explode post is one of my most read posts, I know you'll all lap it up. There are two   more »

Like with most things in life, the longer you do something for the more you get set in your ways. Excel is no exception and if I'm a sucker for anything it's number formatting. Maybe it's the accountant in me, but I think negative numbers are easier to read and look far more stylish wiht a bracket around them. Then if you are going to use a   more »

After many months struggling with my favorite excel explode addin, I've finally discovered the solution to my issues. The problems started after I arrived at a new job - the computers were super locked down and they had Office 2010. I had problems installing the addin and even when it was working there was no 'Explode..' option on the right-click   more »

Next in my series of helpful SQL code is duplicates, I seem to always be having issues with duplicates. This annoys me immensely as all of the issues comes from other peoples data I have to work with. No problem I hear you say, google is awash with ways of how to delete them. Trouble is these are all based on blindly deleting superfluous rows   more »