Microsoft Office Tutorials and References

In Depth Information

**SORT WITH A FORMULA**

Part

I

Figure 53.
Type in values in F5, F7, F8 and this worksheet will build the formula to use..

Summary:
A fairly complex array formula can generate random numbers

without any duplicates.

Source:
http://www.mrexcel.com/forum/showthread.php?t=222922

This formula was nominated by Barry Houdini.

SORT WITH A FORMULA

Challenge:
You have a range of numbers in cells D2:D11. You want a formula

to rearrange the numbers into ascending or descending sequence.

Solution:
You can use either the
SMALL
or
LARGE
function to solve this problem

quickly.
=SMALL(D2:D11,1)
returns the smallest number in the range,

=SMALL(D2:D11,2)
returns the second-smallest number, and so on. Unlike

the
RANK
function, the
SMALL
and
LARGE
functions deal well with ranges that

contain ties.

Of course, you need to make the ﬁ rst argument absolute by adding dollar signs:

$D$2:$D$11
. In addition, you need to ﬁ nd a way to change the 1 in the second

argument to 2, 3, 4, and so on as you copy down the range.

Instead of typing a 1 in the formula, you can use
ROW(A1)
. This function returns

a 1, and as you copy the formula down the page, it changes to
ROW(A2)
,

ROW(A3)
, etc.