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 fi rst argument absolute by adding dollar signs:
$D$2:$D$11 . In addition, you need to fi 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