Microsoft Office Tutorials and References
In Depth Information
66. Select cell C66. Click the fill handle and drag down to cell C70. You
now have a list of the top five open receivables.
77. At this point, you know the amounts of the top receivables, but this
immediately brings up the question of which customers have those re-
amount. Note that this method assumes that no two customers in the
top five have exactly the same receivable.
88. Enter the following intermediate formula in cell B66:
=MATCH(C66,$C$2:$C$60,0). This formula tells Excel to take the re-
ceivable value in cell C66 and to find it in the list of open receiv-
ables. The MATCH function returns the row number within C2:C60 that
has the matching value. For example, 13,560.43 is found in cell C9.
This is the eighth row in the range of C2:C60, so MATCH returns the
99. The largest receivable in the eighth row of a range is not useful to a
person trying to collect accounts receivables, so to return the name,
ask for the eighth value in the range of B2:B66. You can use the INDEX
function to do this. =INDEX($B$2:$B$66,8) returns the customer with
the largest receivable.
10. Combine the formulas from step 8 and step 9 into a single formula in
cell B66: =INDEX($B$2:$B$60,MATCH(C66,$C$2:$C$60,0)).
11. Copy the formula in cell B66 down through cell B70.
As shown in Figure 14.2 , the result is a table in A66:A70 that shows the five
largest customers. After receiving checks today, you can update the receiv-
able amounts in C2:C60. If Best Raft sent in a check for $10,000, the formulas
would automatically move Magnificent Electronics up to the fourth position
and move the sixth customer up to the fifth spot.
Rather than entering the numbers 1 through 5 in A66:A70, you could use the
ROW() function to return the values of 1 to 5. In cell C66, use
=LARGE($C$2:$C$60,ROW(A1)). Because the row number of cell A1 is 1, the
row function returns a 1 as the second argument to the LARGE function. This
method has the advantage that as you drag the formula down, it switches to
ROW(A2) for 2, ROW(A3) for 3, and so on.