I have tutored students who take this class many times. It is a very confusing class and teaches old concepts rarely used in business anymore, as the internet has changed how we use Excel and other data management tools. Further, it does not teach definitions of terms so it becomes difficult to look for information online.
I wanted to give some definitions here that will help you at least search for help online:
- In Excel, things that start with = are called "formulas".
- A bunch of characters together are called a "string". Strings can have spaces and punctuation.
- If you cut off a piece of a string (which is done using the LEFT, RIGHT, and MID formulas), that is called "parsing a string".
- An "array" is a list of numbers or cells. Arrays have have a : in them, and mean STARTING CELL:ENDING CELL (like A1:A20).
- Each formula has a name, and starts with = and the name, then has a set of ()'s after it. For example, the IF formula goes like this =IF() - only the () always have something in between them.
- When you set up a formula and it's done calculating, you see how it "evaluated", meaning the answer it gave. Since you can also do math in Excel cells, you can do =1+2 and it will evaluate to 3.
- What is in between the ()'s are called "arguments". Each formula calls for a different number of arguments, and what is allowed in each argument is specific to the formula. For example, the IF formula calls for 3 arguments. The arguments are separated by commas, so it's =IF(something, something, something). In =IF, those somethings are specifically: =IF(logical test, value to evaluate to if logical test is true, value to evaluate to if logical test is false).
- The reason why each formula has a different number of arguments is because each formula does something different, and needs to know different things. For example, =SUM wants to add up a list of numbers, so it wants an array in its (), and that is just one argument. But LEFT is to parse off the left side of a string, so it needs 2 arguments: what position to start at, and how many to go over.
A few specific homework tips:
- For the parsing homework, use MID() as much as you can. It's hard to use LEFT() or RIGHT() except for the pieces of the strings that are on the leftmost side or the rightmost side.
- Also for parsing, you usually have a column where the data are not standardized. For example, a name column where some names have a middle initial and some don't. Just handle these differently with an =IF conditional. In the example, you could use a =FIND command to look for the period in the middle initial in a column. Then, in your =IF, handle the ones that have a period one way, and the ones that don't the other way.
- For the homeworks for calculating percentages based on lookup tables and total owed, first make separate columns for each part of the equations and refer to those in formulas. Then, copy the formula out of the separate columns and replace it in the real formula, and erase the separate column. That makes it easier to build long formulas.
- The professor always wants you to use =IFERROR. Do this after you have built your answers - simply slip an IFERROR statement first, then nest the formula already in the cell in the IFERROR argument for "what to return if no error".
- For calculating a straight percentage, think in terms of figuring out the numerator, then figuring out the denominator. For the numerator, you can use COUNT if there are numbers, COUNTBLANK if you are trying to count blanks, or COUNTA if you are trying to count a column with characters (COUNT will return 0 for this). Do whatever that is, and then use COUNT or COUNTA (depending on if your data are numbers or characters) for the denominator. For example, if I wanted to count the blanks in an e-mail address column, it would look something like =(COUNTBLANK(A2:A10)/COUNTA(A2:A10))
Still need help? Contact me and we'll set up a tutoring session!