Project progress

[jlister]

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.

 

[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.