# Allocate points from 0-100 based on value

• Hi

I have a count on the number of supplier items. I have to give the supplier points from 0-100, where a low number of items means higher points and a high number of items means a low score. The min. value of items is 1 and the max. value is 3.000.

e.g.

Supplier A: 1 items (100 points)
Supplier B: 3.000 items (0 points)
Supplier C: 5.000 items (0 points).

Maybe someone can help me.

Best regards
Morten

• Re: Allocate points from 0-100 based on value

I believe this should work for you

=IF(NumOfItems>=3,0,(1-NumOfItems/3)*150)

• Re: Allocate points from 0-100 based on value

It's not working exactly like was my intention, but maybe I was not clear enough For example if we have a Supplier D with e.g. 750 items, then this supplier should get a score between 0-100 proportionally.

Best regards
Morten

• Re: Allocate points from 0-100 based on value

I apologize I thought 3.000 was 3 I forgot that it can mean 3,000. Please try =IF(B9>=3000,0,ROUND((1-B9/3000)*100,0)) where the B9 references you number of items. This will give you a score of 100 at 1, 75 at 750, 50 at 1500, 25 at 2250, and 0 at 3000

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!