The recently released version of Microsoft Excel 2016 has 484 functions. Out f these, 360 existed before Excel 2010. Today, I have gathered 10 advanced Excel formulas for Data Analysis in industry.
MEDIAN() function in Excel
Median is a function which is used to find the middle number in a given array of numbers.
To find a median, you generally have to sort the data in ascending order. but in Excel, just use the Median function formula and the remaining task is done automatically.
MAX() and MIN() functions in Excel
MAX() and MIN() will provide the Maximum and minimum number from given numbers or array.
VLOOKUP() function in Excel
VLOOKUP is one of the most widely used Excel functions. It can match data from a table with an input value (lookup). Basically it can return only one value. In VLOOKUP, V is for searching Vertically (in a single column).
= VLOOKUP (Lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP () function in Excel
HLOOKUP is another of the most used Excel functions. It can match table data with an input value (lookup). Basically it can return only one value. In HLOOKUP, H stands for searching Horizontally (in a single row).
=HLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])
10 Advanced Excel formulas
CONCATENATE() function in Excel
CONCATENATE() function will add or concatenate all the text that is provided inside this.
IF() function in Excel
The IF () function in Excel performs a test logically returning one value for a TRUE another for a FALSE result.
=IF(logical_test, [value_if_true], [value_if_false])
LEN() function in Excel
LEN() function in excel returns the length of the specified string.
LOWER(), UPPER() and PROPER() functions in Excel
UPPER() changes text into upper case, LOWER() changes everything into lower case. And PROPER() changes everything into proper case. First character uppercase and following characters into lower case.
TRIM() function in Excel
The TRIM() function is used to remove unnecessary spaces between words in a string. It can also return a text value with the leading and trailing spaces removed.
Time functions in Excel
WEEKNUM() is used to get the particular number of the week at a particular date.
Now, to find out the fraction of year, when two dates are supplied YEARFRAC() is used.
=YEARFRAC(start_date, end_date, [basis])
EDATE() gives the specific date when the number of days after a particular date is mentioned.
Hope this post with advanced Excel formulas proves helpful for you, in performing numerous difficult tasks in Excel.