small and large

[Dryver14]

If anyone has a little time and is feeliing generous,

I often see small and large in formulas, I have looked them up in FX and see they return the "k" value.

What are the benefits of this?

 

[schielrn]

Some of the benefits are just wanting to know what the 1st, 2nd and 3rd largest values in a data set are. It can also be nice for conditional formatting before they had the feature of Top N or Bottom N because you could use conditional formatting to color any number >= say the top 3 by using the large function. It can also be really useful in summing the top N records or bottom N records like so:

=SUM(LARGE(A1:A100,{1,2,3}))

=SUM(SMALL(A1:A100,{1,2,3}))

Entered as an array formula with control+shift+enter, this will sum the top 3 values and the bottom 3 values. You can also make it a little more dynamic by using rows function:


=SUM(LARGE(A1:A100,row(1:3)))

Hope that gives a little more insight into what they are used for.

 

[Dryver14]

Thank you very much,

It is now a lot clearer than mud.

I appreciate the time.