Project progress

[jlister]

I am looking for an excel formula to identify the latest stage of a project.

[TABLE="width: 576"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 75, bgcolor: transparent"]B
[/TD]
[TD="width: 75, bgcolor: transparent"]C
[/TD]
[TD="width: 75, bgcolor: transparent"]D
[/TD]
[TD="width: 75, bgcolor: transparent"]E
[/TD]
[TD="width: 75, bgcolor: transparent"]F
[/TD]
[TD="width: 75, bgcolor: transparent"]G
[/TD]
[TD="width: 64, bgcolor: transparent"]H
[/TD]
[TD="width: 64, bgcolor: transparent"]I
[/TD]
[TD="width: 64, bgcolor: transparent"]J
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 6"]Progress Stages
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"] Project
[/TD]
[TD="bgcolor: transparent"]Prepare
[/TD]
[TD="bgcolor: transparent"]Start
[/TD]
[TD="bgcolor: transparent"]30%
[/TD]
[TD="bgcolor: transparent"]60%
[/TD]
[TD="bgcolor: transparent"]90%
[/TD]
[TD="bgcolor: transparent"]Complete
[/TD]
[TD="bgcolor: #C6E0B4"]Stage
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]Battersby
[/TD]
[TD="bgcolor: transparent, align: right"]21/09/2018
[/TD]
[TD="bgcolor: transparent, align: right"]22/09/2018
[/TD]
[TD="bgcolor: transparent, align: right"]15/12/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #C6E0B4"]30%
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"]Phillips
[/TD]
[TD="bgcolor: transparent, align: right"]14/07/2018
[/TD]
[TD="bgcolor: transparent, align: right"]20/07/2018
[/TD]
[TD="bgcolor: transparent, align: right"]25/08/2018
[/TD]
[TD="bgcolor: transparent, align: right"]25/11/2018
[/TD]
[TD="bgcolor: transparent, align: right"]29/12/2018
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #C6E0B4"]90%
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"]Jordsan
[/TD]
[TD="bgcolor: transparent, align: right"]09/07/2018
[/TD]
[TD="bgcolor: transparent, align: right"]15/07/2018
[/TD]
[TD="bgcolor: transparent, align: right"]20/08/2018
[/TD]
[TD="bgcolor: transparent, align: right"]20/11/2018
[/TD]
[TD="bgcolor: transparent, align: right"]24/12/2018
[/TD]
[TD="bgcolor: transparent, align: right"]13/01/2019
[/TD]
[TD="bgcolor: #C6E0B4"]Complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[jtakw]

Hi,

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

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

Cell Formulas
Range	Formula
H3	=LOOKUP(9.99999999999999E+307,B3:G3,B$2:G$2)
I3	=LOOKUP(2,1/B3:G3,B$2:G$2)

 

[jtakw]

In case you have Project with No dates at all, use these versions:

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

Cell Formulas
Range	Formula
H3	=IFERROR(LOOKUP(9.99999999999999E+307,B3:G3,B$2:G$2),"")
I3	=IFERROR(LOOKUP(2,1/B3:G3,B$2:G$2),"")

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

 

[jlister]

Thanks JTAWA - Much appreciated

 

[jtakw]

You're welcome.