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 […]
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 […]
Some more info on sums with SUMIFS.
Most people use a SUM function in their spreadsheets. But if you want to add up only some parts of your data, then SUMIFS is a handy function to know. =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) SUMIFS takes in at least 3 inputs. Sum_range is the number that you want to add up. criteria_range1 is […]
Create a series, with SEQUENCE.
The SEQUENCE function in Excel is useful when you need a pattern. So, it might be as simple as a list of numbers, starting from one, and going up by one. Or, codes for products, that start from 1000, and go up in hundreds. Or, create a list of dates. =SEQUENCE(rows,[columns],[start],[step]) SEQUENCE has one compulsory […]
Round and round, all about rounding in spreadsheets.
There are a few ways to dealing with rounding in Excel. ROUND function ROUND(number, num_digits) requires two inputs. The number is be rounded (normally a reference to a cell with a number in it, or a calculation) and the number of digits to round to. This can be entered as a positve number (will round […]