I am trying to add 1 condition to my formula using an OR statement.

Mathew Palter · 05/15/23

My current formula is giving an INVALID DATA TYPE error when I have added the OR Statement however before I added the OR statement there were no errors, No criteria has changed anywhere in the data sets.

=IF(ISBLANK(Probability@row), "-", IF(OR(((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) < 0, ((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)))), 0, 0.1 * ((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)))))

This is my Formula with No Error without the OR statement

=IF(ISBLANK(Probability@row), "-", IF((((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) < 0), 0, 0.1 * ((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)))))

I need the OR statement to return 0 when YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row))> 10 when added into the OR however it gives INVALID Data Type Error

Paul Newcome · 05/16/23

It looks like you have some issues with your parenthesis:

(YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row))> 10

Hollie Green · 05/16/23

If I am understanding correctly I would try this formula instead of using an Or statement.

=IF(ISBLANK(Probability@row), "-", IF((((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) < 0), 0, IF((((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) > 10), 0, 0.1) * ((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)))))

Paul Newcome · 05/16/23

I meant that portion would go in the OR statement, but the parenthesis for that portion needed some slight adjustment.

Paul Newcome · 05/16/23

Ok. So it looks like this portion is determining how many months have elapsed:

((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)))

If we do a MOD/12, that should get it to reset to zero every 12 months.

MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12)

Multiplying by .1 (as you did in your formula) will give you the appropriate 10% (10, 20, 30, etc.).

0.10 * MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12)

Subtracting that from 1 will essentially reverse the order so that you go 100, 90, 80, 70, etc..

1 - (0.10 * MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12))

To stop it from going below zero, you would use a MAX function to either pull the percentage if it is a positive number or zero if the percentage is negative.

MAX(1 - (0.10 * MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12)), 0)

A little syntax adjustment to the first part of the IF and we should end up with:

IF(Probability@row <> "", MAX(1 - (0.10 * MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12)), 0), "-")

And that should be working for your Delay Weight formula.

The biggest adjustment is using the MAX function to essentially stop the countdown at zero and the MOD function to get it to reset every 12 months.

I haven't tested this in sheet though yet, so please make sure you test it for a wide range of dates and let me know how it works (or doesn't).

Paul Newcome · 05/16/23

There is. It would be the same idea as using the MAX. I use this logic pretty regularly to keep % Complete based on date between 0% and 100%.

IF(Probability@row <> "", MIN(MAX(1 - (0.10 * MOD(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row))), 12)), 0), 0.90), "-")

So you have your number and the MAX to keep it above zero

MAX(#, 0)

Then you treat the entire MAX function as a solitary number (since that is what it will output) and wrap it in the MIN.

MIN(max_output, 0.90)

Paul Newcome · 05/16/23

It looks like you have some issues with your parenthesis:

(YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row))> 10

Hollie Green · 05/16/23

If I am understanding correctly I would try this formula instead of using an Or statement.

=IF(ISBLANK(Probability@row), "-", IF((((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) < 0), 0, IF((((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)) > 10), 0, 0.1) * ((YEAR(TODAY()) - YEAR([Target date for Stage Gate 0 meeting P0.1]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target date for Stage Gate 0 meeting P0.1]@row)))))

Paul Newcome · 05/16/23

I meant that portion would go in the OR statement, but the parenthesis for that portion needed some slight adjustment.

Mathew Palter · 05/16/23

https://community.smartsheet.com/discussion/comment/375934#Comment_375934

Thank you, I guess I would have another question on top of this now, if I were to try to have it loop so that after 12 months it starts counting again and after 24 months etc how would one go about that? So essentially 10 and 11 months after it always goes to 0 because the probability turns negative but 12 months after it resets? Do you know if there would be a way? Thanks again for the assistance it is appreciated!

Paul Newcome · 05/16/23

Are you able to provide more context as to the overall flow? I'm not sure I follow exactly what you are trying to accomplish, but it almost sounds like you would need to incorporate the MOD function.

Mathew Palter · 05/16/23

https://community.smartsheet.com/discussion/comment/375946#Comment_375946

I am trying to have a loop essentially that a project loses 10% probability month over month, after 10 months there probability would be negative and not incorporated and the delay weight would be 0. At 12 months or 1 year it resets back to 0 and as the months increase the delay weight increases until 22 months etc. If that makes any sense

Paul Newcome · 05/16/23

Would you be able to provide screenshots with some sample data? I understand what you are saying, but I can't quite visualize it.

The way I understand it is that you have a column of dates and a percentage. That percentage starts out at 100% in the current month, one month later is 90%, two months later is 80%, so on and so forth. 10, 11, and 12 months are all 0%. Then when we get to 13 months it goes back to 100% and starts the countdown all over again?

Mathew Palter · 05/16/23

https://community.smartsheet.com/discussion/comment/375952#Comment_375952

With May it should go back down to 0 as it has been 12 months. It starts at 90% and then decreases onward to 0 after 9 months so month 10 and 11 and 12 are 0 as you suggested and at month 13 it resets.

Initial Probability formula
=IF(Probability@row = "High", 0.9, IF(Probability@row = "Medium", 0.6, IF(Probability@row = "Low", 0.3)))

Delay Weight

=IF(ISBLANK(Probability@row), "-", IF(OR(AND(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)) > 9, ((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)) < 12), AND(((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)) > 21, ((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)) < 24), (((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12) + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)) < 0)), 0, 0.1 * ((YEAR(TODAY()) - YEAR([Target Date of Start]@row)) * 12 + (MONTH(TODAY()) - MONTH([Target Date of Start]@row)))))

%probability

=IF(OR([Initial Probability]@row - [Delay Weight]@row < 0, [Delay Weight]@row < -10), 0, ([Initial Probability]@row - [Delay Weight]@row))

Again I appreciate your assistance

Paul Newcome · 05/16/23