Auto populate score based on 6 different criteria zones.

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!

Find more posts tagged with

Finance

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…