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 35 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]@rowDuration@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!