Monday 27 November 2017

Two useful little functions for tidying data

By .

Here are two useful functions plus a light-hearted one which my wife discovered...

The first is great for anyone dealing with manually-entered data which might be a little untidy... The TRIM function will remove any preceding or training spaces. You could create a new column after a column of data (for example, names or addresses) and enter the following formula: =TRIM(cell ref). For example if A1 contains " Hello ", and you enter =TRIM(A1), the result will be "Hello". This is an important little step towards cleaner data.

You're probably familiar with using MAX and MIN to display the largest and smallest values in a list. But what if you want the third smallest? We can use SMALL to do this. Imagine D1:D5 contains 8, 5, 6, 1, 3 and you want to know the third smallest number. Use =SMALL(D1:D5,3) to show this.

Why not have a play with LARGE? It works in exactly the same way.

And a fun one to close: try entering =ROMAN(2018) into Excel and see what you get...