IF statement based off a single text field

[NBOnecall]

I have what I think is an easy question, but couldn't find an my exact question while searching. The following table has the item number, the possible commission rate I want to check against, the actual commission rate and a check column. I will be comparing the actual commission rate (number), to the possible comission rate, if it is in the range or equal to it then the check is "Yes", if it doesn't equal it then the resulat is "No".

I feel like I could easily get it if the possible commission rate was just a single number, but if it has a range like the 5-16% and 8.10-14.9%, then I am not sure how to write that in the if statement.

Thanks,

Noel

[TomMartens · Accepted solution]

Hey,

you have to rebuild your table and instead of providing either a single number or a range you should use a min value and max value, then the check becomes easy again, everything else will become overly complex, as one has to determine if it's a single value by looking for the "-" character, besides that different units are used even if the represent the same.

For this reason you should rebuild your table

item:1058 min:0.08 max:0.08

item:2005 min:0.05 max:0.16

and you also have to make sure a proper data type is used like decimal.

Hopefully this provides with some new ideas

Regards,

Tom

[NBOnecall]

Agreed. After I posted I thought maybe creating a second table to look back at the text value and create a new column with a Min and Max like you suggest. Thanks!