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 […]
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 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 […]
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 […]
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 […]
Get text from text – using Excel text functions.
Excel has a lot of functions that can deal with text. The first three I will introduce you to are Left, mid, and right. These are functions in Excel for pulling bits of texts out of other texts. For example, if you had an address that has the post code on the end, after a […]
Search and Find – same same, but different.
Search and Find are two similar functions in Excel. Their syntax is the same (find_text,within_text,[start_num]) Basically, what do you want to find, what text are you looking in, what number character to start at. The first two inputs are required, the third is optional. If you don’t specify then it will start looking at the […]
Joining together with CONCAT or TEXTJOIN.
Joining cells into one can be done in a couple of ways in Excel. The CONCAT function in Excel joins things together. CONCAT is short for concatenate. There is actually a concatenate function as well, and this does exactly the same thing, but it is an older function, and if you have a newish version […]