Excel Formulas You Should Definitely Know:
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
The SUM formula does exactly what you
would expect. It allows you to add 2 or more numbers together. You can use cell
references as well in this formula.
The above shows you different examples.
You can have numbers in there separated by commas and it will add them together
for you, you can have cell references and as long as there are numbers in those
cells it will add them together for you, or you can have a range of cells with
a colon in between the 2 cells, and it will add the numbers in all the cells in
the range.
Formula: =COUNT(A1:A10)
The count formula counts the number of
cells in a range that have numbers in them.
Formula: =COUNTA(A1:A10)
Counts the number of non-empty cells in
a range. It will count cells that have numbers and/or any other characters in
them.
The COUNTA Formula works with all data
types.
It counts the number of non-empty cells
no matter the data type.
4. LEN
Formula: =LEN(A1)
The LEN formula counts the number of
characters in a cell. Be careful though! This includes spaces.
Notice the difference in the formula
results: 10 characters without spaces in between the words, 12 with spaces
between the words.
5. TRIM
Formula: =TRIM(A1)
Gets rid of any space in a cell, except
for single spaces between words. I’ve found this formula to be extremely useful
because I’ve often run into situations where you pull data from a database and
for some reason extra spaces are put in behind or in front of legitimate data.
This can wreak havoc if you are trying to compare using IF statements or
VLOOKUP’s.
I added in an extra space behind “I
Love Excel”. The TRIM formula removes that extra space. Check out the character
count difference with and without the TRIM formula.
6. RIGHT, LEFT, MID
Formulas: = RIGHT(text, number of characters), =LEFT(text,
number of characters), =MID(text, start number,
number of characters).
(Note: In all of these formulas,
wherever it says “text” you can use a cell reference as well)
These formulas return the specified
number of characters from a text string. RIGHT gives you the number
of characters from the right of the text string, LEFT gives you the number of
characters from the left, and MID gives you the specified number of characters
from the middle of the word. You tell the MID formula where to start with the
start_number and then it grabs the specified number of characters to the right
of the start_number.
I used the LEFT formula to get the
first word. I had it look in cell A1 and grab only the
1st character from the left. This gave us the word “I” from “I love Excel”
I used the MID formula to get the
middle word. I had it look in cell A1,
start at character 3, and grab 5 characters after that. This gives us just the
word “love” from “I love Excel”
I used the RIGHT formula to get the
last word. I had it look at cell A1 and grab the first
6 characters from the right. This gives us “Excel” from “I love Excel”
7. VLOOKUP
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
By far my most used formula. The
official description of what it does: “Looks for a value in the leftmost column
of a table, and then returns a value in the same row from a column you
specify…”. (See the full
explanation of VLOOKUP) Basically, you define a value
(the lookup_value) for the formula to look for. It looks for this value in the leftmost
column of a table (the table_array).
Note: If at all possible use a number
for the lookup_value. This makes it a lot easier to make sure the data you are
getting back is a correct match.
If it finds a match of the
“lookup_value” in the left column of the “table_array” it will return the value
in the column you specify using the “index_num”. The “index_num” is relative to
the left most column. So, if you have the table_index look
in column A and you want what is returned to be what’s in column B the
“index_num” would be 2 because the leftmost column, column A in this case, is
the 1st column in the table array and column B is the 2nd column (hence the 2
for the index number). If you want what is in column C to be returned you’d put
3 for the index_num. The “range_lookup” is a TRUE or FALSE value. If you put
TRUE it will give you the closest match. If you put FALSE it will only give you
an exact match. I only use FALSE when using the VLOOKUP formula.
Example:
You have 2 lists: 1 with a sales
person’s ID and the sales revenue for the quarter. Another with the sales
person’s ID and the sales person’s name. You want to match up the sales
person’s name to the sales person’s revenue numbers for the quarter. They are
all jumbled around so to manually match this, even for a small number of
salesmen would leave room for a high margin of error and take a lot of time.
The first list goes from A1 to B13. The
2nd list goes from D1 to E25.
In cell C1 I would put the formula
=VLOOKUP(B18, $A$1:$B$13, 2, FALSE)
B18 = the lookup_value (the sales
person’s ID. This is a number that appears on both lists.)
$A$1:$B$13 = the
“table_array”. This is the area I want the formula to search the leftmost
column (column E in this case) for the “lookup_value”. I went to F because if
it finds match in column E, I want it to return what’s in
column F. (The money signs are there so that the table_array will stay the same
no matter where the formula is moved or copied to. This is called an absolute
reference.)
2 = the index_num. This tells the
formula the number of columns away from the left most column to
return in case of match. So, if you find a match between
the lookup_value and the leftmost column of the table array, return what’s in
the same row in the 2nd column of the table (the 1st column is always the
leftmost column. It starts at 1, not 0).
FALSE= tells the formula I want it to
only return the value if it’s an exact match.
I would then copy and paste that
formula along all the cells in column C next to the first list. This would give
me a perfectly aligned list with the sales person’s ID, sales person’s revenue
for the quarter, and the sales person’s name.
In order to get a nice neat list of
Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each
other I used the VLOOKUP formula to compare 1 list to another.
This is a complicated formula, but an
extremely useful one. Check out some other examples: Vlookup Example, Microsoft’s
Official Example.
8. IF Statements
Formula: =IF(logical_statement,
return this if logical statement is true, return this if
logical statement is false)
When you’re doing an analysis of a lot
of data in Excel there are a lot of scenarios you could be trying to discover
and the data has to react differently based on a different situation.
Continuing with the sales example:
Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the
revenue next to the name. Now you can use an IF statement that says: “IF the
salesperson met their quota, say “Met quota”, if not say “Did not meet quota”
(Tip: saying it in a statement like this can make it a lot easier to create the
formula, especially when you get to more complicated things like Nested IF
Statements in Excel).
It would look like this:
In the example with the VLOOKUP we had
the revenue in column B and the person’s name in column C (brought in with the
VLOOKUP). We could put their quota in column D and then we’d put the following
formula in cell E1:
=IF(C3>D3, “Met Quota”,
“Did Not Meet Quota”)
This IF statement will tell us if the
first salesperson met their quota or not. We would then copy and paste this
formula along all the entries in the list. It would change for each sales
person.
Having the result right there from the
IF statement is a lot easier than manually figuring this out.
9. SUMIF, COUNTIF, AVERAGEIF
Formulas: =SUMIF(range,
criteria, sum_range), =COUNTIF(range, criteria),
=AVERAGEIF(range, criteria, average_range)
These formulas all do their respective
functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the
formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective
functions based on multiple criteria you give the formula.
I use these formulas in our example to see the average revenue (AVERAGEIF) if a
person met their quota, Total revenue (SUMIF) for the just the sales people who
met their quota, and the count of sales people who met their quota (COUNTIF)
10. CONCATENATE
A fancy word for combining data in 2
(or more) different cells into one cell. This can be done with the Concatenate
excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in
cell A1 and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1
and it would give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining with the
&). I can use =concatenate(A1, “ “, B1) and it
will give me the same thing: “Steve Quatrani”
Lean Basic Formulas
No comments:
Post a Comment