Project progress

jlister

New Member
Joined
Nov 16, 2004
Messages
49
I am looking for an excel formula to identify the latest stage of a project.

A
B
C
D
E
F
G
H
I
J
1
Progress Stages
2
Project
Prepare
Start
30%
60%
90%
Complete
Stage
3
Battersby
21/09/2018
22/09/2018
15/12/2018
30%
4
Phillips
14/07/2018
20/07/2018
25/08/2018
25/11/2018
29/12/2018
90%
5
Jordsan
09/07/2018
15/07/2018
20/08/2018
20/11/2018
24/12/2018
13/01/2019
Complete
6
7

<tbody>
</tbody>

Table shows progress for each project. I need a formula to calculate the stage (shaded green) at any time and for future projects to be added to the program.

For example, if project in row 3 has a date under 60%, then the Stage should change to 60%

Also, if two stages have the same date, it needs to show the stage furthest right

Thanks for any help offered.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Two different ways to write basically the same formula, take your pick, H3 or I3 copied down:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Progress Stages</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Project</td><td style=";">Prepare</td><td style=";">Start</td><td style="text-align: right;;">30%</td><td style="text-align: right;;">60%</td><td style="text-align: right;;">90%</td><td style=";">Complete</td><td style=";">Stage</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Battersby</td><td style="text-align: right;;">9/21/2018</td><td style="text-align: right;;">9/22/2018</td><td style="text-align: right;;">12/15/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">30%</td><td style="text-align: right;;">30%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Phillips</td><td style="text-align: right;;">7/14/2018</td><td style="text-align: right;;">7/20/2018</td><td style="text-align: right;;">8/25/2018</td><td style="text-align: right;;">11/25/2018</td><td style="text-align: right;;">12/29/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">90%</td><td style="text-align: right;;">90%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jordsan</td><td style="text-align: right;;">9/7/2018</td><td style="text-align: right;;">7/15/2018</td><td style="text-align: right;;">8/20/2018</td><td style="text-align: right;;">11/20/2018</td><td style="text-align: right;;">12/24/2018</td><td style="text-align: right;;">1/13/2019</td><td style=";">Complete</td><td style=";">Complete</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet465</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999999E+307,B3:G3,B$2:G$2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=LOOKUP(<font color="Blue">2,1/B3:G3,B$2:G$2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
In case you have Project with No dates at all, use these versions:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Progress Stages</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Project</td><td style=";">Prepare</td><td style=";">Start</td><td style="text-align: right;;">30%</td><td style="text-align: right;;">60%</td><td style="text-align: right;;">90%</td><td style=";">Complete</td><td style=";">Stage</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Battersby</td><td style="text-align: right;;">9/21/2018</td><td style="text-align: right;;">9/22/2018</td><td style="text-align: right;;">12/15/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">30%</td><td style="text-align: right;;">30%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Phillips</td><td style="text-align: right;;">7/14/2018</td><td style="text-align: right;;">7/20/2018</td><td style="text-align: right;;">8/25/2018</td><td style="text-align: right;;">11/25/2018</td><td style="text-align: right;;">12/29/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">90%</td><td style="text-align: right;;">90%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jordsan</td><td style="text-align: right;;">9/7/2018</td><td style="text-align: right;;">7/15/2018</td><td style="text-align: right;;">8/20/2018</td><td style="text-align: right;;">11/20/2018</td><td style="text-align: right;;">12/24/2018</td><td style="text-align: right;;">1/13/2019</td><td style=";">Complete</td><td style=";">Complete</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">New Project</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet465</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">9.99999999999999E+307,B3:G3,B$2:G$2</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">2,1/B3:G3,B$2:G$2</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

EDIT: PS, don't forget to format result cells as "percentage".
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,098,855
Messages
5,465,090
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top