Help needed on formula for scorecard

[Darren Durney]

Morning All

i need to create a formula for my scorecard and it is sending me round the twist, below is the layout of what i need to do, i have a list of suppliers with amount of rejections and i need to weight the score, but whatever i try the formula wont have the likes of "2-3" or "4-5":

30 Zero DMR's raised within the period specified
25 1 DMR raised within the period specified
17.5 2-3 DMR's raised within the period specified
10 4-5 DMR raised within the period specified
5 6 DMR's raised within the period specified
1 >6 DMR's raised within the period specified

Can someone help me on this please.

thanks in advance

Darren

 

[oldchippy]

Hi Darren,

I would suggest you upload a sample file with some data so we can see exactly what you are trying to do?

 

[Darren Durney]

Apologies for that, i have created a sample document with no real data on it, i need to work along the basics i have added on the spreadsheet in getting both Rejections per qtr and yearly into a weighted score and also on OTD performance also

 

[Jake Collins]

Dear Darran

With H15 containing the number of DMRs you want to then work out the weight for

=CHOOSE(MIN(H15+1,8), 30,25,17.5,17.5,10,10,5,1)

will give you the weight from your list

 

[Darren Durney]

Hi Jake thank you for that much appreciated