Formula that compares multiple cut-points and renders desired number

Mar_star

Hello,

I am trying to render a number from 1 - 5 based on cut points. I am pretty sure I need a pretty long formula from this but was wondering if anyone could help me to make sure I am on the right track for my solution. Essentially this is the desired outcome I am looking for.

I have one sheet with "cut-point ranges" for 1-5 and another sheet that has the "rate" I want to compare the "rate" to each cut-point range below and have it give me a 1 - 5 based on what the "rate is"

I started the function below for to solve for at least the 2 to generate and received an error. Can anyone help? Or maybe there is an easier solution?

=IF(OR([Column15]@row] = < "{Cut-Points Range 1}", AND([Column15]@row] = > "{Cut-Points Range 2}, "2")

Carson Penticuff

Give this a try:

=IF(ISNUMBER([COLUMN15]@row), IF([COLUMN15]@row >= {Cut-Points Range 5}, "5", IF([COLUMN15]@row >= {Cut-Points Range 4}, "4", IF([COLUMN15]@row >= {Cut-Points Range 3}, "3", IF([COLUMN15]@row >= {Cut-Points Range 2}, "2","1")))), "")

Mar_star

Hey Carson,

I am getting an error #Invalid Operation. This is the formula I am using and the range I am using. Am I doing something wrong?

=IF(ISNUMBER([Column15]@row), IF([Column15]@row > ={Cut-Points Range 3}, "5", IF([Column15]@row >= {Cut-Points Range 1}, "4", IF([Column15]@row >= {Cut-Points Range 2}, "3", IF([Column15]@row >= {Cut-Points Range 4}, "2", "1")))), " ")

Carson Penticuff

Is that a direct copy/paste of your formula? The > and = symbols in "greater than or equal to" must be adjacent.

IF([Column15]@row > ={Cut-Points Range 3}

^^This will throw an error.

Mar_star

Thank you kindly!!! You are right, those spaces were rendering the error. I fixed it to now be the below formula, and it worked! Appreciate you!

=IF(ISNUMBER([Column15]@row), IF([Column15]@row >= {Cut-Points Range 3}, "5", IF([Column15]@row >= {Cut-Points Range 1}, "4", IF([Column15]@row >= {Cut-Points Range 2}, "3", IF([Column15]@row >= {Cut-Points Range 4}, "2", "1")))), " ")