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