Functions 9: VLOOKUP Time to learn: 25 minutes You will find this perhaps the most powerful function within the Excel framework. Why? Because one of the most tedious tasks in data management is keeping track of it all. During the age of the dinosaurs, if you had 10 items to keep track of, that would be no problem, but due to the scale at which the world is now used to, a relatively small piece of data would be thousands, if not hundreds of thousands of items long. VLOOKUP effectively and efficiently cross-checks and compares lists in seconds. So, let’s get our hands around this and choke it. VLOOKUP The VLOOKUP function is essentially a function that COMPARES LISTS. So, it would look at one list of items and tell you if another list you have contains the same items. If you had a list of all the items in your store and a client asked you if you could supply
Functions 8: SUMIF, COUNTIF & AVERAGEIF Time to learn: 30 minutes Do you need to get the breakdown of sums, averages or a count of your data? Need to get them by SEPARATE CATEGORIES? Needed it yesterday? This series of formulas has so many uses, it’s amazing. You’ll be able to get the sums, counts and averages of items by specifying criteria of your design! Let’s start with some data. Here we have a list of students, with the following columns – Name, Age, School, Grade and Fee. Now, let’s see what we can do. SUMIF/SUMIFS This function adds values together if they fulfil certain conditions that YOU specify! SUMIF will only add values based on 1 specified condition SUMIFS will add values based on more than 1 specified condition Here’s an example. Let’s try to find out how much in fees students from school X pay: =SUMIF(C:C,”X”,E:E) Like all formulas, begin with ‘=’ and then the function SUMIF, with the
Functions 10: Wildcard Time to learn: 10 minutes Ever wanted to find out something about your products just from their names? Tired of counting stuff BY HAND? God forbid. Ready to save yourself hundreds of hours doing manual work that can be better spent conceptualising and innovating? Let’s get it on! Let’s start with some data. This is a list of 5 items in a gun catalogue. We want to find out how many guns are “Automatic”, how many are “Rifles” and how many of them can be characterised under the “Machine” category. Step 1: Let’s create a title for ‘Names Beginning with “Automatic”. Step 2: Now, by using a COUNTIF and a wildcard “*”, we will find out how many names begin with “Automatic”, with the following function: =COUNTIF(A2:A6,”automatic*”) Neat? That’s right. Step 3: Now, by using the COUNTIF and a wildcard “*”, we will find out the number of names that end with “Rifle”, with the following function:
Function: IF, AND & OR Want to know how to make your spreadsheet look like it’s a thinking being? Looking for a way to make your data talk back to you? The most interesting aspect about Excel is the ability to execute logical equations or FUNCTIONS. IF One of my favourite functions is the IF function. Remember that this is a formula and as such, whenever you type it into a cell, you have to begin with a ‘=’ sign. The structure of the IF function has 3 parts and looks like this: =if(1. a logical or mathematical expression; usually referring to another cell, like if cell A1 is equal to 3 or if cell A1 is equal to cell B1, 2.then what you would like this cell to say or do if the expression indicated in 1. is true, 3. Lastly, what you would like this cell to say or do if the expression in 1. is false) Here’s
Hi everyone, I see there has been a lot of attention on pivot tables. Well, the page is ready! Have a look and have fun! Josh.