Another article on Excel idioms; this one’s about dates. It’ll be useful for some. Continue reading “Datediff in Excel”
Datediff in Excel
Tag: excel
Encrypting Excel
How to protect an Excel File. File » Info » Protect » Encrypt with Password. Continue reading “Encrypting Excel”
On D’Hondt
For the European Parliament election in Great Britain and Greater London Authority City wide list, a form of PR is used. Parties register lists of candidates, and voters vote for their preferred party. The counting is done using a method called the Dhondt method. This note describes the counting method and shows how an excel spreadsheet can produce the results. Continue reading “On D’Hondt”
How to unpivot in Excel
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”
Analysing the 2015 General Election
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”
OMG Excel Arrays (a UNIQ filter)
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)”
Date Differences in Excel
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”
VLOOKUP vs INDEX ( MATCH)
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)”
Microsoft Office Templates
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!