H
Haggisdog
I am struggling with writing an Excel formula that I would greatl
appreciate some help on.
In theory this sounds quite simple. I have a table which shows th
progression it takes, in months, to move from one 'level' to the next.
Another larger table shows how a number of 'starting' individuals woul
progress through their levels based on the progression table mentione
earlier.
e.g.
Progression Table
-----------------------
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 abov
illustrations
The challenge is that I can use a formula lik
=INDIRECT(ADDRESS(16,IF($B$3-1>=0,3,1))) to accurately identify ho
long a person will be at one level but I can't quite figure out how t
write an elegant formula that can correctly identify when one group a
a level moves to another level and joins another group (at least fo
the period of time before that group moves to another level). Th
formulas I keep coming up with look like this:
[FONT=courie
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 spreadsheet to show what I am trying to do.
Any help would be great appreciated. Please feel free to email me wit
solutions or post your ideas in this forum.
Thanks in advance for any assistance.
Bryan Campbell
(e-mail address removed)
Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56989
appreciate some help on.
In theory this sounds quite simple. I have a table which shows th
progression it takes, in months, to move from one 'level' to the next.
Another larger table shows how a number of 'starting' individuals woul
progress through their levels based on the progression table mentione
earlier.
e.g.
Progression Table
-----------------------
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 abov
illustrations
The challenge is that I can use a formula lik
=INDIRECT(ADDRESS(16,IF($B$3-1>=0,3,1))) to accurately identify ho
long a person will be at one level but I can't quite figure out how t
write an elegant formula that can correctly identify when one group a
a level moves to another level and joins another group (at least fo
the period of time before that group moves to another level). Th
formulas I keep coming up with look like this:
[FONT=courie
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 spreadsheet to show what I am trying to do.
Any help would be great appreciated. Please feel free to email me wit
solutions or post your ideas in this forum.
Thanks in advance for any assistance.
Bryan Campbell
(e-mail address removed)
Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56989