Sunday, March 16, 2014

Important Excel Functions

  • Parameters in [..] are optional

SUMIF

Sums the values in a range that meet criteria that you specify.

=SUMIF(range,criteria,[sum_range])

Range is the range of cells that you want evaluated by criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".

Sum_range are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

COUNTIF

Counts the number of cells within a range that meet the given criteria.

=COUNTIF(range,criteria)

VLOOKUP

VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example you can ask What is the name of the customer with ID=C00023 or How much is the product price for product code=p0089 and VLOOKUP would give you the answers.

=VLOOKUP(what you want to lookup, table, column from which you want the output, is your table sorted?)

Example:

=VLOOKUP(“C00023″, customers table e.g. A2:C7, 2, false)

Looks up customer ID C00023 in the first column of customers table and returns the corresponding value from 2nd column. Assume that customers table is not sorted.

Annoying Limitation:

VLOOKUP formula can only search on left most column.

INDEX

Returns the value that is corresponding to the given row number in a defined column (range).

=INDEX(lookup column, row number)

MATCH

Returns the position number of the lookup element within the lookup column.

=MATCH(what you want to lookup, lookup column, match type)

INDEX + MATCH

VLOOKUP formula can only search on left most column. That means, if a table of customers has customer ID in left column and name in right column, when using VLOOKUP, you can search for customer ID only. You cannot ask questions like what is the customer ID of “Samuel Jackson” ?

Thankfully, INDEX+MATCH formulas come to rescue. These 2 beautiful formulas help us lookup on any column and return corresponding value from any other column.

=INDEX(lookup column, MATCH(what you want to lookup, lookup column, match type))

Example:

=INDEX(customer IDs, MATCH(“Samuel Jackson”, Customer names, 0))

Think like this:

=INDEX(column I want a return value from, MATCH(My Lookup Value, Column I want to Lookup against, 0))

IF

=IF(condition to test, output for TRUE, output for FALSE)

CHARTS

Not hard. Extremely important in a business environment.

MACROS

This is advanced.

IFERROR

Checks a formula (or expression) and returns the value of formula if there is no error, otherwise a custom formula.

=IFERROR(error-prone-formula,"Problem Message")

Example:

=IFERROR(1/0,"Try splitting an atom instead!")

will give the message "Try splitting an atom instead!" because the expression 1/0 returns an error.

Whereas,

=IFERROR(0/1,"Try splitting an atom instead!")

will give the value 0 since 0 divided by 1 is 0.

Common Use:

While writing lookup formulas like VLOOKUP, INDEX+MATCH it is common to search for values that do not exist in your data. You can wrap such formulas in IFERROR for peace of mind.


e.g. =IFERROR(VLOOKUP(...),"Not found")

PIVOT TABLE

They can be used to summarize, analyze, explore and present your data.

You can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.