Excel Important Formula which is asked by interviewer.
Excel Important Formula
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…”. (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)
Comments
Post a Comment