How to use formula column in Jira Structure

[Tristen John Bagain]

Hi,

Hopefully someone can help me figure out things. I'm trying to utilize the "Formula" column. wherein it will return the Story Point of the story if the progress of the ticket is greater than 70% and if its below then it should display "0". You can refer on the attached screenshot. I've been doing these via excel and its quite taxing. I think it can be done via structure. I just can't figure out the right formula. So basically logic is:

If type = Story and Progress  < 70%

   then Story Point  = 0

else

  Story Point = Story Point

Note: I don't want to override the actual Story Point of the ticket. I want to display the result of the above logic in the Formula Column

[David Niro · Accepted solution]

Hello @Tristen John Bagain ,

The reason the formula is not working as expected is because it is not currently possible to reference another formula column (such as Progress) in a formula.

That being said, it may be possible to approach the formula you are attempting to create in another way.

How is your Progress by % set up?  Does it use Time Tracking, Status or Resolution?  I believe Status or Resolution we could work with, Time Tracking may not.

Best,
David

[Tristen John Bagain]

Hi David,

I'm using status on Progress setup.

Regards,

Tristen John

[Tristen John Bagain]

Also I'm not sure how to set the value for the formula column itself. I can't make the simple logic below work.

[David Niro]

Hello @Tristen John Bagain ,

Thank you for the additional information.  The reason the formula from the screenshot is showing an error is because the value needs to be separated from the conditional statement by a colon.  It would look like this:

IF type = "Story" AND progress > 90:
  storypoints

What will happen is progress will show in red and we won't be able to map it to the progress column.  The good news is since you are using progress by Status, we can just define the values for the status in a variable that the formula can use.  It will look like this:

with progress = 
  IF status = "Open": 0
  ELSE IF status = "To Do": 0
  ELSE IF status = "In Progress": 50
  ELSE IF status = "In Review": 70
  ELSE IF status = "Done": 100
:

IF type = "Story" AND progress > 90:
  storypoints

At the top we define what we mean by "progress" in the formula and assign the values to the appropriate statuses using IF conditional statements.  

Please let me know if this helps!

Best,
David

[Tristen John Bagain]

Thank you @David Niro The formula worked perfectly fine. Thanks again!