Using Smartsheet Baselines values
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?
Answers

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.

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.

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.

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

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. 
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 ….

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.

It was already a Text/number column.

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?

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

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.

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!!!!

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

Woo hoo! That was a rollercoaster 😀
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!