The need to unpivot data is a common requirement. There are a number of reasons as to why. I and my customers are primarily using Excel on Windows and so the examples below are shown in excel and rely on excel functionality. Continue reading “How to unpivot in Excel”
I am fed up hearing about the PLP’s 9m votes mandate. A large number of these 9m votes will have voted for Labour candidates that lost. So I decided to calculate the number of votes cast for the PLP. So something for both political geeks and excel nerds. Here’s how I did it. Continue reading “Analysing the 2015 General Election”
Right. I needed to write a UNIQ filter in an excel spreadsheet and this needed to be implemented using functions i.e. not VB for two reasons; I can’t and the customer doesn’t want me to. Continue reading “OMG Excel Arrays (a UNIQ filter)”
I was looking to calculate if someone was under 27 years old; this can Be quite tricky but excel has just the function, =DATEDIF(). Continue reading “Date Differences in Excel”
How awesome is this! Excel =INDEX, is a matrix retrieve i.e. =INDEX(array, row, col) retrieves an entry from the two dimensional array. If we combine this with MATCH which returns a row number we can use these functions instead of =VLOOKUP, its allegedly faster too. MATCH operates on a column or linguistically a list. Continue reading “VLOOKUP vs INDEX ( MATCH)”
With Office 2010, the location of the “My Templates” folder for Excel is managed using Word’s Options dialogue box, and it seems that you can no longer specify a search path, solely one folder.
What Microsoft say at answers.microsoft.com!