Project Variance Formula Using Levels
I need help with a formula that calculates variance between actual durations and planned durations set at the start. We frequently need to reset the baseline throughout a 3-5 year project, and I would like a historical record of where we started and where we ended, without having to copy/paste current baselines before they are reset.
In the image below, the column with the formula is "Duration Variance" and it subtracts "Duration" from "Target Duration". If the task is Level 2, it takes that number and adds the previous row in the column for a total project variance. When the level changes from 2 to 1, it gives a blank cell, which is what I want.
The Problem: When the task goes back to being Level 2, it only gives a 0 instead of allowing me to select the previous level 2 task target duration.
Any help would be appreciated! Thank you!
Sarah
Answers
-
What about changing the "if true" reference, and then changing the order of execution of the "if" statement?
if(Level@row=1,0, ([Target Duration]@row-Duration@row)+[Duration Variance]14)
That may also remove the need to have an iferror statement in there as well.
-
Ok that fixed some of the issue. The last part of the formula is adding the cell above to the duration delta ([Duration Variance]14 in the formula below). How would I have it only do that addition if the previous row is not Level 1? And if it is Level 1, to skip to the row above it for the value?
Updated formula but [Duration Variance]14 is what needs to change:
-
You can add another IF statement into your formula to look at the Level in the previous row. For example, if you're writing this formula in row 15:
=IF(Level14 <> 1, [Duration Variance]14, [Duration Variance]13)
You can then add that IF statement into your formula after the + sign. Try this in row 15:
=IF(Level@row = 1, 0, ([Target Duration]@row - Duration@row) + IF(Level14 <> 1, [Duration Variance]14, [Duration Variance]13))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!