equation

[Zalabya83]

Hello, i would like to know what equation to use if i want to use the below condition

if the result is 0% or in negative % then score is 0% and if result between 1% to 25% then score is 25% and so on till i reach score 76% till 100% then score is 100%

 

[steve the fish]

Try this:

=CEILING(MAX(0,MIN(1,A1)),0.25)

 

[Little_Clubber]

=IF(A1<=0,0,IF(A1<=0.25,0.25,IF(A1<=0.5,0.5,IF(A1<=0.75,0.75,IF(A1<=1,1)))))

Replacing A1 with the reference you are interested in.

 

[joeu2004]

But beware: what appears to be 25% or even 25.0000000000000% (15 significant digits) might actually be larger than 25%, and CEILING might surprisingly return 50%.

So, it would be prudent to write:

=CEILING(MAX(0,MIN(1,ROUND(A1,2))),0.25)

unless A1 has already been rounded explicitly, not merely by formatting.

 

[Zalabya83]

Thanks, works great