Using IF with AND, OR and NOT functions in Excel

You can achieve this with a nested IF function. In cell D3, enter the following formula:

=IF(A3 < B3, MEDIAN(B3, C3), IF(A3 > C3, "personal to incumbent", A3))

Explanation:

1. IF(A3 < B3, …): 
   This part checks if the current salary (in A3) is less than the minimum salary (in B3). If this condition is true, Excel calculates the median of the values in B3 and C3 using the `MEDIAN` function. The median of two numbers is essentially their average.

2. IF(A3 > C3, …):  
   If the first condition isn’t met (i.e., the salary is not below the minimum), this inner IF checks if the current salary (A3) is above the maximum salary (C3). If it is, the formula returns the text “personal to incumbent.”

3. Else (when A3 is between B3 and C3): 
   If neither condition is true—that is, when the current salary is between the minimum and maximum—the formula simply returns A3. This indicates that the salary is within the acceptable range.

This formula ensures that all three scenarios are covered:
- Below the minimum: Returns the median value.
- Above the maximum: Returns the text `"personal to incumbent"`.
- Between the two: Returns the current salary as is.