 # 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

One additional question-

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.