Like the MAX function, the MAXA function returns the largest numeric value in a supplied set of data. For example, the MAXA function can return the highest test score, the slowest time in a race, the maximum temperature, or the top sales number. Arguments can be provided as constants, or as references:
The primary difference between MAX and MAXA is that MAXA evaluates TRUE and FALSE values as 1 and 0 respectively. Microsoft documentation also suggests that MAXA evaluates numbers entered as text differently from MAX:
If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.
This is a bit confusing. In my testing, MAX and MAXA both evaluate text representations of numbers when supplied directly as arguments. For example:
=MAXA(5,"10")// returns 10=MAX(5,"10")// also returns 10
The difference appears to be that when cell references include text, MAXA evaluates them as zero in the calculation, whereas MAX simply ignores them. For example, if A1 contains -5, and A2 contains "10" (as a text value):
The Excel SMALL function returns numeric values based on their position in a list ranked by value. In other words, it can retrive "nth smallest" values - smallest value, 2nd smallest value, 3rd smallest value, etc.
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time) values, using an...