If Then Statement producing some inaccurate outputs

The If Then statement is suppose to output the Length of Assignment in days. Any value between 0 and 365 days is to output 1 into the 1 or 2 column. Otherwise all other values are suppose to indicate 2 in the 1 or 2 column.

There are values in Length of assignment that are over 1000 and indicating 1 when it should be 2.

I need some help figuring out why the values are returning the wrong output.

Input 1.xlsx

Output 1.xlsx

Length of Assignment.yxmd

Developer Tools

Accepted answers

Qiu

@awhitehead012
It is nice that you want to understand.
I can still see the "" in your latest screen shot...
If you can upload your workflow, I can take a look also.

The formula should be looking like below.
The location of "(" and ")" are also important.

If tonumber([Length of Assignment]) >= 0 AND  tonumber([Length of Assignment]) <= 365 then 1 else 2 endif

All comments

Qiu

@awhitehead012
You are assigning the field "Length of Assignment" as String, which should be numeric such as "Int16".

Length of Assignment-r1.yxzp

caltang

One thing I always add on to fields of numeric comparison is the "ToNumber()" expression. So riding on @Qiu 's work, I can state:

IF ToNumber([Length of Assignment]) >= 0
AND ToNumber([Length of Assignment]) <= 365
THEN 1
ELSE 2
ENDIF

So in this case, even if I forget or let's say the field changes data types dynamically, the content is still numeric in nature. So it is unlikely to go into error. The other upside is that if something changes, let's say the data type shifts from numeric to string by force, then you will get warnings instead of an error. Easier to troubleshoot as well.

awhitehead012

Thank you. I tried the data type has INT16 and it presented an error asking to use ToNumber.

I'm trying with ToNumber but the error is still present.

IF ToNumber([Length of Assignment] >= "0" AND ToNumber([Length of Assignment] <= "365")) THEN "1" ELSE "2" ENDIF

Any thoughts?

Screenshot 2024-01-29 180026.png

Qiu

@awhitehead012
Can you check my workflow?

We need to make sure that "[Length of Assignment]" is a numeric field, which can be achieved by assigning it to Int32 in the formula too.

Now since we are comparing numeric values, remove the "" around 0 and 365. Adding quote to a number will make it string type value.

awhitehead012

Yes, both formula tools are assigned to Int16. I removed the "" and it still throws an error.

Screenshot 2024-01-29 180026.png

awhitehead012

To follow up- I also removed the "" around THEN, ELSE (1,2) -its not shown in screenshot

Qiu

@awhitehead012
Like I said, Put quotation around number will make it to be String so you can not compare.

2024-01-29 180026.png

Qiu

@awhitehead012
And also ToNumber function is not used correctly.

Can you just download and check my sample workflow?

It should be like this

ToNumber([Length of Assignment]) >= 0

awhitehead012

Hello Qiu,

I appreciate your willingness to support and provide the exact workflow but I am also hoping to learn myself instead of use what someone built for me. I checked your workflow, I copied the same formula into mine and its not working. I would like to understand why its not working. I have removed all "" and both data types reflect INT32.

Screenshot 2024-01-29 180026.png

Qiu

@awhitehead012
It is nice that you want to understand.
I can still see the "" in your latest screen shot...
If you can upload your workflow, I can take a look also.

The formula should be looking like below.
The location of "(" and ")" are also important.

If tonumber([Length of Assignment]) >= 0 AND  tonumber([Length of Assignment]) <= 365 then 1 else 2 endif

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703