5 Excel What If Formulas

Introduction to What-If Formulas in Excel

Excel is a powerful tool used for data analysis, and one of its most useful features is the ability to create what-if formulas. These formulas allow users to test different scenarios and predict outcomes based on various input values. In this article, we will explore five essential what-if formulas in Excel, including their applications and examples.

1. IF Formula

The IF formula is one of the most commonly used what-if formulas in Excel. It allows users to make logical comparisons between a value and what they expect. The syntax for the IF formula is: IF(logical_test, [value_if_true], [value_if_false]). This formula is useful for making decisions based on specific conditions. For example, if you want to determine whether a student has passed or failed a test based on their score, you can use the IF formula.

📝 Note: The IF formula can be nested to test multiple conditions and return different values based on those conditions.

2. IFERROR Formula

The IFERROR formula is used to return a custom value when a formula or function returns an error. The syntax for the IFERROR formula is: IFERROR(cell, value_if_error). This formula is useful for handling errors in formulas and providing a more user-friendly experience. For example, if you have a formula that divides a value by another value, and the denominator is zero, the IFERROR formula can return a custom message instead of the “#DIV/0!” error.

3. INDEX/MATCH Formula

The INDEX/MATCH formula is a powerful combination of two functions that allows users to look up values in a table and return a corresponding value from another column. The syntax for the INDEX/MATCH formula is: INDEX(range, MATCH(lookup_value, lookup_array, [match_type]). This formula is useful for creating dynamic what-if scenarios where the input values change. For example, if you have a table of prices for different products, you can use the INDEX/MATCH formula to look up the price of a specific product based on its name.

4. CHOOSE Formula

The CHOOSE formula allows users to select a value from a list of values based on a position number. The syntax for the CHOOSE formula is: CHOOSE(index_num, value1, [value2], ...). This formula is useful for creating what-if scenarios where the user needs to select a value from a predefined list. For example, if you have a list of different shipping options, you can use the CHOOSE formula to select the shipping cost based on the user’s selection.

5. VLOOKUP Formula

The VLOOKUP formula is used to look up values in a table and return a corresponding value from another column. The syntax for the VLOOKUP formula is: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). This formula is useful for creating what-if scenarios where the input values change. For example, if you have a table of employee data, you can use the VLOOKUP formula to look up an employee’s salary based on their ID number.

Formula	Description	Example
IF	Make logical comparisons	=IF(A1>10, "Pass", "Fail")
IFERROR	Handle errors in formulas	=IFERROR(A1/B1, "Error")
INDEX/MATCH	Look up values in a table	=INDEX(C:C, MATCH(A2, B:B, 0))
CHOOSE	Select a value from a list	=CHOOSE(A1, "Option 1", "Option 2", "Option 3")
VLOOKUP	Look up values in a table	=VLOOKUP(A2, B:C, 2, FALSE)

In summary, these five what-if formulas in Excel are essential tools for data analysis and scenario planning. By using these formulas, users can test different scenarios, make predictions, and make informed decisions. Whether you’re a student, a business professional, or a data analyst, mastering these formulas will take your Excel skills to the next level.