Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

EXCEL FORMULAS:
10 SECRET DEFINITELY HELPED IN
YOUR JOB

1
...
It allows you to
add 2 or more numbers together
...

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
...
COUNT

Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that have
numbers in them
...


3
...
It will count cells that
non-empty
have numbers and/or any other characters in them
...


It counts the number of non empty cells no matter the data type
...
LEN

Formula: =LEN(A1)
The LEN formula counts the number of characters in a cell
...


Notice the difference in the formula results: 10 characters without spaces
in between the words, 12 with spaces between the words
...
TRIM

Formula: =TRIM(A1)
Gets rid of any space in a cell, except for single spaces between words
...
This can wreak
front
havoc if you are trying to compare using IF statements or VLOOKUP’s
...
The TRIM formula
removes that extra space
...

6
...

(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
...
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 had it look in cell A1 and
grab only the 1st character from the left
...
I had it look in cell A1,
start at character 3, and grab 5 characters after that
...
I had it look at cell A1 and
grab the first 6 characters from the right
...
VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num,
range_lookup)
By far my most used formula
...
(See the full
explanation of VLOOKUP) Basically, you define a value (the
lookup_value) for the formula to look for
...


Note: If at all possible use a number for the lookup_value
...

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”
...
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)
...
The “range_lookup” is a TRUE or FALSE value
...
If you put FALSE it will only give
you an exact match
...

Example:
You have 2 lists: 1 with a sales person’s ID and the sales revenue for the
quarter
...

You want to match up the sales person’s name to the sales person’s
revenue numbers for the quarter
...

The first list goes from A1 to B13
...

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
...
)
$A$1:$B$13 = the “table_array”
...

I went to F because if it finds match in column E, I want it to return what’s
in column F
...
This is called
an absolute reference
...
This tells the formula the number of columns away
from the left most column to return in case of match
...
It starts at 1, not 0)
...

I would then copy and paste that formula along all the cells in column C
next to the first list
...


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
...
Check out
some other examples: Vlookup Example, Microsoft’s Official
Example
...
FREE preview**
8
...

Continuing with the sales example: Let’s say a salesperson has a quota to
meet
...
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)
...
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
...

It would change for each sales person
...

9
...
There are also the formulas: SUMIFS,
COUNTIFS, AVERAGEIFS where they will do their respective functions
based on multiple criteria you give the formula
...
CONCATENATE

A fancy word for combining data in 2 (or more) different cells into one cell
...
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”
...
I can use =concatenate(A1, “ “, B1)
and it will give me the same thing: “Steve Quatrani”

Finding The Right Excel Formulas For The Job
There are 316 built in functions in Excel
...
Luckily Excel has a
built in wizard that helps you find the correct formula for what you’re
looking to do (if there is one)
...

More Excel Formulas

There is so much more that I use on a regular basis such as Time formulas
(NOW, TODAY, MONTH, YEAR, DAY, etc
...

The real power comes in combining these functions into complicated excel
formulas

---