The NETWORKDAYS function gives a number of working days when two supplied days are given. The WORKDAY function is used to output the date of the working day only when the number of days is specified. WEEKNUM function is used to extract a particular number of the week at a particular date. The TRIM function removes any unnecessary spaces between the words in a string. The proper function makes the first character of the letter or word as capital or in uppercase and the rest of the character in lowercase characters. UPPER function changes the lowercase characters into uppercase characters. LOWER function changes the uppercase characters into lower case characters. =RANK(CELL, RANGE-TO-RANK-AGAINST,)Īfter getting the basic understanding of the Excel formulas for data analysis now let’s focus on some advanced excel formulas for data analysis. The RANK function is used to denote how values will be ranked in a data set either in ascending or descending order. The function is used to calculate the sum of the values based on multiple criteria. SUMIFS is one of the known Excel formula for data analysis. = MATCH(common data point you’re trying to match, a column of the other data source that has the common data point, 0) ) = INDEX( column of the data you want to return) When you use INDEX/MATCH together they will pinpoint the data reference and search for a value in a single dimension array. The function INDEX/MATCH can be used together to overcome the limitation that VLOOKUP has. INDEX/MATCH function can be used for searching specific data based on input data. FIND function returns only case sensitive matches whereas the SEARCH function has no limitations. Both the functions are the same just with one major difference between them. If you are wanting to isolate specific text within a data set then FIND/SEARCH function can be used. With this formula, you can calculate average returns, price points, and margins. With this formula, you can multiply ranges or arrays together and then return the sum of the products. The function enables to add limitless criteria to the query. = AVERAGEIFS( avg_range, criteria-range1, criteria1,… )ĬOUNTIFS function can be used to count the number of instances a data set meets with specific criteria. However, the criteria range and the sum range needs to be of the same size range. The average can be different for each criterion and set of values. =MAXIFS( max-range, criteria-range1, criteria1,… )Īs the name suggests the function is used to calculate or find an average for a data set based on one or more than one criterion. =MINIFS( min-range, criteria-range1, criteria1,… ) It then looks for the minimum and maximum values and matches it with the criteria set. You just need to set the criteria and give it a set of values to work upon. MINIFS and MAXIFS are similar to knowing minimum and maximum value. =VLOOKUP(LOOKUP-VALUE, TABLE_ARRAY, COL_INDEX_NUM, ) Well, in case you set the range to TRUE then it will look for an approximate match but if you set the range to FALSE then it will lookup for an exact match. Now, you might be wondering what role does exact and approximate offer. These two modes are controlled by the range of lookup. Exact and approximate are two modes of matching that VLOOKUP offers. VLOOKUP formula is used to match the data from a table with input data. It allows the user to evaluate the gaps in the dataset without having to reorganize the data. The formula identifies whether a cell is empty or not. To overcome this problem, the COUNTA formula in excel comes in handy. If you are aspiring to become a data analyst then you need to know that in your career you are going to run into incomplete data sets almost daily. This formula comes in handy when you are trying to create texts with character limits and when trying to determine the differences between different unique identifiers (UIDs). This is just another function for data analysis that used to calculate the number of characters in a given cell. In the example above, we used a concatenate formula to combine the month and sales cells into a single column. Concatenate formula is useful when you want to combine data from different cells into a single cell. The formula enables its users to combine numbers, texts, dates, etc. It is one of the basic and most popular formulas of Excel that is used when conducting data analysis. Microsoft Excel formulas for data analysis