Advanced Excel Formulas
Microsoft Excel is one of the powerful tools widely used on a large scale for official and personal use. By default, Excel consists of various formulas and functions, which help to calculate the data effectively and quickly. It saves the user's time and has high computational power and efficiency. MS Excel is used in various fields, such as Education. Hospitals, Government and private organizations, etc. It is also used to create Budgets, Balance Sheets, business decisions, etc., calculate sales reports, etc. Some of the applications of Excel are as follows:
It is a widely used software that can handle all types of data. Data like spreadsheets, images, and documents. In more straightforward terms, it is defined as a single roof for all work done easier.
Advanced Excel formulas
Excel consists of some of the advanced formulas used for calculating the data. Some of the formula is explained as follows:
As the name suggests, VLOOKUP stands for Vertical Lookup. This Excel function is used to find the specific information across the vertical pattern of the given worksheet.
The syntax of the VLOOKUP function is as follows:
2. SUM Function
The SUM Function is used to sum the specified values. The value format contains arrays, numbers, ranges, and cell references. It can add up to 255 values.
The syntax of the SUM Function is as follows:
3. MAX Function
The MAX function in Excel is used to find the maximum value in the selected cell range. Cell values such as empty cells, text values, and logical values such as TRUE or FALSE is ignored.
The syntax of the MAX function is:
4. MIN Function
The MIN function in Excel is used to find the minimum or lowest value in the selected cell range. Cell values such as empty cells, text values, and logical values such as TRUE or FALSE is ignored.
The syntax of the MIN function is:
5. IF Function
The IF() function is a logical function that returns the specific result if the criteria are valid and return some other value if the criteria are FALSE.
The syntax of the IF function is:
6. SUMIF function
The SUMIF function in Excel sums the specified range of values based on specific criteria?the criteria change based on the data.
The syntax of the SUMIF function is as follows:
7. COUNT Function
The COUNT function in Excel counts the cells containing numeric values, ignoring the empty cells in the selected range.
The syntax for the COUNT function is:
8. COUNTIF Function
The COUNTIF function in Excel counts the cells containing data based on single criteria in the selected range. The COUNTIF function counts the cell containing text, numbers, and dates.
The syntax for the COUNTIF function is:
9. AND Function
The AND function is a logical function that returns the result as TRUE or FALSE. If two criteria are given in the function, it returns the result as TRUE if both conditions are satisfied. If one of the conditions is not satisfied, it returns the result as FALSE.
The syntax for the AND function is:
10. OR Function
The OR function is a logical function that returns the result as TRUE or FALSE. If two criteria are given in the function, it returns the result as TRUE if any of the conditions are satisfied. If none of the conditions are satisfied, it returns the result as FALSE.
The syntax for the OR function is:
11. RIGHT Function
The RIGHT function in Excel extracts the specified number of strings from the right of the given string.
The syntax of the RIGHT function is:
12. LEFT Function
The LEFT function in Excel extracts the specified number of strings from the left of the given string.
The syntax of the RIGHT function is:
13. CONCATENATION Function
The Concatenation function in Excel combines the data from various cells, and the result is displayed in a single cell.
The syntax of the CONCATENATE function is:
14. ROUND Function
The ROUND function in Excel rounds the selected number to a specified number of digits.
The syntax of the ROUND function is:
15. PROPER Function
The PROPER function in Excel changes the selected text to Upper case or Capitalizes the text. The spaces, numbers, and punctuation marks are not affected.
The syntax of the PROPER function is:
16. NOW Function
The NOW function in Excel displays the current time and date. It displays the date and time after the data is entered. It is edited when the worksheet is opened, or the data is edited.
NOW() displays the current date and time
NOW()-7 displays the date and time before 7 days, where the data entry is made.
NOW()+7 displays the date and time 7 days ahead when the user will make a data entry.
17. UPPER() and LOWER() Case
Excel's UPPER() case function changes the selected string to upper case.
The syntax of the UPPER Case is:
Excel's LOWER() case function changes the selected string to lowercase.
The syntax of the LOWER Case is:
18. TRIM Function
The TRIM function in the text function removes the particular text or space from the selected text. It removes the text from the beginning, between, and end of the text.
The syntax of the TRIM function is:
19. CHOOSE Function
The CHOOSE function in Excel retrieves a specified value from the list using the given position or index.
The syntax of the CHOOSE function is:
The REPT() function in Excel is used to repeat the selected characters or numbers a specified number of times.
The syntax for the REPT() function is:
The TYPE() function in Excel finds the given or selected data type and returns the result as a respective number. For example,
2 represents text
1 represents number
4 represents logical value
16 represents the error value
64 represents array
The syntax of the TYPE() function is :
The RANDBETWEEN function in Excel generates a random number between two given values. It simulates results or behavior in spreadsheets.
The syntax of the RANDBETWEEN() function is:
The convert function in Excel is used to convert the selected number from one measurement unit to another measurement.
The syntax of the Convert function is:
24. PV Function
The PV function in Excel is financial in Excel and is used to calculate investment periods. Payment per period, calculate the rate and future value and calculate arguments based on the input of the data.
The syntax of the PV function is:
25. WEEKNUM Function
The WEEKNUM function in Excel is used to find the corresponding week number of the given date in a year. The counting starts from the week containing January 1.
The syntax of the WEEKNUM function is:
26. INDEX and MATCH
The INDEX MATCH is an alternative option to HLOOKUP and VLOOKUP. Based on the row and column number, it returns the specified value.
The syntax of the INDEX function is:
The MATCH function returns the position of the cell in the given data.
The syntax of the MATCH function is:
27. IF combined with AND / OR
Combining the IF logical function with AND and OR helps to perform multiple calculations and saves the user time.
The example syntax of the formula is as follows:
Microsoft Excel provides powerful formulas to perform calculations quickly and easily. The various advanced formulas are explained in this tutorial.