Auto populate score based on 6 different criteria zones.

jkerns

Hello,

I have been asked to build a scorecard that would automatically insert a numerical score (0-5) based on the input given.

So for example, if a perfect score of 100 = 5; 90 - 99 = 4; 80 - 89 = 3; 70 - 79 = 2; 60 - 69 = 1; and 0 - 59 = 0, I would like to develop a formula that would automatically insert the correct score for the value given.

In the past I have used a formula to auto populate a score, but it was only based on a low threshold and a high threshold. The formula looked like this:

=IF(ISBLANK(Result1), "", IF(Result1 > [Threshold Low]1, 0, IF(Result1 < [Threshold High]1, 5, 3)))

Resulting in a score of 0 if the entered value is below the Low Threshold Low, 3 if it's between the Threshold Low and Threshold High, and 5 if it is above the Threshold High.

I'm not sure how to write a formula that will encompass five thresholds. Has anyone encountered something like this before? Any help would be greatly appreciated.

Thanks!

 

Andrée Starå

Hi,

Try something like this.

=IF(ISBLANK(Result@row); ""; IF(Result@row = 100; 5; IF(AND(Result@row >= 90; Result@row <= 99); 4; IF(AND(Result@row >= 80; Result@row <= 89); 3; IF(AND(Result@row >= 70; Result@row <= 79); 2; IF(AND(Result@row >= 60; Result@row <= 69); 1; IF(AND(Result@row >= 0; Result@row <= 59); 0)))))))

The same version but with the below changes for your and others convenience.

=IF(ISBLANK(Result@row), "", IF(Result@row = 100, 5, IF(AND(Result@row >= 90, Result@row <= 99), 4, IF(AND(Result@row >= 80, Result@row <= 89), 3, IF(AND(Result@row >= 70, Result@row <= 79), 2, IF(AND(Result@row >= 60, Result@row <= 69), 1, IF(AND(Result@row >= 0, Result@row <= 59), 0)))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jkerns

Thank you so much for the help!

The formula worked once I removed the = after the < or >. I have copied a version that worked for me below:

=IF(ISBLANK(Result@row), "", IF(Result@row = 100, 5, IF(AND(Result7 >  90, Result7 < 99), 4, IF(AND(Result@row > 80, Result@row < 89), 3, IF(AND(Result@row > 70, Result@row < 79), 2, IF(AND(Result@row > 60, Result@row < 69), 1, IF(AND(Result@row > 0, Result@row < 59), 0)))))))

However, each item in my scorecard has different scoring criteria. Rather than manually editing the formula for every entry, I would rather have the formula pull from Threshold columns. I experimented with modifying the formula above to do so. It looks like this:

=IF(ISBLANK(Result@row), "", IF(Result@row = [Threshold 5]@row, 5, IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 4]@row), 4, IF(AND(Result@row > [Threshold 4]@row, Result@row < [Threshold 3]@row), 3, IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 2]@row), 2, IF(AND(Result@row > [Threshold 2]@row, Result@row < [Threshold 1]@row), 1, IF(AND(Result@row < [Threshold 1]@row), 0)))))))

For the above formula the threshold columns have been set up as follows:

Threshold 1: 59

Threshold 2: 69

Threshold 3: 79

Threshold 4: 89

Threshold 5: 99

The formula works if it's below threshold 1 and above threshold 5 but for everything else it the cell remains blank (containing neither and error message nor a numerical score). I'm not sure where I went wrong. Do you have any suggestions for how I could fix this?

Thanks again! I am very grateful for the advice!

Paul Newcome

Try something like this...

 

=IF(ISBLANK(Result@row), "", IF(Result@row < [Threshold 1]@row, 0, IF(Result@row < [Threshold 2]@row, 1, IF(Result@row < [Threshold 3]@row, 2, IF(Result@row < [Threshold 4]@row, 3, IF(Result@row < [Threshold 5]@row, 4, 5))))))

.

If you look at your modified formula and compare it to the data in your Threshold columns, you will see that you have your less than and greater than signs mixed up. For example...

 

 IF(AND(Result@row > [Threshold 5]@row, Result@row < [Threshold 4]@row), 4

 

You are saying:

If Result@row is GREATER THAN 99 AND LESS THAN 89.......

 

That isn't possible. Since that statement is not true, it will not return a value which is why you are getting a blank.

jkerns

That worked!! Thank you so much for all of your help and your excellent explanations. I have learned so much from this!

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée