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.
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.

Search JabSto ::

Custom Search