Formula to evaluate the output of a calculation and to determine a score according to a range

Options

I am currently setting up a scorecard where the individual is allocated points based on % achievements within specific ranges, and would really appreciate some help in formularizing the allocation of points (score) based on the % achievement

For example - In the example below. an Achievement of 93% will score 20 points, 105 % will score 40 points..etc

• ✭✭✭✭✭✭
edited 03/10/21
Options

I hope you're well and safe!

Try something like this.

=IF(Output@row = ""; ""; IF(Output@row >= 1,1; 50; IF(Output@row >= 1,03; 40; IF(Output@row >= 0,98; 30; IF(Output@row >= 0,9; 20; IF(Output@row <= 0,89; 10))))))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Thanks Andree, What I am looking for is a result within a range..If output is between 98 % and 102%, score 20 else

if output/result is between 103 % and 109%, 30

etc... so score is determined by output depending on which range it falls within the table... does that make sense?

• ✭✭✭✭✭✭
Options

Happy to help!

Yes, that makes sense, and the formula provided will work that way. You'll need to update the column name to match yours.

Make sense?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

It is working...thank you !,

• ✭✭✭✭✭✭
edited 03/10/21
Options

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!