Earned Value Formula Issue

[Consult123]

This is a new request stemming from an unanswered post from yesterday that became convoluted due to column changes and edits. This is not a duplicate post! My goal in this post is to be more concise so I can achieve the desired assistance needed. First, I'll briefly explain earned value so everyone understands and can participate. Lastly, I'll share questions and the spreadsheet.

For those of you who don't know what earned value is, below is a link and some highlighted sections showing EV and SV. My spreadsheet with requirements is also attached.

Here's a link to potentially help Earned Schedule Management (ESM) - MPUG

Known issues:
1. When a date is placed in column F, column H should say "Completed."
2. When no data is in column E, column H should say "Not Started."
3. Formula in column H is not working properly and aligned with parameters in SV key table to the top right.

If we can get the formula in column H working properly I would be extremely happy.!!!

Goals List Creation Rev1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	2024
2	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		SV Key	Descriptions
3	TASK-001	Put laundry in hamper	1/1/2024	1/1/2024	100%	1/1/2024	0	ON SCHEDULE		LATE	>completion date and EV <100%
4	TASK-002	Put laundry in washer	10/02/23	1/2/2024	21%		92	COMPLETED		AT RISK	³7 days behind schedule
5	TASK-003	Add detergent	12/21/23	12/20/2023	25%		-1	AT RISK		ON SCHEDULE	Meeting schedule day for day. Example: EV at 50% & 50 of 100 days spent
6	TASK-004	Start laundry	01/04/23	12/4/2024	45%		700	COMPLETED		AHEAD OF SCHEDULE	Ahead of schedule by one day or more. Example: EV at 50% & 49 of 100 days spent
7	Q2	2024								COMPLETE	Date entered into Column G
8	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)		NOT STARTED	No input in Column E
9	TASK-004	Move laundry to dryer	12/30/2023	12/22/2023			-8	LATE
10	TASK-005	Add dryer sheets	12/30/2023	12/14/2023			-16	LATE
11	TASK-006	Start dryer	1/3/2024	12/3/2024			335	COMPLETED
12	Q3	2024
13	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
14	TASK-007	Remove laundry from dryer					0	ON SCHEDULE
15	TASK-008	Clean dryer filter					0	ON SCHEDULE
16	TASK-009	Fold laundry					0	ON SCHEDULE
17	Q4	2024
18	Task ID	Task Name	Start Date	Due Date	EV % Progress	Completed Date	Days Remaining	SV (Schedule Variance)
19	TASK-010	Hang laundry					0	ON SCHEDULE
20	TASK-011	Iron laundry					0	ON SCHEDULE
21	TASK-012	Inspect laundry					0	ON SCHEDULE
2024

Cell Formulas
Range	Formula
G19:G21,G14:G16,G9:G11,G3:G6	G3	=DAYS(D3,C3)
H19:H21,H14:H16,H9:H11,H3:H6	H3	=IF(G3="","",LOOKUP(G3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H59	Cell Value	="NOT STARTED"	text	NO
H2:H59	Cell Value	="AHEAD of SCHEDULE"	text	NO
H2:H59	Cell Value	="COMPLETED"	text	NO
H3:H6	Cell Value	contains "COMPLETED"	text	NO
H2:H59	Cell Value	contains ""	text	NO
H2:H59	Cell Value	contains ""	text	NO
J8	Cell Value	="AT RISK"	text	NO
H2:H59	Cell Value	contains ""	text	NO

 

[Fluff]

Please stop posting the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.