LIVE Advanced Excel Formulas Masterclass

The function is used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. You have to visit the function option. The insert function tab will let you enter ‘VLOOKUP’, or you can find it in the list. Once it is selected, a wizard box will open with a different set of box options.

This function is most popularly used to sum a group of numbers in a particular column. The sum function has a dedicated button for itself in the Home tab, which can be used to sum after selecting the part of the column.

This function is used to pull the maximum and the minimum values from the selected set of data. To get the maximum value, you have to enter ‘MAX’ in the function tab, and to get the minimum value; you have to enter ‘MIN’. The function will automatically draw the values from the primary data table.

The IF function is used to pull truth and false information regarding a parameter set in the variable box. The IF statement is written or broken as:

SUMIF function helps you to look up for a certain set of information that matches your criteria. The criteria are entered in a wizard box which contains the range tab, criteria tab, and the sum range tab.

The only difference between the previous function and the COUNTIF function is that the latter does not add up the matching values from the cells. It just pulls and shows the set to you.

This function is used to set more than one criterion for searching the variables. If the variable matches with the multiple criteria, then the value is returned as True, or else the search shows FALSE. The wizard box has tabs where you can enter a logical set of criteria to find the behavior of the selected set of data. The result comes with another column of TRUE and FALSE on the right side.

The OR function is a little different from the previous AND Function. OR function checks for only one criterion to be TRUE and pulls the value, whereas the AND needs every criterion to match to give a TRUE result. If no criterion matches, then the FALSE value comes out.

The LEFT function helps you to pull the part of the data in a selected column from the left side. You can set the variable or the extent of data you want in your new column via commands.

You can pull apart data from the selected column set from the right side of the information by setting variables in the command box.

This function is the combination of both LEFT and RIGHT Functions in Excel, where a new column of data is prepared by setting the variable to pull a particular section of the data from left and right.

This function is used to round up data with a lot of digits after the decimal point for the convenience of calculation. You do not need to format the cell.

This proper function is used to capitalize or uppercase the letters of a sentence in the cells. It can be done in a customized way. You can selectively change the letters in whatever format you want.

The NOW function is used to insert a column that defines the time when the entry of data is done in that particular cell on the right side of the NOW column. You can change the NOW value to date also.

This advanced excel formula is used to change the case of the cells in aggregate. The letters are changed to the command you give, like from lower to upper or vice versa, and bold them by Proper command.

This advanced excel formula is used to trim or remove extra spaces which appear when a set of data is copied from another source.

This advanced excel formula helps you to extract the customized MAX and Min values from the selected set of cells as per their orders and degrees. You can select the fifth-highest among the large data pool easily by inputting proper commands in the array.

This advanced excel formula is used to remove lengthier IF function statements and to pull the particular set of data you want. It is used when there are more than two outcomes for a given condition.

This code is used to get as many times as a character in a cell.

This advanced excel formula is very handy when you are working on a spreadsheet created by someone else. This command helps you to track down the type of data the cell is having.

This advanced excel formula lets you generate a random number between the values you have set. It helps when you want to simulate some results or behavior in the spreadsheets.

You do not have to seek help from Google all the time to find out the converted value of data in different units. The CONVERT() advanced excel formula will do the rest. The versatile function can also be used to convert currencies and many other things.

The PV function n Excel is a versatile virtual financial expert which can calculate the rate, periods for investment, payment per period, future value, and other arguments based on the input of the variables.

There are a lot of functions that are used to show a piece of information regarding time.
WEEKNUM() is used to get the particular number of the week at a particular date.
WORKDAY() gives the date of the working day when the number of days is specified.
NETWORKDAYS() gives the number of working days between two supplied dates by excluding the weekends and holidays.
YEARFRAC() allows a user to find out the fraction of the year when two dates are supplied.
EDATE() gives the specific date when a the number of days after a particular date is mentioned.

There are a set of commands for various mathematical applications to calculate values in different segments of mathematics. Finding aggregate, difference, total, quotient, LCM, GCD, etc., is a cakewalk with Excel.