I DO know how to find the median value using the median function. I need to find a formula that I can use in another app that does NOT have a median function, and I'm testing the formula in Numbers. The other app does have if, min, and max functions. So can anyone help me create a formula to pick the median number of three values like this? The answer would be 3.
Hi Bruce,
With three values, you can find the middle value with this formula in E2:
SUM(B2:D2)−MIN(B2:D2)−MAX(B2:D2)
If the other app does not have the SUM function,
B2+C2+D2-MIN(B2:D2)−MAX(B2:D2)
Column F is a test using the Numbers MEDIAN function.
Regards,
Ian.
Edit: If the other app expects a list of cells (rather than the range B2:D2) for MIN and MAX
B2+C2+D2-MIN(B2,C2,D2)−MAX(B2,C2,D2)
Ian.
Hi Bruce,
With three values, you can find the middle value with this formula in E2:
SUM(B2:D2)−MIN(B2:D2)−MAX(B2:D2)
If the other app does not have the SUM function,
B2+C2+D2-MIN(B2:D2)−MAX(B2:D2)
Column F is a test using the Numbers MEDIAN function.
Regards,
Ian.
Edit: If the other app expects a list of cells (rather than the range B2:D2) for MIN and MAX
B2+C2+D2-MIN(B2,C2,D2)−MAX(B2,C2,D2)
Ian.
If there are "always three numbers," there's an easy solution:
Regards,
Barry
If it is just three numbers and if the other app has the SMALL or LARGE function,
=SMALL(B2:D2,2)
will give you the middle number (by size).
No small or large function, only min, max, if. Also, the numbers are examples. There will always be three numbers, but those numbers will differ.
Thanks, both of those variations work.
Find the median value no using the median function?
