Indirect and Address Functions to Determine Schedule

From: Haggisdog (Haggisdog.17jb4v_at_excelforum-nospam.com)
Date: 06/08/04 

Date: Tue, 8 Jun 2004 08:41:29 -0500

I am struggling with writing an Excel formula that I would greatly
appreciate some help on.

In theory this sounds quite simple. I have a table which shows the
progression it takes, in months, to move from one 'level' to the next.
Another larger table shows how a number of 'starting' individuals would
progress through their levels based on the progression table mentioned
earlier.

e.g.
[FONT=courier new]Progression Table[/FONT]
-----------------------
Months
Level One 1
Level Two 2
Level Three 3
etc.

Duration Table
# of People Jan Feb Mar Apr
Level One 2 2
Level Two 3 3 5 2
Level Three 2 2 2 5
etc.

* Sorry I can't get the table formatting right in the above
illustrations

The challenge is that I can use a formula like
=INDIRECT(ADDRESS(16,IF($B$3-1>=0,3,1))) to accurately identify how
long a person will be at one level but I can't quite figure out how to
write an elegant formula that can correctly identify when one group at
a level moves to another level and joins another group (at least for
the period of time before that group moves to another level). The
formulas I keep coming up with look like this:

[FONT=courier
new]=IF(AND(D9-C9>0,Inputs!$E$38-2>0),SUM(INDIRECT(ADDRESS(9,IF(Inputs!$E$38-1>=0,3,1))))+D10,IF(AND(E9=0,C9=D9),SUM(INDIRECT(ADDRESS(10,IF(Inputs!$E$39-2>=0,3,1))))+INDIRECT(ADDRESS(9,IF(Inputs!$E$38-1>=0,3,1))),IF(AND(C9+C10=D10,Inputs!$E$38-2<0),SUM(INDIRECT(ADDRESS(10,IF(Inputs!$E$39-2>=0,3,1))))+INDIRECT(ADDRESS(9,IF(Inputs!$E$38-1>=0,3,1))),INDIRECT(ADDRESS(10,IF(Inputs!$E$39-2>=0,3,1))))))[/FONT]

I have attached a sample spread*** to show what I am trying to do.

Any help would be great appreciated. Please feel free to email me with
solutions or post your ideas in this forum.

Thanks in advance for any assistance.

Bryan Campbell
bryan.campbell@valtech.com

                Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=569893 

---
Message posted from http://www.ExcelForum.com/