Has anyone found a way to calculate CPI (based on planned spend and actual spend) with regards to to

shondaconnor0413

I am using the following formulas for my calculations on the parent row in my project plan but need to find a way to have it based on today's date and not the roll up from the entire project

1. SPI: =IF(AND([Planned Value]@row = 0, [% Complete]@row <> 0), [% Complete]@row + 1, IF([Planned Value]@row = 0, 0, [% Complete]@row / [Planned Value]@row)) 
2. CPI: =IF(ISBLANK(Budget@row), 0, [Planned Spend]@row / [Actual Spend]@row) 
3. Planned Value: =IF(TODAY() < [Forecasted Start Date]@row, 0, IF(TODAY() >= [Forecasted End Date]@row, 1, IF(AND(TODAY() >= [Forecasted Start Date]@row, TODAY() < [Forecasted End Date]@row), (TODAY() - [Forecasted Start Date]@row) / ([Forecasted End Date]@row - [Forecasted Start Date]@row)))) 

In photo, parent row is in red rectangle.

Project Plan.png

shondaconnor0413

Figured this out this weekend myself (I think.) I am using

=IF(OR(Budget@row <= 0, [Forecasted Start Date]@row >= TODAY()), 1, [Planned Spend]@row / [Actual Spend]@row)

shondaconnor0413

Figured this out this weekend myself (I think.) I am using

=IF(OR(Budget@row <= 0, [Forecasted Start Date]@row >= TODAY()), 1, [Planned Spend]@row / [Actual Spend]@row)