Exceljet

Quick, clean, and to the point

IF with other calculations

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

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.