=MEDIAN(IF(A2:A13="M",B2:B13))
=MODE(IF(A2:A13="M",B2:B13))
Non-Array alternatives
For MEDIANIF
=AGGREGATE(16,6,(B1:B13)/(A1:A13="m"),50%)
For MODEIF
=INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),IND
EX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,),0))
51. Number of Days in a Month
Suppose, you have been given a date say 15-Nov-14 and you have to determine how many
days this particular month contains.
The formula which you need to use in the above case would be
=DAY(EOMONTH(A1,0))
Explanation - EOMONTH(A1,0) gives the last date of the month and DAY function extract
that particular Day from the last date of the month.
52. How to Know if a Year is a Leap Year
Let's say that A1 contains the year. To know whether it is a Leap Year or not, use following
formula -
=MONTH(DATE(A1,2,29))=2
TRUE means that it is Leap Year and FALSE means that this is not a Leap Year.
53. Last Working Day of the Month If a Date is Given
If A1 holds a date, the formula for calculating last Working Day of the month would be
=WORKDAY(EOMONTH(A1,0)+1,-1)
The above formula assumes that your weekends are Saturday and Sunday.
But, if your weekends are different (e.g. in gulf countries), you can use following formula -
=WORKDAY.INTL(EOMONTH(A1,0)+1,-1,"0000110")
