Excel Formula for Value Based on Number Ranges

Objective

To create a formula in Excel that assigns a specific value based on the range in which a number falls.

Scenario

Assuming the following ranges of values:

• 1: If the input is between 0 and 10
• 2: If the input is between 11 and 20
• 3: If the input is between 21 and 30
• 4: If the input is between 31 and 40
• 5: If the input is over 40

Excel Formula

The formula to achieve this is as follows:

=IF(A1 <= 10, 1, IF(A1 <= 20, 2, IF(A1 <= 30, 3, IF(A1 <= 40, 4, 5))))

Breakdown of the Formula:

1. Conditional Logic: The formula uses nested IF statements to evaluate the value in cell A1.
2. Range Checks:
   • A1 <= 10: Assigns 1 if true.
   • A1 <= 20: Assigns 2 if true, and so forth for subsequent conditions.
3. Final Condition: The last part assigns a value of 5 when all previous conditions fail (i.e., A1 is greater than 40).

Practical Example

Setup:

• Input Value in Cell A1: 25

Formula Application:

1. Entering the Value: Assume A1 has the value of 25.
2. Result: The formula checks:
   • Is A1 <= 10? (No)
   • Is A1 <= 20? (No)
   • Is A1 <= 30? (Yes)
   • Result will be 3.

Summary

• If A1 is within the specified ranges, the formula will return corresponding values from 1 to 5 based on the conditions set. This method allows for easy adjustments by modifying the ranges or values as needed.

Conclusion

This structured approach uses conditional statements to evaluate ranges effectively. For further enhancements and advanced techniques in Excel, consider exploring courses on the Enterprise DNA Platform.