Do you know the MEDIAN function in Excel? It’s different from the AVERAGE function. The AVERAGE function will take the average of a set of numbers. For example, the average of 2,4,4,6,7, and 10 is 5.5, but the median of  2,4,4,6,7, and 10 is 5. So the MEDIAN function will calculate the middle value of a given set of numbers.

Formula

=MEDIAN(number1, [number 2], …)

How to use it?

Enter [=MEDIAN()], then highlight the number area, press Enter key.

If we change some numbers into text, blank and logic value, the function would automatically ignore.

That’s very easy, right? Now, Let’s move on to a more difficult one.

In the table below are the scores of five students.  When calculating the final score, if the score is below 60, the final score is 60.  If the score is 100 or more, the final score counts as 100.  If the score is between 60 and 100, keep the original score.

There are three ways we can quickly get the final score.

No.1 IF Function

=IF(B2>100,100,IF(B2<60,60,B2))

No.2 MAX and MIN Function

=MAX(MIN(B2,100),60)

No.3 MEDIAN Function

=MEDIAN(B2,60,100)

I deem the way of MEDIAN function to be the simplest and easiest to understand.   What do you think?