K
Kevin
Hi all:
I'd like some direction (or a solution!) in solving a problem that has
probably been solved before, and it involves calculating sales
commissions in a multi-tiered sales force. The sales hierarchy is
constantly expanding, but the relationships remained fixed within it
(e.g. person number 1 has 3 direct reports, 2, 3 and 4. Each, in turn,
may have an arbitrary number of people reporting to them. Say 2 has 5
and 6 reporting to her, 3 has 7, 8, 9, 10 and 11 reporting to him, and
4 has 12 reporting to him. Then, each of those people has others that
report to them, and so on...) I'm trying to develop a formula,
probably an array formula, that "maps" the relationships in a columnar
fashion, so that in one column you have the employees (1 through 12,
etc...) and each employee's direct reports follow that employee in the
same row, but subsequent columns (e.g. Cell A1 contains "1" for
employee 1, and B1 contains 2, C1 contains 3 and D1 contains 4, which
are employee 1's direct reports. Row 2 would have 3 columns of data
(2, 5, 6) and row 3 would have 6 columns of data (3, 7, 8, 9, 10 and
11).
I am comfortable with array formulas, but can't seem to get my mind
around this problem. Any help would be appreciated.
Platform for solution is Excel 2000-2003, Windows-based.
Kevin
I'd like some direction (or a solution!) in solving a problem that has
probably been solved before, and it involves calculating sales
commissions in a multi-tiered sales force. The sales hierarchy is
constantly expanding, but the relationships remained fixed within it
(e.g. person number 1 has 3 direct reports, 2, 3 and 4. Each, in turn,
may have an arbitrary number of people reporting to them. Say 2 has 5
and 6 reporting to her, 3 has 7, 8, 9, 10 and 11 reporting to him, and
4 has 12 reporting to him. Then, each of those people has others that
report to them, and so on...) I'm trying to develop a formula,
probably an array formula, that "maps" the relationships in a columnar
fashion, so that in one column you have the employees (1 through 12,
etc...) and each employee's direct reports follow that employee in the
same row, but subsequent columns (e.g. Cell A1 contains "1" for
employee 1, and B1 contains 2, C1 contains 3 and D1 contains 4, which
are employee 1's direct reports. Row 2 would have 3 columns of data
(2, 5, 6) and row 3 would have 6 columns of data (3, 7, 8, 9, 10 and
11).
I am comfortable with array formulas, but can't seem to get my mind
around this problem. Any help would be appreciated.
Platform for solution is Excel 2000-2003, Windows-based.
Kevin