Category Archives: Formulae explained

INDIRECT

Oh what a gem of a formula this is. In essence, it allows you to specify the text that you would like to be used to bring back information. So =INDIRECT("A1") will bring back the contents of cell A1. And … Continue reading →

VLOOKUP and HLOOKUP

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data. But we shouldn't forget about its lesser-used sibling, HLOOKUP. First, VLOOKUP. … Continue reading →

COUNTIFS and SUMIFS

Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that. Since Excel 2007, there have been a … Continue reading →

CLEAN

Summary The CLEAN function in Excel is a little-known function. As its name suggests, it cleans up text. Or more specifically, it removes all non-printable characters from text. So wherever you see one of those ugly characters that appear as … Continue reading →

RANK

Summary If you have a range of values, the RANK function tells you where in that range a specific number sits. It can either tell you it's the nth biggest number in the range. Or it can tell you it's … Continue reading →

TRIM

Summary This is an oft forgotten function. Quite simply, it gets rid of unwanted spaces. It does so in the following ways. All leading spaces that appear at the start of the cell being trimmed are removed All trailing spaces … Continue reading →

WEEKDAY

Summary Weekday is a very useful little function. Quite simply, it works out which day of the week a date represents. Given that dates are simply formatted numbers, it can be used against any cell that contains a number. But … Continue reading →

MIN

Summary MIN brings back the minimum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values As with SUM, it only applies to cells that are formatted as numeric values … Continue reading →

MAX

Summary MAX brings back the maximum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values As with SUM, it only applies to cells that are formatted as numeric values … Continue reading →

COUNT

Summary COUNT is not as straightforward as it might look. Many people think it counts the number of cells in a range, or the number of populated cells. But it doesn't. Instead, it counts the number of cells in a … Continue reading →