10 Advanced Excel formulas

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.

Syntax

=MEDIAN(number1, [number2],..)

MAX() and MIN() functions in Excel

MAX() and MIN() will provide the Maximum and minimum number from given numbers or array.

Syntax

=MAX(number1, [number2],..)

=MIN(number1, [number2],..)

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).

Syntax

= 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).

Syntax

=HLOOKUP(Lookup_value, table_array, col_index_num, [range_lookup])

10 Advanced Excel formulas
10 Advanced Excel formulas

10 Advanced Excel formulas

CONCATENATE() function in Excel

CONCATENATE() function will add or concatenate all the text that is provided inside this.

Syntax

=Concatenate(Text1, Text2,…..Textn)

IF() function in Excel

The IF () function in Excel performs a test logically returning one value for a TRUE another for a FALSE result.

Syntax

=IF(logical_test, [value_if_true], [value_if_false])

LEN() function in Excel

LEN() function in excel returns the length of the specified string.

Syntax

=Len(text)

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.

Syntax

=LOWER(text)

=UPPER(text)

=PROPER(text)

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.

Syntax

=TRIM(text)

Time functions in Excel

Syntax

WEEKNUM() is used to get the particular number of the week at a particular date.

=WEEKNUM(serial_number, [return_type])

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.

=EDATE(start_date, months)

Hope this post with advanced Excel formulas proves helpful for you, in performing numerous difficult tasks in Excel.

Keep visiting for more

Leave a Reply

Your email address will not be published.