What century are you in?
When you type in a two digit year as part of a date in a spreadsheet, by default, the spreadsheet will turn it into a four digit year. This is very helpful behaviour, until it isn’t. If you have dates that are very far in the past, or the future, the default behaviour may not […]
Finding the Ceiling
There are three CEILING functions in Excel. CEILING, CEILING.MATHS, and CEILING.PRECISE.(Although this one won’t show up if you start typing it in like the others will. You have to type the whole thing!) They are all similar but work differently in certain circumstances. CEILING and CEILING.PRECISE are older functions, so unless you need your spreadsheet […]
ISBLANK: When blank really does mean blank.
Sometimes you want to wait to work out something on your spreadsheet until you have the input. This is where ISBLANK is a useful function. The structure is simple: =ISBLANK(value) One input, which usually would be the reference to your input cell. As is often the case, things are not always clear cut and there […]
CELL function: More information than you ever thought you would need.
There is a function in Excel called CELL. It returns information about a cell, but it has some very strange characteristics and quirks. The inputs appear straight forward. =CELL(info_type, [reference]) The info_type will show up as a choice of words, some seem obvious as to what they will return, others are a bit more obscure. […]
RANK – Nothing to do with Sergeant Majors!
There are three (for now) RANK functions in Excel. Basically, you give them a number, and a list of numbers, and they will return where in the list the number falls. So, if there are 100 test scores, and you got 78, and you want to know how you went compared to all the other […]
Large – Finding first, second, or third, or any other placing.
There is a function in Excel called LARGE. It is a an odd name for what it does, but since I can’t think of anything better, maybe they couldn’t either! =LARGE(array, K) It requires two inputs. An array, normally a group of cells, and a number. What it returns is the “k-th largest value in […]
AVERAGEIF: more than an average function?
I have written about average, mean, median, mode, here. But you can also get the average (mean) of numbers based on conditions. So maybe you want the average sales from one region, or average cost of a particular product in the last year. This is where AVERAGEIF and AVERAGEIFS come in to play. AVERAGEIF works […]
Sumproduct: Some info about a useful function.
Sumproduct is a function in Excel that is a little tricky to get your head around, but can be really useful once you do. Let’s look at an example, that might help us to see where and why we might want to use this function. Look at the table below. We have the quantity sold […]
Reference a table
Referencing a table is a little different to referencing cells, so it is worth taking a look. First off, if you are unfamiliar with tables, then learn more about them here. When you write a formula inside a table, referencing other columns in the table, then Excel will use the column name, rather than the […]
A different way to reference a cell.
When you start using formulas in Excel you need to understand cell references. One thing that people find a bit confusing is absolute cell references. You can read about them here. If you want to avoid (at least for a little while) learning about absolute cell references, you can use a named range, which will […]