RASCI Template
September 21, 2021
RASCI Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp The RASCI matrix is a visual business tool used to define the connection between members and their
Top 10 Most Useful Set of Excel Functions
Brief
- A look at 10 of the most useful sets of excel functions ranging from index-match to IFS.
- Readily available case studies to learn and practice.
- Downloadable excel case study file to support education and knowledge retention.
Excel has been around for a while, in fact its first release came to the public in 1985. Since then, it has seen a continuous rise in popularity and today, the software benefits from 750 million users around the world. Many other products have come and gone, but Excel has managed to remain a centerpiece of the technology stack used at organizations of all sizes due to its flexibility, accessibility and ease-of-use. The tool has proved to withstand the test of time and, over the years, new functionalities have been added that allow individuals and organizations to gather better insights and even partially automate processes. To ensure that you are ready to use excel to support your organization, our team has set-up a list of the 10 most useful set of excel functions.
Throughout every single excel function showcased, a case study will be offered to help you get a feel for how the function works. For those interested in having the Case Study file, please download it here.
1) INDEX-MATCH functions
What is the index function? It returns a value or reference from a given table, range or array.
What is the match function? The match function searches for an item that has been specified by the user and provides the position that the result is situated at.
When combining both functions, it allows users to find an item and return a reference that is associated with that item. It serves as a powerful tool for many businesses and analysts.
- Formula: =INDEX(The column or area where the result is expected to be, MATCH(What you want to lookup, Where you are looking for, How similar is the lookup to the data you are looking for [0=identical] & [-1 or 1 = approximate]), discretionary1, discretionary2).
Discretionary1 = [column_num]
Discretionary2 = [area_num] - Case Study: A company has a pricing database that lists all of its products. The manager requests that you confirm the price of Zippers from the database.
Answer: By using INDEX to define the positioning of the returning variable ‘Price’ and MATCH to connect the searched item to the database, we are quickly able to extract the requested information.
2) VLOOKUP & XLOOKUP functions
What is VLOOKUP? VLOOKUP or Vertical lookup searches for a requested item in a column and outputs the results tied to that cell. It is considered to be the slower and less efficient option in comparison to INDEX-MATCH.
What is XLOOKUP? XLOOKUP is the upcoming solution (July 2020 estimated release) available only on Microsoft 365 and onwards. It aims to upend both VLOOKUP and INDEX-MATCH by doing both functionalities.
- Formula: =VLOOKUP(What you want to lookup, Where you are looking for, The column number where the result is expected to be, Approximate match [0=identical] & [-1 or 1 = approximate])
- Case Study: A company has two databases, consumer purchases and product information. The first contains data on consumer’s name, their country and product sold. The second database contains merchandise related information such as the Product Type and Price. The request of your manager is to reconcile the price in database 2 into database 1.
Answer: By setting up a new column in database 1 and building a VLOOKUP formula that looks up the product type in the same row to the database 2, it is possible to quickly determine purchase price for all items in database 1.
Note: To ensure that the search in database 2 goes smoothly, it is important to use the $ sign for absolute referencing.
3) SUM & SUMIFS functions
What is SUM? The SUM function adds up all the numerical cells that are defined in the formula.
What is SUMIF? SUMIF only summarizes a set of values defined in the formula when the values meet a certain criteria or condition specified by the user.
- Formulas: =SUM(number1, number2,…)
=SUMIF(sum_range, criteria_range1,criteria1, criteria_range2,criteria2,…) - Case Study: Using the database in the previous example and assuming the purchase price was the amount spent. A manager requests that you check to see how much money was made selling Zippers in Mexico.
Answer: The formula is defining that all cells under purchase price (E4:E103) should be summed up if their respective product type (D4:D103) matches Zipper ($N$4) AND their respective countries (C4:C103) matches Mexico ($N$3).
4) AVERAGE, MEDIAN & STDEV.S functions
What is Excel Average? The AVERAGE function returns the arithmetic mean in a defined dataset. It works by adding together all the numbers and dividing by the quantity of numbers added. For those interested in creating averages with criteria, there is a function called AVERAGEIFS that works in a similar way to SUMIFS.
What is Excel MEDIUM function? It provides the middle number out of the defined dataset.
What is Excel STDEV.S? It is the standard deviation function (Use STDEV.P for population) to a defined dataset. It works by gaging the amount of variation happening in a dataset.
- Formulas: =AVERAGE(number1,number2,…)
=MEAN(number1,number2,…)
=STDEV.S(number1, number2, …)
- Case Study: A manager is requesting that you provide the average, median and standard deviation for the purchase prices in database 1 (the same database used in previous examples).
Answer: this is a simple formula inputting answer. However, it is important to note the power of these formulas to analyze numerical data and understand variations in data.
5) COUNT & COUNTIFS functions
What is the COUNT function? The COUNT function is used to determine the number of cells within a range or table that are numbers based.
What does COUNTIFS do? Counts the number of cells that follows a certain defined criteria(s).
- Formulas: =COUNT(value1, value2, value3,…)
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…) - Case Study: Following previous patterns, your manager requests that you confirm the number of items in the database that have a purchase price. She also asks that you provide the number of sales made to French customers.
6) CONCATENATE (CONCAT) functions
What does concatenate do? The Concatenate or CONCAT function helps users combine two or more strings (cells) into one.
- Formula: =CONCATENATE([text1], [text2], [text3]…)
- Case Study: A company needs to ship 100 products to a variety of clients, unfortunately the data is divided into 3 segments: First Name, Last Name, and Address.
Answer: If someone were to create the shipping sticker manually, they would need to copy and paste each segment. By using concatenate, the company is able to add all 3 segments into one. It could even add its name at the end! The imagine below can shed some light into the solution. Please note that “ “ provides a much needed space, “ @ “ serves as a clear denomination of where it will go and “ – Sent by GPetrium” clarifies the sender.
7) AND & OR functions
What is the AND function? AND function is a logical argument that determines whether the conditions defined in the formula are TRUE or FALSE. Under the AND function, all conditions need to be TRUE to achieve a TRUE state.
What does OR do? OR function is another logical argument that works in similar fashion to AND, however, it will turn TRUE as long as at least one of the logical arguments are TRUE.
- Formulas: =AND(logical1, logical2, logical3,…)
=OR(logical1, logical2, logical3,…) - Case Study: Your manager would like to see the purchases that were made in the United States with a price of $5 or higher. On a different request, she would also like to see all the entries in the database that happened in Germany or under product type ‘Bottle’.
Specify the criteria to be identified within the quotation marks (“ “)and input ‘>=’ to identify prices that are greater than or equal to.
8) SEARCH function
What is the SEARCH function? Search is used to find characters within a cell and where it is situated.
- Formulas: =SEARCH(find_text, within_text, [Start_Num])
- Case Study: In the database provided, the Lewis family is seen as a major buyer of the company’s products. Your manager wants to see all the purchases that have been made under the name Lewis.
Answer: Input the criteria within the quotation marks (“ “), search filtered out the #VALUE! To change the #VALUE!, it is possible to use an IFERROR formula as shown in the downloadable case study.
9) MAX & MIN functions
What is the MAX function? The MAX function determines the highest value in a defined range.
What does MIN do? It searches for the lowest value in a defined range.
- Formulas: =MAX(number1, number2, number3,…)
=MIN(number1, number2, number3,…) - Case Study: Utilizing the previous database, your manager wants to know the highest and lowest purchase price.
10) IF & IFS functions
What is the IF function? The IF function does a user defined logical test and outputs a result if it falls under TRUE and another for FALSE.
What does IFS do? The IFS function allows users to do multiple consecutive logical tests followed by the expected response if a logical test is TRUE. Note that logical tests are processed from left to right, so if Excel discovers that the second logical test is TRUE, it output the result for that and not move forwards with the rest.
- Formulas: =IF(logical_test, [value_if_true], [value_if_false])
=IFS(logical_test1, [value_if_true1], logical_test2, [value_if_true2],…) - Case Study: Based on the database, your manager wants to know whether the organization has surpassed its high expectations (e.g. $2,000). If not, the manager wants to know whether it managed to achieve the base expectation (e.g. $1,000).
Answer: To properly establish the logical test, it is important to set the sum of purchase price [sum(##:##)] to be (=>) greater than or equal to $2,000, if that is true, it outputs ‘Goal Achieved’. For the multiple IFS statement, it has been determined that if ‘Major Goal is not achieved’, it then checks to see if the base goal is achieved. If the answer is no, it will output ‘Goal Failed’.
Our Latest Posts
Read More »
Communication Plan Template
September 14, 2021
Communication Plan Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Communication helps instill important information between different parties, helping to build relations, drive collaboration and much
Team Charter Template
September 7, 2021
Team Charter Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Team charters can help teams, projects and organizations build the rules of engagement and roadmap needed
Check Register Template
August 31, 2021
Check Register Template Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Financial transactions have existed throughout history, helping buyers and sellers exchange goods based on their needs.
Employee Training Tracker
August 24, 2021
Employee Training Tracker Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp Training is a great way to learn or hone the skills relevant to one’s professional career
Bi-Weekly Budget Tool
August 17, 2021
Bi-Weekly Budget Tool Share on facebook Share on twitter Share on linkedin Share on email Share on whatsapp There are many cases where employees or contractors are paid bi-weekly for their effort. To some,
