Variance formula

Yaya
Yaya
edited 10/18/22 in Formulas and Functions

Hi,

I'd like to create a formula for caculating variance between baseline date and finish date. The baseline function doesn't really work well in SS.

The logic is if there are no dates in the Baseline finish column, then keep the variance cell empty.

If there are dates in the Baseline column, then calculate the networking days between Baseline finish date and actual finish date. If actual finish date is delayed than the baseline finish date, then I'd like it to show Minus -.

Much appreciate your help!

Thanks


Yaya

Best Answer

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    Hi @Yaya,

    I use an Target Duration as well as an Actual Duration. I then use the following column formula for a column I call "Duration Variance" to show how long something was scheduled to take, and how we performed against that. It shows whether we were x days less that target, or x days more than target.

    =IFERROR(IF([Target Duration]@row > [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days < Target", IF([Target Duration]@row < [Actual Duration]@row, ABS([Target Duration]@row - [Actual Duration]@row) + " days > Target", IF([Target Duration]@row = [Actual Duration]@row, "No Variance", ""))), "")

    Hope this is what you were meaning, and that this was helpful.

    BRgds,

    -Ray

  • Hi Ray,

    I am more referring to the INVALID data result, as some rows do not have baseline finish dates.


    The logic I am thinking is: for rows, there isn't a baseline finish date? variance cell is empty.

    If there is baseline finish date, then calculate the number of networking days (baseline finish - finish).



  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 10/18/22

    Hi @Yaya,

    I believe you're on the right track. It does seem like that invalid error is because of the null value That's why I start my formula with the IFERROR. That way when a cell is blank, it doesn't cause an invalid.

    The final "") in my formula is what is displayed if there is an error. Basically an empty string.

    BRgds,

    -Ray

  • Thanks Ray, I don't have a baseline duration. Any chance to create a formula based on baseline finish date and actual finish date? With consideration to the empty baseline finish date?

  • @Genevieve P. is this something you can help with? Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this:

    =IFERROR(NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row), "")

  • amazing. thanks @paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!