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, […]
Subtotal function, more than meets the eye.
If you have ever added a total to a table you might have noticed that it doesn’t use a SUM function, but a strange thing called SUBTOTAL (And if you never have added a total to a table, then you really should! You can read about the greatness of tables here). So, why does the […]
Working days made easy.
You can work out the date so many days away in Excel, by just doing one date plus the number of days. This works because Excel treats dates as numbers (as long as you have actual dates, and not just text that looks like a date). However, if you are trying to calculate with working […]
DAY, MONTH, and YEAR functions in Excel.
There are formulas in Excel to take a date, and return the day of the month, the month of the year, or the year itself. Of course, you can also format a date, so it stays as a date, but only displays the day, month, or year, but sometimes you need just the number and […]
Trim, clean, and proper.
Cleaning your data is often an important pre step to actually using it to make decisions. Three useful functions in Excel to help do that are TRIM, CLEAN, and PROPER. TRIM(text) The TRIM function will remove all spaces from text except for single spaces between words. You would be surprised how often people put extra […]