New to Smartsheet. trying to figure out a formula

Ray Sanchez

I am trying to figure out a formula that will help me track data. I work in construction so I think this will help me out.

Example, I have 100 duration days to complete interior framing. I have gone through the schedule a picked what is interior framing and that will give me a % complete of what is done.

Now what I am asking for is. Is there a formula that will, When the % completion gets higher. The duration of days will get lower? Example, I type in 10% in % complete column and the duration will go from 100 to 90 days.

Paul Newcome

Try enabling the dependencies and se if that makes a difference. It may be that because the dependencies are not enabled the Duration column is simply a text value because of the "d" included in the string as opposed to being stored as a numerical value (number of days for dependencies).

Paul Newcome

Happy to help. 👍️

In this particular instance, yes. Dependencies should be enabled. There are ways to write formulas to accomplish this without dependencies though.

There is definitely a bit of a learning curve with Smartsheet, but once you get the basics down you should be pretty well off. This Community is a great resource and so is the learning center for getting up and running. Best of luck to you and don't hesitate to ask plenty of questions.

Paul Newcome

You should be able to use

=[% Complete]@row * (1 - Duration@row)

Ray Sanchez

@Paul Newcome that work. Thanks As you can see in the picture. 100% = to -3. The duration is 4D. Now in order to equal the day I assuming that I will need to change the 1 in the formula?

Ray Sanchez

@Paul Newcome Figured it out. had to put it to 0. thanks for the help

Paul Newcome

My apologies. I subtracted the wrong value from 1. It should have been the % Complete. Try this instead.

=(1 - [% Complete]@row) * Duration@row

Ray Sanchez

@Paul Newcome Thanks for the help. New problem. it works great in 1 schedule but when i type this formula in another schedule with the same information. Its giving me a "Invalid operation". Why is that? Please see photo

Ray Sanchez

Paul Newcome

When you enter the percentage, are you just entering a number into a column that is formatted as a percentage? Are dependencies turned on?

Ray Sanchez

Correct, I am just entering the percentage. Yes the percentage was already pre-feild from creating the project.

I went to Project settings and Dependencies is unchecked.

Paul Newcome

Try enabling the dependencies and se if that makes a difference. It may be that because the dependencies are not enabled the Duration column is simply a text value because of the "d" included in the string as opposed to being stored as a numerical value (number of days for dependencies).

Ray Sanchez

So the dependencies has to be checked in order for it to work. I checked it and it works now. Thanks for the help. I am on a crash course learning process with smartsheet at the moment. We just got it yesterday and i been trying to put in all schedules and settings. Eventually I will take the courses to get a better understanding of the software. I am pretty sure I will be asking for help again soon on anther topic.

Again, Thank you.

Paul Newcome

Happy to help. 👍️

In this particular instance, yes. Dependencies should be enabled. There are ways to write formulas to accomplish this without dependencies though.

There is definitely a bit of a learning curve with Smartsheet, but once you get the basics down you should be pretty well off. This Community is a great resource and so is the learning center for getting up and running. Best of luck to you and don't hesitate to ask plenty of questions.