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

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Tim_Simmons

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.

sarah_specialized

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:

Genevieve P.

Hi @sarah_specialized

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.