If average number is less than or equal to
This tutorial shows how to test if an average number from a range of cells is less than or equal to a specific number and return a value if the test is True or False through the use of an Excel formula, with the IF and AVERAGE functions
|
GENERIC FORMULA
=IF(AVERAGE(range)<=number,value_if_true, value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(AVERAGE(range)<=number,value_if_true, value_if_false)
ARGUMENTS EXPLANATION This formula uses the IF and AVERAGE functions test if an average number from a range is less than or equal to a specific number and return a value if the test is True or False.
Click on either the Hard Coded or Cell Reference button to view the formula that has the number that you want to test the range against for being less than or equal to the average of the values in the range and the return values directly entered into the formula or referenced to a specific cell.
In this example the formula initially calculates the average of the numbers in the range B5 to B9 (hard coded example) or B10 to B14 (cell reference example), which averages to 152. Then the formula performs a test to identify if this average value is less than or equal to 100 and return a text value of "Include" otherwise it will return a text value of "Exclude". With this example given the average of 152 is higher than 100 the formula returns a value of "Exclude". |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If average number is greater than or equal to | How to test if an average number from a range of cells is greater than or equal to a specific number and return a value if the test is True or False | |
| If average number is greater than | How to test if an average number from a range of cells is greater than a specific number and return a value if the test is True or False | |
| If average number is less than | How to test if an average number from a range of cells is less than a specific number and return a value if the test is True or False |
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| AVERAGE Function | The Excel AVERAGE function returns the average value from a specified range |