Sign up to hear about free Excel training.
I won't share your email with anyone.
2 mins read
Hat tip to Jessica S on LinkedIn who shared this tip.
In the image below the typical AND function is shown to determine if a value in column C is between the lower value from column A and the upper value of column B.
The AND function requires several different symbols and cell references.
It returns TRUE if the value is between the lower and upper value.
The MEDIAN solution is easier to create and requires fewer symbols.
In the image below you can see the MEDIAN solution.
It can refer to the three cells as a range. The result of the MEDIAN is compared to the value we are analysing. If the result is the same it means that the value is between the lower and upper values.
When comparing 3 numbers the MEDIAN function returns the number that is in the middle of all the three sorted numbers. So, if it was comparing 1,5 and 10 it would return 5. If it was comparing 1, 1 and 5 it would return 1.
So, if the result of the MEDIAN equals the value being analysed then it means that the value is between the lower and upper values.
This technique can be useful for budgets and financial models where you need to identify if the bank balance is not within the overdraft limit in a cash flow.
As well as accepting a range the MEDIAN function can also accept individual cells separated by commas.