I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.
The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.
They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.
This blog post uncovers 4 Secret PowerPoint Tricks that will help you to deliver effortless and high quality presentations. These 4 tricks are some of my personal favourites (PowerPoint has many useful tools).
These 4 tricks are easy to use, but will make Massive differences to the way you develop and deliver PowerPoint presentations.
This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.
This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.
This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.
The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.
This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.
If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.
We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.
Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done. Continue reading →
If you find yourself creating projects on a regular basis, you should consider creating your own project template, or customising the default MS Project template. It is easy to do and will save you loads of time in the long run.
This tutorial will begin by showing you what a template consists of, and how to save one. This approach is best if you require multiple templates. For example, specific templates for specific clients or specific templates for specific types of job.
This tutorial will then show you how you can customise the default MS Project template. This will be the best option if you just need one template for all your projects. Continue reading →