Microsoft Office Tutorials and References

In Depth Information

**Syntax**

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)

=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

number 8.

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.

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.

11.