New Column Value Based on Number Range

[Jandujar]

Hi Community,

I think there is an easy solution to my problem, but I just don't know it.  I am trying to create new columns with scores based on a scoring rubric.  How do I get a workflow to check to see if a number falls within a certain range and then output a score for that field in a new column?  Below is the rubric table. 

My first thought was that this could be done with a formula or I could potentially create a reference table for the workflow to check against.  I don't know how to do either of these things.  I'm essentially creating 5 new columns based on the table's range criteria.  Any help would be much appreciated.  

If a row from column name Field 1 is between 17.5 and 22.49% then the output in the new column would be 30*.9

If a row from column name Field 3 is between 50 and 59% then the output  in the new column would be 20*.7

etc

ColumnNames					Score Weight
Field 1	<10%	10 - 17.49%	17.5 - 22.49%	> 22.5%	30
Field 2	1 - 1.49	1.5 - 2	2- 2.49	> 2.5	30
Field 3	<50%	50% - 59%	60% - 67.49%	> 67.5%	20
Field 4	>125%	100% - 124.99%	75% - 99.99%	< 75%	10
Field 5	<70%	70% - 80%	80% - 90%	> 90%	10
Score Modifier	0.5	0.7	0.9	1

[atcodedog05 · Accepted solution]

Hi @Jandujar 

I was able to take your table and create a supporting look up table.

Table:

Workflow of that part for reference:

Then i created a sample data to imply your logic.

Input:

Output:

Workflow:

You can check and get back to me.

Hope this helps 🙂

[Jandujar]

Your solution worked!  Thanks so much.  

[atcodedog05]

Happy to help 🙂 @Jandujar 

Cheers and Happy Analyzing 😀