BUGSPOTTER

Request Free Demo
Request Free Demo
Request Free Demo

Advance Excel Formulas

Advanced Excel Formulas

Advanced Excel formulas are essential for tackling complex tasks, manipulating large datasets, performing in-depth calculations, and analyzing data more efficiently. Here are some advanced Excel formulas with explanations and examples:

1. ARRAY FORMULAS (using CTRL+SHIFT+ENTER)

Array formulas allow you to perform multiple calculations on one or more items in an array and return either a single result or multiple results.

Example:

To sum the products of two columns (e.g., Column A and Column B):

				
					=SUM(A1:A5 * B1:B5)

This is an array formula, and you need to press Ctrl + Shift + Enter to execute it. It multiplies each corresponding value in columns A and B and sums the results.

2. INDEX + MATCH (Advanced Lookup)

The combination of INDEX and MATCH is a more flexible alternative to VLOOKUP and HLOOKUP. It allows you to look up a value in any column or row, regardless of its position.

Syntax:

				
					=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

This is an array formula, and you need to press Ctrl + Shift + Enter to execute it. It multiplies each corresponding value in columns A and B and sums the results.

3. SUMIFS / COUNTIFS / AVERAGEIFS

These functions are used to sum, count, or average values based on multiple criteria. SUMIFS can sum values in a range based on multiple conditions.

Example of SUMIFS:

Suppose you have a table of sales data:

  • Column A: Product
  • Column B: Sales Amount
  • Column C: Region

To sum the sales amount for the product “Apple” in the “East” region:

				
					=SUMIFS(B1:B10, A1:A10, "Apple", C1:C10, "East")

4. IFERROR

The IFERROR function helps you handle errors in formulas gracefully, replacing error values with something else (like 0 or a custom message).

Syntax:

				
					=IFERROR(formula, value_if_error)

Example:

If you are dividing values in A1 and B1, but you want to avoid a #DIV/0! error when B1 is zero:

				
					=IFERROR(A1 / B1, "Division Error")

5. OFFSET (Dynamic Range Selection)

The OFFSET function returns a reference to a range that is offset from a starting point, and it can be combined with other functions to create dynamic ranges.

Syntax:

				
					=OFFSET(reference, rows, cols, [height], [width])

Example:

If you want to sum the last 5 entries in a column (e.g., column B):

				
					=SUM(OFFSET(B1, COUNTA(B:B)-5, 0, 5, 1))

This sums the last 5 rows of column B.

6. INDIRECT

The INDIRECT function returns a cell reference specified by a text string. It is useful when you want to dynamically refer to a range or cell.

Syntax:

				
					=INDIRECT(ref_text, [a1])

Example:

If you want to refer to cell “B2” dynamically by changing the reference in A1, you can use:

				
					=INDIRECT(A1)

If A1 contains the text “B2”, this formula will return the value in cell B2.

7. TEXT (Formatting Numbers)

The TEXT function allows you to apply custom formatting to a number or date as text.

Syntax:

				
					=TEXT(value, format_text)

Example:

To format the number 12345 as currency with a comma separator:

				
					=TEXT(12345, "$#,##0.00")

To display the current date in a custom format:

				
					=TEXT(TODAY(), "dd-mm-yyyy")

8. TRANSPOSE

The TRANSPOSE function changes the orientation of a range of cells, converting rows to columns and vice versa.

Syntax:

				
					=TRANSPOSE(array)

Example:

To transpose the data from cells A1:A3 to a horizontal range:

				
					=TRANSPOSE(A1:A3)

This will convert the vertical range A1:A3 into a horizontal range.

9. SUMPRODUCT

The SUMPRODUCT function multiplies corresponding elements in the given arrays and returns the sum of those products. It’s useful for conditional summing.

Syntax:

				
					=SUMPRODUCT(array1, array2, ...)

Example:

Suppose you have two columns:

  • Column A (Quantity)
  • Column B (Price)

To calculate the total sales, you would use:

				
					=SUMPRODUCT(A1:A5, B1:B5)

This will multiply each quantity by the corresponding price and sum the results.

10. CONCATENATE / TEXTJOIN

  • CONCATENATE is an old function (still works but replaced by TEXTJOIN in newer versions of Excel). It joins multiple text values into one cell.
  • TEXTJOIN is more powerful and allows specifying a delimiter.

Example of TEXTJOIN:

To combine first name (in A1) and last name (in B1), separated by a space:

				
					=TEXTJOIN(" ", TRUE, A1, B1)

Here, TRUE ignores any empty cells.

11. XLOOKUP (Excel 365 and Excel 2021)

XLOOKUP is a modern replacement for VLOOKUP and HLOOKUP. It’s more flexible, allowing you to search both vertically and horizontally.

Syntax:

				
					=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example:

To search for the price of a product in column A and return the corresponding price from column B:

				
					=XLOOKUP("Apple", A1:A5, B1:B5, "Not Found")

12. POWER

The POWER function returns the result of a number raised to a specified power.

Syntax:

				
					=POWER(number, power)

Example:

To calculate 5 raised to the power of 3:

				
					=POWER(5, 3)

This will return 125.

13. EOMONTH

The EOMONTH function returns the last day of the month, a specified number of months before or after a given date.

Syntax:

				
					=EOMONTH(start_date, months)

Example:

To find the last day of the month, 3 months after 2024-01-01:

				
					=EOMONTH("2024-01-01", 3)

This will return 2024-04-30.

14. NETWORKDAYS

The NETWORKDAYS function calculates the number of working days (excluding weekends and holidays) between two dates.

Syntax:

				
					=NETWORKDAYS(start_date, end_date, [holidays])

Example:

To calculate the number of working days between January 1st, 2024, and January 10th, 2024, excluding the holiday on January 5th:

				
					=NETWORKDAYS("2024-01-01", "2024-01-10", {"2024-01-05"})

15. IF + AND / IF + OR

Using IF with AND or OR allows for more complex conditions.

Example of IF + AND:

If you want to check if a student has both passed Math and Science (marks >= 50):

				
					=IF(AND(A2 >= 50, B2 >= 50), "Pass", "Fail")

Example of IF + OR:

If you want to check if a student has passed either Math or Science:

				
					=IF(OR(A2 >= 50, B2 >= 50), "Pass", "Fail")

Latest Posts

  • All Posts
  • Software Testing
  • Uncategorized
Load More

End of Content.

Categories

Tags

Enroll Now and get 5% Off On Course Fees