Excel formula: IF with other calculations

Generic formula

=IF(calculation1,calculation2,calculation3)

Summary

The IF function can be combined with other calculations by nesting other formulas and functions inside IF. In the example shown, the formula in G5, copied down, is:

=IF(F5-E5>2,SUM(data1),SUM(data2))

where data1 (B5:B14) and data2 (C5:C14) are named ranges.

When the result of F5-E5 is greater than 2, the IF function returns the sum of values in data1. When the result of F5-E5 is not greater than 2, IF returns the SUM of values in data2.

Explanation

The purpose of this formula is to demonstrate how other formulas and functions can be nested inside the IF function.

The IF function takes three arguments like this:

=IF(logical_test, value_if_true, value_if_false)

In this example, the logical test is the expression F5-E5>2:

=IF(F5-E5>2 // logical test

When this expression returns TRUE, the IF function calls the SUM function to sum values in data1:

SUM(data1) // when test is TRUE

When the expression returns FALSE, IF calls the SUM function to sum values in data2:

SUM(data2) // when test is false

In either case, the SUM function returns a value to the IF function, and IF returns that value as the final result.

Other calculations

This concept can be customized any way you like. To return an average instead of a sum:

=IF(F5-E5>2,AVERAGE(data1),AVERAGE(data2))

To check if F5 and F5 are both greater than 3:

=IF(AND(F5>3,E5>3),SUM(data1),SUM(data2))

And so on. The point is you can do any calculations you like inside IF. You can even create a nested IF.

Note: nesting other calculations inside a function or formula is a common practice in many more advanced formulas. It is not limited to the IF function. You can find many examples in this list.

Author

Dave Bruns

Related formulas

If cell is this OR that

In the example shown, we want to mark or "flag" records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word "red" or "green". In D6, the formula were using is: = IF...

If this AND that

To do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the r esult is TRUE, and (optionally) do take another if the...

If NOT this or that

The behavior of the IF function can be easily extended by adding logical functions like AND , and OR , to the logical test. If you want to reverse existing logic, you can use the NOT function. In the example shown, we want to "flag" records where...

Related functions

Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

Excel SUM Function

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

Excel AND Function

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...

Excel AVERAGE Function

The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.