Using Smartsheet Baselines values

Jenann

Is there a way to use a helper Column and Formula to turn a Smartsheet Baseline variance into a real number so I can get an average? OR is there a better way to baseline changes to end dates?

Brian_Richardson

You can take an average directly on Variance =AVG(Variance:Variance)

The number returned will be a normal number, not appended with the duration indicator like d. The number returned is number of days. Variance is always in days, regardless of the duration.

Jenann

Thanks when using the Baseline feature columns with any formula it see's the values as zero. I get a divide by zero error. If I attempt to sum, I get 0 as the sum.

Brian_Richardson

Hmm..it works for me. Maybe post your formulas? There might be something else going on.

You can also always try wrapping VALUE() around the formula components where they reference variance, or setup a helper column that takes the VALUE and then use that in calcs.

Also IFERROR can be your friend particularly if you need to do math when you have null values possibly in your denominator.

Jenann

The question first came up when in a Report I used Average on 2 columns, my Target Variance and my Committed.

Target Variance uses Smartsheet Set Baseline standard functions and Committed is created with formulas. On the report the Target always come up 0 but Committed works. Then to test, I just used a formula in the summary section on my sheet to test against each column and I had the same issue where the Target from standard baseline functionality appears to behave differently. I will try Value and see if it can manipulate what is in the column to be a number - it acts like it is not a number

Target created via columns added by

Report

Brian_Richardson

Oh I see ok. The Target Variance column created by the Baseline function is a Duration type of column. The AVG function in formulas seems to treat that as a number, but my guess is that Report Summarization isn't clever enough to know to do that.

In playing around it looks like VALUE will not take the value of a duration like Variance. So scratch that advice!

However, it may be as simple as adding one more column to your sheet, a text/number column, and point it at Target Variance =[Target Variance]@row. This returns the numeric value instead of a duration value. Then you can use that numeric value in calculations and in your reports.

Jenann

Thank you so much for helping - I attempted above still no go, I also did a test in the sheet itself to see if I could just use formula to AVG those numbers and still get zero, same with SUM. However, know I know I am looking for How to change duration into a number .. The other thought I had is I may have to stop using the generic baseline and do it all with formulas. I was hoping to avid that route ….

Brian_Richardson

It looks like you set the Target Variance Value column to be a duration column. I assume this is the =[Target Variance]@row formula column? Set it to text/number not duration and you’ll get a number back that you can use. That’s the duration to number conversion that you’re looking for.

Jenann

It was already a Text/number column.

Brian_Richardson

At this point I'm not sure what's wrong - my Smartsheet is not behaving the same way. When I do math on the Variance column, it works. When I reference the Variance column into a separate text/number, I get the value.

You might want to reach out to Support and see if they have some ideas.

This is assuming "Target Variance" is the original Variance column created by the Baseline button, right?

Jenann

good point, the values may have been lost because I used data shuttle to move from one sheet to another to combine data!! I did not even think of that but now that you say that it would no longer be the original in a sense…. I need to go back and try on the original sheet prior to any moving…. If that is the issue, then I should be able to do the translation before I shuttle and it may hold

Brian_Richardson

ohhh, yes I’d say that’s the issue. You aren’t working with a duration column then, you’re working with text. You should be able to get VALUE() of that text then in your formulas.

Jenann

That is very helpful - just getting to this point. It is more complex because this is many projects and I had hoped to use a Dynamic report. However if I solve, I may just have to rework each project plan … it is a place to sort solving THANKS!!!!

Jenann

Yes Success - If I use the advice you gave me by adding a Helper Column to each project plan that converts duration to a number, then I can use the data …BIG BIG THANKS now that I figured out I had to start back at the initial source sheet

Brian_Richardson

Woo hoo! That was a rollercoaster 😀