Forum Discussion
Help with referencing column data of a table depending on todays date.
Greetings all,
I am attempting to determine the present value or planned value of a project at a given point in time. I have a table to data that contains data (in example Column O-S) for the projects present value at the end of each month. I acknowledge it is a bit confusing since the date is the first of every month but that column would represent all of the cost the project should incur over the course of that month.
My plan to derive the PV or Present value at any point in time was to determine the PV given the current day is listed below:
Based on Column Letters:
Based on Description
Delta PV or change in PV is:
Prog. In Mo is progress or % of Workdays completed this month. I planned to determine this as listed below:
;
Where Wd So Far is =Networkdays(1st Day of month, Today)
WD Mo Total is = NetworkDays(1st Day of month , Last Day or month)
If I haven't lost you so far, My next need was to populate the Last Month PV & Current Mo PV columns. My aim was to use xlookup or and index match but I seem to be missing something to get this figured out. The aim is to gradually add the PV daily as the project progresses. Any insight you can give would be greatly appreciated.
| Project | Customer | PV | Today | 1st DoM | Last DoM | Last Month | WD So Far | WD Mo Total | Prog. In Mo |
| 12534 | First | 0 | 6/18/24 | 6/1/24 | 6/30/24 | 5/1/24 | 12 | 20 | 0.6 |
| 19384 | Second | 0 | 6/18/24 | 6/1/24 | 6/30/24 | 5/1/24 | 12 | 20 | 0.6 |
| 13094 | Start | 0 | 6/18/24 | 6/1/24 | 6/30/24 | 5/1/24 | 12 | 20 | 0.6 |
| 18503 | Turner | 0 | 6/18/24 | 6/1/24 | 6/30/24 | 5/1/24 | 12 | 20 | 0.6 |
| Last Month PV | Current Month PV | Delta PV | Spacer | 5/1/24 | 6/1/24 | 7/1/24 | 8/1/24 | 9/1/24 |
| 0 | 100 | 500 | 900 | 1500 | 3000 | |||
| 0 | 300 | 600 | 700 | 1200 | 3500 | |||
| 0 | 50 | 50 | 700 | 1500 | 2000 | |||
| 0 | 125 | 400 | 750 | 1300 | 4500 |
This is a sorry attempt to get an example amount of data in here since the form will not let me upload the excel doc.
1 Reply
- 1. Setup for PV Calculation
Let's assume the table data starts in cell A1:
Column G: Last Month PV
Column H: Current Month PV
Column I: Delta PV
2. Determine PV for Last Month
To get the PV for the last month, use INDEX and MATCH to find the value corresponding to the last month's date:
excel
Copy code
=INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
This formula assumes:
Row 1 has dates for PV (e.g., 5/1/24, 6/1/24, etc.).
DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1) gives the first day of last month.
$L$1:$P$1 is the range of dates in the header row.
3. Determine PV for Current Month
To get the PV for the current month:
excel
Copy code
=INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
This formula uses the first day of the current month to find the corresponding PV value.
4. Calculate Delta PV
To calculate the Delta PV as the difference between the current month and last month PV:
excel
Copy code
=H2 - G2
Where H2 is the Current Month PV and G2 is the Last Month PV.
5. Calculate Progress in Month
WD So Far: Number of workdays so far in the current month:
excel
Copy code
=NETWORKDAYS(E2, TODAY())
Where E2 is the first day of the month.
WD Mo Total: Total workdays in the current month:
excel
Copy code
=NETWORKDAYS(E2, F2)
Where E2 is the first day of the month and F2 is the last day of the month.
Prog. In Mo: Progress percentage:
excel
Copy code
=WD So Far / WD Mo Total
6. Example Formulas in Excel
Assuming the example data starts from row 2:
Last Month PV (G2):
excel
Copy code
=INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
Current Month PV (H2):
excel
Copy code
=INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
Delta PV (I2):
excel
Copy code
=H2 - G2
WD So Far (H2):
excel
Copy code
=NETWORKDAYS(D2, TODAY())
WD Mo Total (I2):
excel
Copy code
=NETWORKDAYS(D2, E2)
Prog. In Mo (J2):
excel
Copy code
=H2 / I2
