COUNTIFS is powerful because of the limitless criteria you can input. In the example above the product name is used to determine which product was the best seller. =COUNTIFS is the easiest way to count the number of instances a dataset meets a set of criteria. In the example: =IFERROR(FIND“BIG”,A6,1),“”) In the example above, the cell is blank so that data consumers can easily pick out which rows returned a matching value.įormula: =IFERROR(FIND“VALUE”,SELECT CELL,VALUE_IF_ERROR) Use =IFERROR to replace the #VALUE errors with any text/value. This causes a #VALUE error, and while harmless, it is distracting and an eyesore. Using the previous example, looking for specific text/values in a dataset won’t return a match. =IFERROR is something that any analyst who actively presents data should take advantage of. Note: is optional and is used to indicate the starting cell in the text to search This is particularly useful for looking for anomalies or unique identifiers.įormula: =FIND(TEXT,WITHIN_TEXT,) OR =SEARCH(TEXT,WITHIN_TEXT,) But a =SEARCH for “Big” will match with Big or big, making the query a bit broader. if you use FIND to query for “Big” you will only return Big=true results. Both are listed here because =FIND will return a case-sensitive match, i.e. =FIND/=SEARCH are powerful functions for isolating specific text within a data set. In the image below we use reference data (A2) combined with the pricing table to have excel looking up matching criteria in the first column and returning an adjacent value.įormula: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM, ) This is the perfect use case for VLOOKUP. For example, accounts receivable might know how much each product costs, but the shipping department can only provide units shipped. As an Excel user, you’ll probably need to “marry” data together at some point.
VLOOKUP is one of the most useful and recognizable data analysis functions. Much like SUMIFS, AVERAGEIFS allows you to take an average based on one or more criteria.įormula: =AVERAGEIF(SELECT CELL, CRITERIA,) In the example below, SUMIFS is used to determine how much each product is contributing to top-line revenue.įormula: =SUMIF(RANGE,CRITERIA,) The common formula used is =SUM, but what if you need to sum values based on multiple criteria? SUMIFS is it. =SUMIFS is one of the “must-know” formulas for a data analyst. OR =NETWORKDAYS(SELECT CELL, SELECT CELL,) Even workaholics need a break now and then! Using these two formulas to compare time frames is especially helpful for project management.įormulas: =DAYS(SELECT CELL, SELECT CELL) This formula determines the number of “workdays” between two dates as well as an option to account for holidays. =NETWORKDAYS is slightly more robust and useful. This is a useful tool for assessing the lifecycle of products, contracts, and run rating revenue depending on service length – a data analysis essential. This function determines the number of calendar days between two dates. COUNTA will allow you to evaluate any gaps the dataset might have without having to reorganize the data. In the life of a data analyst, you’re going to run into incomplete data sets daily. =COUNTA identifies whether a cell is empty or not. LEN is especially useful when trying to determine the differences between different Unique Identifiers (UIDs), which are often lengthy and not in the right order. As in the example above, you can identify two different kinds of product Stock Keeping Units (SKUs) using the =LEN formula to see how many characters the cell contains. =LEN quickly provides the number of characters in a given cell. This is an excellent function for creating API endpoints, product SKUs, and Java queries.įormula: =CONCATENATE(SELECT CELLS YOU WANT TO COMBINE) Combine text, numbers, dates and more from multiple cells into one. =CONCATENATE is one of the easiest to learn but most powerful formulas when conducting data analysis. Whether you dabble in Excel or use it heavily at your job, there is a function for everyone in this list.
Fifteen simple functions will improve your ability to analyze data, making you wonder how you ever lived without them. The most useful functions don’t have to be complicated. There are hundreds of functions in Excel, and it can be overwhelming trying to match the right formula with the right kind of data analysis.
If that sounds like you, then this Data Analysis in Excel top 15 is for you. Maybe you worked on it for hours, finally giving up because the data output was wrong or, the function was too complicated, and it seemed simpler to count the data yourself manually. If you’ve ever used Excel, then you’ve probably experienced the agony of choosing an incorrect formula to analyze a data set.
0 kommentar(er)
