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

Lee (Testing) Ziervogel

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

looking forward to your thoughts

Andrée Starå

@Lee (Testing) Ziervogel

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?

Andrée Starå

Hi @Lee (Testing) Ziervogel

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Lee (Testing) Ziervogel

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?

Andrée Starå

@Lee (Testing) Ziervogel

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?

Lee (Testing) Ziervogel

It is working...thank you !,

Andrée Starå

@Lee (Testing) Ziervogel

Excellent!

You're more than welcome!