Use ‘Format Cell’ option in Home tab to set data type for each column <\/figcaption><\/figure>\n\n\n\nList of Excel functions<\/h1>\n\n\n\n Data analysts would want to explore data in limitless ways and therefore, in this article, we present various Excel functions that range from arithmetic operations to finding character length and looking up a value from within a column.<\/p>\n\n\n\n
SUM()<\/h2>\n\n\n\n The SUM() function calculates the sum of cell values. It\u2019s always applied on Numeric data types. Within the parentheses, specify a range as in =SUM(B3:B408) to apply addition operation on the set of values specified by the range.<\/p>\n\n\n\n
AVERAGE()<\/h2>\n\n\n\n The AVERAGE() function calculates the average of cell values. Just as the SUM(), it\u2019s too always applied on Numeric data types. Within the parentheses, specify a range as in =AVERAGE(B3:B408) to apply average operation on the set of values specified by the range.<\/p>\n\n\n\n
POWER()<\/h2>\n\n\n\n This function takes the power of any input value. POWER(number, power) function takes two input parameters, number in the cell, and power value. You can also specify a cell range as in =POWER(b3:b408, 2) to apply a power of 2 over the cells B3, to B408. The POWER() function will spill the result in more than one row for multiple cells.<\/p>\n\n\n\n
CEILING\/FLOOR<\/h2>\n\n\n\n These two functions round off any numeric value to either up or down the nearest multiple of a number of significance. Rounding to the nearest multiple means that the result will be the closest number that is the multiple of the specified significance value in either direction. <\/p>\n\n\n\n
For example, =Ceiling(18, 4) will round off the number 18 up the nearest multiple of 4, which in this case is 20. While =Floor(18, 4) will round off the number 18 down the nearest multiple of 4 which in this case is 16.<\/p>\n\n\n\n
For the case of applying formula over a range of cells in a column for any significance value \u2018x\u2019, CEILING(b2:b7, x) will return the result over the cells between b1, and b8. In this case the CEILING() function will spill the result in more than one row for multiple cells.<\/p>\n\n\n\n
SUMIF()<\/h2>\n\n\n\n It\u2019s the SUM() functionality but it offers more flexibility by allowing you to sum over a specified criteria. It takes at least two parameters, one is the range to be summed up, and the other is criteria. For example, you want to sum the cell values b3 to b7, but you want to sum only those that are greater than 5. You would put something like this, =SUMIF(b3:b7, \u201c>5\u201d).<\/p>\n\n\n\n
For the case where you want to sum values in a separate column and specify criteria from another column you would input three parameters. Take an example, you want to sum the cells b3:b14 but this time you want to sum only those cells in the B column that represent the country Europe in the I cell of the excel sheet. So you would put something like this: =SUMIF(i3:i14, \u2018\u2019Europe\u201d, b3:b14). Note, in this case, the range to be summed is not at the start of the formula, rather it\u2019s in the end. <\/p>\n\n\n\n
AVERAGEIF()<\/h2>\n\n\n\n Similar to SUMIF(), this function will output the average values over a criteria defined. You will input at least two parameters, the range to be averaged and the criteria for which the rage values fulfill. For example, to average the first five cell values in column B greater than 5, enter the following: =AVERAGEIF(b3:b7, \u201c>5\u201d).<\/p>\n\n\n\n
For more enriched criteria, the formula takes three parameters. =AVERAGEIF(i3:i14, \u2018\u2019Europe\u201d, b3:b14) will take average on cells b3 to b14 where the cell values in the I column are \u201cEurope\u201d. Note, in this case, the range to be averaged is not at the start of the formula, rather it\u2019s in the end. <\/p>\n\n\n\n
COUNTIF()<\/h2>\n\n\n\n Now that you know what an if-embedded function would do for you, this function is pretty straight forward and might be the easiest to understand. It will count the number of cell values that fulfill a defined criteria. <\/p>\n\n\n\n
For example, to count the first five cell values in column B that are greater than 5, enter the following: =COUNTIF(b3:b7, \u201c>5\u201d). The function =COUNTIF(i3:i408, \u2018\u2019Europe\u201d) will only count cell values over i3 to i408 where the cell values in the I column are \u201cEurope\u201d.<\/p>\n\n\n\n
RANK()<\/h2>\n\n\n\n This function is immensely useful in data analysis and it ranks a numeric data value in ascending or descending order by comparing its value in the list of values. It takes three arguments, a data value that is to be ranked, a list of numeric values for comparison, and an order number. If order number is 0, it ranks in descending order, and if order number is 1 the function will rank the numeric value in descending order.<\/p>\n\n\n\n
For example, =RANK(B3, B3:b408, 0) will rank value in b3 in descending order.<\/p>\n\n\n\nPreview of functions results (a)<\/figcaption><\/figure>\n\n\n\nSORT()<\/h2>\n\n\n\n While RANK() functions to determine the rank of a value as its place in the list, SORT() will simply output a sorted version of the data. Unlike RANK() it\u2019s equally useful for both numeric and text data types. To sort values in ascending order, you would put function as: =SORT(A3:A408) while for descending order sort function, you would increase the input parameters and write something as: =SORT(A3:A408, 1, -1) where -1 denotes a sort operation in descending order.<\/p>\n\n\n\n
IF()<\/h2>\n\n\n\n IF() is one of the most popular functions in excel and it allows you to compare values and outputs a logical value or your mentioned result if the comparison is either True or False. It\u2019s used for both numeric and text data types. For example, in our cars dataset we want to categorize cars based on how much mileage they cover per gallon (MPG). For this we write the following function in formula bar: =IF(B3:408<10,”Low”, “Medium”). <\/p>\n\n\n\n
The above function will output Low if MPG is less than 10, and medium otherwise.<\/p>\n\n\n\n
IFERROR()<\/h2>\n\n\n\n IFERROR() offers an amazing functionality of handling errors in a formula.This means that it will return a result you specify when there\u2019s error in the formula. An error shows that the result of calculation is false. <\/p>\n\n\n\n
An error example could be in arithmetic operation when you divide a value by 0. In this case you would write something as: =IFERROR(B3\/B13, \u201cError in calculation\u201d), it will output Error in calculation. If the result is true, the calculated value is the output. <\/p>\n\n\n\n
VLOOKUP()<\/h2>\n\n\n\n We have seen arithmetic and count functions over a range based on criteria using SUMIF(), AVERAGEIF() and COUNTIF(). <\/p>\n\n\n\n
Now let’s take a look at how we can find values from a table based on a criteria. VLOOKUP() finds values in a column (or as we say by row).<\/p>\n\n\n\n
To simplify the function, a VLOOKUP() would need following input parameters: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match \u2013 indicated as 1\/TRUE, or 0\/FALSE).<\/p>\n\n\n\n
For example, in our dataset, we want to look for the origin of the car \u2018Chevrolet Chevelle Malibu\u2019. We would use VLOOKUP and write something like this: =VLOOKUP(A3, A3:I408,9,0). It will return the output of: US. <\/p>\n\n\n\n
Please note that VLOOKUP will not work if the index of the column of the value you are looking for (the first parameter after open parenthesis) is greater than the column index mentioned in the third parameter.<\/p>\n\n\n\n
HLOOKUP()<\/h2>\n\n\n\n HLOOKUP() is used to find values in a row (or as we say by column). A column is specified from the top row of the table and a row index is provided to find the value in the same column.<\/p>\n\n\n\n
For example, in our dataset, we want to look for the cars in a specific row. We would use HLOOKUP and write something like this: =HLOOKUP(A1, A1:I408,3,1). It will return the output of: Chevrolet Chevelle Malibu.<\/p>\n\n\n\n
LEN()<\/h2>\n\n\n\n LEN() calculates the number of characters in each cell. It takes a single input parameter that is the cell value for which character length is required. <\/p>\n\n\n\n
CONCATENATE()<\/h2>\n\n\n\n It\u2019s an immensely useful function when combining different data into a single entity. This function embeds a number of data cells together. The syntax is =CONCATENATE(A3,I3). <\/p>\n\n\n\nPreview of functions results (b)<\/figcaption><\/figure>\n\n\n\nGrow your skills as Business Analyst!<\/h1>\n\n\n\n Excel is termed as one of the top skills for business analysts<\/a> other than SQL and PowerBI. With little time spent on practicing these top 15 functions, you would become better at using Excel for data analysis. At this point, we would urge you to practice these top 15 functions at your side and explore other 500+ functions to further enhance your analytical skills.<\/p>\n\n\n\nWe wish you happy learning!<\/p>\n","protected":false},"excerpt":{"rendered":"
Learn how to use SUMIF(), AVERAGEIF(), VLOOKUP(), HLOOKUP, CONCATENATE(), RANK() and many more powerful analytical functions in excel.<\/p>\n","protected":false},"author":7,"featured_media":1513,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,8],"tags":[28,43],"class_list":{"0":"post-1487","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-business-analytics","8":"category-data-analytics","9":"tag-articles","10":"tag-data-analytics"},"_links":{"self":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/comments?post=1487"}],"version-history":[{"count":34,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1487\/revisions"}],"predecessor-version":[{"id":1541,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/posts\/1487\/revisions\/1541"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/media\/1513"}],"wp:attachment":[{"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/media?parent=1487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/categories?post=1487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/testing.dicecamp.com\/insights\/wp-json\/wp\/v2\/tags?post=1487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-org-1024x528.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-cleaning-step-1-1024x607.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-step-2-1024x611.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-step-3-1024x605.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-step-4-1-1024x601.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-sol1.png\")
![\"\"](\"https:\/\/testing.dicecamp.com\/insights\/wp-content\/uploads\/2022\/05\/excel-sol2.png\")