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.

@Telecaster ,

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 …?

@Telecaster,

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

@Telecaster,

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.