excel 2016 review of errors in statistical functions

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, ) That 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 standard method 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. It 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. =DAYS is precisely what it implies it is. 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. That 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 more straightforward 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.

Excel 2016 review of errors in statistical functions