Formula needed that gives a score to a suppliers price based on certain criteria

Help is much needed with the following please:

I need a formula that will calculate the percentage price difference based on the lowest suppliers price, and then return a score for each supplier based on the following criteria:

Marks
5 Lowest Cost
4.5 more than lowest cost and up to and including 5% higher
4 more than 5% up to and including 10% higher
3.5 more than 10% up to and including 15% higher
3 more than 15% up to and including 20% higher
2.5 more than 20% up to and including 25% higher
2 more than 25% up to and including 30% higher
1.5 more than 30% up to and including 35% higher
1 more than 35% up to and including 40% higher
0.5 more than 40% up to and including 45% higher
0 more than 45% higher

eg; in the example spreadsheet attached, supplier B price is approx 19% more expensive than Supplier A (Lowest Price) and therefore should be awarded 3.0 marks.

Is it possible to return a score like this for each supplier in the attached?

I've agonised over this one for awhile now, any help would be extremely appreciated.

LisaG

The attached file does this for you.

I've inserted a few cells in column A to shift your table over, and put the lower percentage of each range in those cells. Then it's just a VLOOKUP formula.

Hope this helps.

Pete

Try this for A person (and change B3 for others):

=INT(10*(1-B3/$C$6))+5

See file attached, probably not the quickest way to fix it. But it works.
Copy of Score Supplier Price based on Percentage of Lowest Price.xls

Cheers, thanks so much Pete_UK

Your solution works great, thanks very much for taking the time to answer my post.

Your help is very much appreciated.

LisaG