Earned Value Formula Issue

Status
Not open for further replies.

Consult123

Banned user
Joined
Dec 30, 2023
Messages
22
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

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

1703977368009.png




Goals List Creation Rev1.xlsx
ABCDEFGHIJK
1Q12024
2Task IDTask NameStart DateDue DateEV % ProgressCompleted DateDays RemainingSV (Schedule Variance)SV KeyDescriptions
3TASK-001Put laundry in hamper1/1/20241/1/2024100%1/1/20240ON SCHEDULELATE>completion date and EV <100%
4TASK-002Put laundry in washer10/02/231/2/202421%92COMPLETEDAT RISK³7 days behind schedule
5TASK-003Add detergent12/21/2312/20/202325%-1AT RISKON SCHEDULEMeeting schedule day for day. Example: EV at 50% & 50 of 100 days spent
6TASK-004Start laundry01/04/2312/4/202445%700COMPLETEDAHEAD OF SCHEDULEAhead of schedule by one day or more. Example: EV at 50% & 49 of 100 days spent
7Q22024COMPLETEDate entered into Column G
8Task IDTask NameStart DateDue DateEV % ProgressCompleted DateDays RemainingSV (Schedule Variance)NOT STARTEDNo input in Column E
9TASK-004Move laundry to dryer12/30/202312/22/2023-8LATE
10TASK-005Add dryer sheets12/30/202312/14/2023-16LATE
11TASK-006Start dryer1/3/202412/3/2024335COMPLETED
12Q32024
13Task IDTask NameStart DateDue DateEV % ProgressCompleted DateDays RemainingSV (Schedule Variance)
14TASK-007Remove laundry from dryer0ON SCHEDULE
15TASK-008Clean dryer filter0ON SCHEDULE
16TASK-009Fold laundry0ON SCHEDULE
17Q42024
18Task IDTask NameStart DateDue DateEV % ProgressCompleted DateDays RemainingSV (Schedule Variance)
19TASK-010Hang laundry0ON SCHEDULE
20TASK-011Iron laundry0ON SCHEDULE
21TASK-012Inspect laundry0ON SCHEDULE
2024
Cell Formulas
RangeFormula
G19:G21,G14:G16,G9:G11,G3:G6G3=DAYS(D3,C3)
H19:H21,H14:H16,H9:H11,H3:H6H3=IF(G3="","",LOOKUP(G3,{-999,-1,0,1,10},{"LATE","AT RISK","ON SCHEDULE","AHEAD OF SCHEDULE","COMPLETED"}))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:H59Cell Value="NOT STARTED"textNO
H2:H59Cell Value="AHEAD of SCHEDULE"textNO
H2:H59Cell Value="COMPLETED"textNO
H3:H6Cell Valuecontains "COMPLETED"textNO
H2:H59Cell Valuecontains ""textNO
H2:H59Cell Valuecontains ""textNO
J8Cell Value="AT RISK"textNO
H2:H59Cell Valuecontains ""textNO
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Status
Not open for further replies.

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top