ZAHID'S ICT CONTENT
Friday, October 17, 2014
Wednesday, October 15, 2014
Badly Needed Excel Formulas on Job environment
10 Excel Formulas Formulas: That usually Helps for Job Environment
April 14, 2014 Zahidul Islam
Don’t waste any more hours in Microsoft Excel doing things
manually. There are many ways to use Excel formulas to decrease the amount of
time you spend in Excel and increase the accuracy of your data and your
reports.
Excel Formulas You Should Definitely
Know:
1. SUM
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.
2. COUNT
Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that
have numbers in them.
This formula only works with numbers though:
It only counts the cells where there are numbers.
3. COUNTA
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…”. 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:
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”
Finding The Right Excel Formulas For The Job
There are 316 built in functions in Excel. You’re not going
to sit there and memorize what all of them do (or at least I hope not!).
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).
Click the “fx” next to the formula bar in Excel
This brings up a menu and in there you can type in a
description of what you are trying to do and it will bring up the correct excel
formula:
I typed in “remove extra spaces” and it returned the TRIM
formula that we went over earlier.
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.), other formulas like AND and
OR, along with many others.
The real power comes in combining these functions into
complicated excel formulas.
Breaking Down Complicated Excel
Formulas
=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “,
“”),””)), “”)
Do you see formulas like the one
above and run away screaming AHHHHHHHHHHHHHHHHHHHHHHHHH!!!
I don’t blame ya!
This can be a really intimidating formula even for the most
seasoned Excel user.
So What’s Going on With This Excel
Formula?
Let’s break down all the Excel functions in it:
- IFERROR
- TRIM
- IF
- LEN
- VLOOKUP
- SUBSTITUTE
I’m not going to go in depth into each formula, but you’ll
get the point.
Let’s start with the innermost formula. This is the end
result that you are trying to accomplish.
We want to take a phrase (that we get with a VLOOKUP) that
has a space in it and SUBSTITUTE that space with nothing. So it will take a
phrase like “Excel Formula” and make it “ExcelFormula”.
SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “,
“”)
We use the VLOOKUP formula to get the actual phrase we want
from a different sheet in the Excel Workbook if it matches what’s in cell F7.
VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)
We use an IF statement to say IF the number of characters
(LEN) of what’s returned from the VLOOKUP is above 0, then run the VLOOKUP,
otherwise, put nothing here. This way if the VLOOKUP returns nothing, then
nothing will happen and this will prevent most errors.
IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “,
“”),””)
We then wrap it with the TRIM function which gets rid of all
extra spaces, besides 1 space in between words. Sometimes when you’re getting
data from a database extra spaces can be added after a word. This can make it
really hard to compare data from different sources, so the TRIM function comes
in handy a lot!
TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “,
“”),””)
Finally we wrap it in the IFERROR formula. This formula will
be triggered if the other formulas wind up giving you an error. It could look
something like #N/A and that’s pretty ugly. This can happen for legitimate
reasons, but you don’t want to hand your boss a sheet full of #N/A symbols.
What happens is IF the combined formulas return any excel
error, it will make the cell blank, otherwise it will show the results of the
formula.
=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “,
“”),””)), “”)
There you have it, this long complicated formula broken down
into simple step by step functions.
You can use the same method for building up a long formula
Our goal is to SUBSTITUTE the spaces for nothing so we start
with that. We then have to pull it in from another sheet so we use VLOOKUP for
that, etc., etc.
Subscribe to:
Posts (Atom)