# Project Size & Score Calculator (nested Formula with variable update)

I am trying to write a formula based on several columns of different types. Basically what I really need is a running variable that I can add to. There will be several factors that give a project a project size and score. I want a column that ends up with a points total and then a column that classifies that total.

Here is the pseudo code:

If {col1} = True then add 10 pts to Var!
If {col1} = False then add 0 pts to Var!

If {col2} = 0 to 5 then 10 pts to Var!
If {col2} = 6-10 then 20 ptst o Var!

â€¦so several of these that show total pts in one column

Then I want to have simple formula that is if total Var! = 0-25 then small, if 26-50 then large
(I can figure that one out.)

Just stuck on how to have multiple cols logically adding up based on input.

Here is one way:

``SUM(IF({col1}=TRUE,10,0), IF(AND({col2}>=0, {col2}<=5),10,0), IF(AND({col2}>=6, {col2}<=10),20,0))``
1 Like

Perfect thanks. I will give it a shot

So this worked perfectly and thanks:
SUM(IF({New Feature}=â€śYesâ€ť,10,0), MULTIPLY({Num Languages}, 10),MULTIPLY({Num Countries}, 10),MULTIPLY({Num ePRO}, 10),IF({Data Integration}=â€śYesâ€ť,10,0),IF({Wearable}=â€śYesâ€ť,10,0),IF({Provisioning}=â€śYesâ€ť,10,0))

Now this gives out a total number and I am trying to classify that number into a project size:
0-100 = Small
101-199 = Med
200-299 = Large
300 > = xLarge

I looked at the Switch formula but not sure that is it and also tried nesting Ifs but that is getting messy

Simple example F(AND({Points Total} >= 0,{Points Total} <= 100), â€śSmallâ€ť ,IF(1>0,â€śhello worldâ€ť,""))

Is the above the way to do this and just keep nesting (small (med (Large â€¦?

As long as the conditions are mutually exclusive, there is no need to nest them. Just string them one after another.

IF(AND({Points Total} >= 0,{Points Total} <= 100), â€śSmallâ€ť, â€śâ€ť)&IF(AND({Points Total} >=101,{Points Total} <= 199), â€śMediumâ€ť, â€śâ€ť)&â€¦

Fantastic! got everything all working. Thanks for the help

There are two columns that I am try to have work together. The points total and the size. However one of the selections should just always make the size be â€śSmallâ€ť. i.e Desired behavior is "If Express = Yes then the points total should be 0 or the points total should work but the size should always be small.

If in the points total col I check this condition outside of the sum it just appends the value like a text string:

Points Total Formula:
SUM(IF({New Feature}=â€śYesâ€ť,300,0), MULTIPLY({Num Languages}, 10),MULTIPLY({Num Countries}, 10),MULTIPLY({Num ePRO}, 10),IF({Data Integration}=â€śYesâ€ť,10,0),IF({Wearable}=â€śYesâ€ť,10,0),IF({Provisioning}=â€śYesâ€ť,10,0))&IF({Express}=â€śYesâ€ť, â€ś0â€ť, â€śâ€ť)

For the above if the points total is 410 and Express is Yes then the display is 4100. I could just put this inside the sum and subtract 10,000 so the points are always in the small (but this seems like a hack)

Project Size Classification Formula
IF(AND({Points Total} >= 0,{Points Total} <= 100), â€śSmallâ€ť ,"") &IF(AND({Points Total} >= 101,{Points Total} <= 199), â€śMediumâ€ť ,"") &IF(AND({Points Total} >= 200,{Points Total} <= 299), â€śLargeâ€ť ,"") &IF(AND({Points Total} >= 300,{Points Total} <= 5000), â€śX-Largeâ€ť ,"")

Question. Is there the concept of IF condition is true do something else do nothing? It seems I have to provide IF({Some column} > 100, â€śBig Dealâ€ť, â€śSmall Dealâ€ť) both results. Like if I change the â€śSmall dealâ€ť to â€śâ€ť it inserts a blank. It doesnâ€™t see to have the concept of IF({Some column} > 100, â€śBig Dealâ€ť). ie do noting and continue.

I guess I could wrap the entire sum in an IF. IF({Express} = â€śYesâ€ť, â€ś0â€ť, â€śdo the big sum formula aboveâ€ť). Would that be the way?

It would be nice if there was the IF do something else do nothing

Thanks

Simple answer, do this:

SUM(IF({New Feature}=â€śYesâ€ť,300,0), MULTIPLY({Num Languages}, 10),MULTIPLY({Num Countries}, 10),MULTIPLY({Num ePRO}, 10),IF({Data Integration}=â€śYesâ€ť,10,0),IF({Wearable}=â€śYesâ€ť,10,0),IF({Provisioning}=â€śYesâ€ť,10,0)) * IF({Express}=â€śYesâ€ť, 0, 1)

Your â€śwrapâ€ť option would also work.

3 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.