Structure with conditional variables in a column

[Christopher Moretti]

I have a structure that allows me to maintain earned value through %-based tracking through milestones. I have columns that showcase milestones for an activity (Mostly based on the states of an Action). I have milestone columns. Then I associate % values to sum up based on which milestone an action is on.

with weight1 = 0.6:
with weight2 = 0.2:
with weight3 = 0.15:
with weight4 = 0.05:
IF(MATCH(Summary, "*Formal*")=1; (weight1 = 0.2 AND weight2 = 0.4 AND weight3 = 0.0 AND weight4 = 0.4))
AND
IF(MATCH(milestone4; "*Done*"); weight1+weight2+weight3+weight4;
MATCH(milestone3; "*Done*"); weight1+weight2+weight3;
MATCH(milestone2; "*Done*"); weight1+weight2;
MATCH(milestone1; "*Done*");weight1;0)

The problem is that when the Summary has "Formal" in it, the weight values do not change. Is there a better way to have conditional variable values based on a milestone?

[Chris Moretti]

Does anyone know how this can be done in a formula?

[Dionathan Lopes {ALM Works}]

Hello @Chris Moretti 

Thank you for sharing your Structure Formulas question here on the Atlassian Community

Here is an example formula you could use as a template:

---

with weight1 = IF(MATCH(Summary, "*Formal*")=1; 0.2; 0.6):
with weight2 = IF(MATCH(Summary, "*Formal*")=1; 0.4; 0.2):
with weight3 = IF(MATCH(Summary, "*Formal*")=1; 0.0; 0.15):
with weight4 = IF(MATCH(Summary, "*Formal*")=1; 0.4; 0.05):

IF((MATCH(Summary, "*Formal*")=1 and MATCH(milestone1,"*DONE*")=1); weight1;

IF((MATCH(Summary, "*Formal*")=1 and MATCH(milestone2,"*DONE*")=1); weight1+weight2;

IF((MATCH(Summary, "*Formal*")=1 and MATCH(milestone3,"*DONE*")=1); weight1+weight2+weight3;

IF((MATCH(Summary, "*Formal*")=1 and MATCH(milestone4,"*DONE*")=1); weight1+weight2+weight3+weight4

))))

---

Basically, it will check if the Summary contains "Formal" and assign the right weight to your variables, and then will compare with the status of the Milestones, showing the sum of the weight.

Please let me know if it helps and in case you need further assistance, feel free to reach out to us at support@almworks.com or support.almworks.com.

Best Regards
Dionathan L
[ALM Works]