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 […]
Referencing cells, absolutely! What is an absolute reference.
If you have used spreadsheets made by other people you may have noticed that sometimes there are dollar signs in the formulas. This may seem strange, but there is a good reason! And that reason is absolute reference. But what is that?! For example, a formula such as =$A$1 * A2 What do the dollars […]
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 […]
Some info about sums.
Sum is probably one of the first functions most people learn in Excel. Sum is just a fancy word for add up. What the sum function allows you to do is avoid a formula such as =A1+A2+A3+A4+A5+A6+A7. The sum function takes a start cell, and an end cell and adds up all the cells in […]