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 […]
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 […]
Transpose – switch it two ways.
The transpose function rotates an array of data. So, changes from data going across a row to down a column and vice versa. There is also a transpose under paste special, which does the same thing. In this example, we can see the formula is referencing row B. From B2 to B7. The result of […]
Being choosy with the CHOOSE function
The CHOOSE function in Excel allows you to bring back a range of cells, based on a single input. =CHOOSE(index_num, value1, [value2], …) The minimum is two inputs, the index number and a value. It wouldn’t be a very interesting function if that is all it did! The power comes when you add other values, […]