Formula to calculate supplier evaluation scores based on their profit margin %

[The_Snook]

Hi,

I've been asked to create a formula to be used in some supplier evaluations. If the suppliers profit margin percentage is higher or lower than a pre-determined range they are either allocated full or no marks. However if the profit margin percentage falls within the bands then we would like to allocate points on a straight line sliding scale based on its relative position within the range, i.e. more points are allocated the closer it is to profit margin that scores maximum points.

Hopefully the attached explains what I'm looking to achieve to if you have any queries just give me a shout.

Thanks in advance,

Snook

[Ace_XL]

In F3

=MAX($I$4,MIN($I$3,$I$4+($I$3-$I$4)*($H$4-E3)/($H$4-$H$3)))

Copy down

[oeldere]

Please see the green cells in the attached file, if I understand your question well.

[The_Snook]

Thanks Ace_XL it looks just the job!

Thanks for trying oeldere but I think you have misunderstood what I was looking for.

Hope you both have a good weekend.

Regards,

Snook