Score Dashboard in Excel

[sanket_sk]

Hi All,

I am working on dashboard in which target based scores are being given.
e.g Min & Max target for Calling - Minimum 60 call and Max >=70 calls per day Minimum score 0 and Maximum is 20%, Score will be proportionate where Achievement 61= Score will be 2% , Achv 62= Score 4% and so on....

I think i should use Min Max formula to handle this type of calculations, can you please help me designing formulas for below criteria

Input values in A Column and score will be in B column

[TABLE="width: 592"]
<colgroup><col span="4" style="text-align: center;"></colgroup><tbody>[TR]
[TD="colspan: 2"]Input[/TD]
[TD="colspan: 2"]Score [/TD]
[/TR]
[TR]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]>=70[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]30 sec[/TD]
[TD]>=60 sec[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]85.0%[/TD]
[TD]>=95%[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]30%[/TD]
[TD]>=50%[/TD]
[TD]0%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]80%[/TD]
[TD]>=90%[/TD]
[TD]0%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]95%[/TD]
[TD]>=100%[/TD]
[TD]0%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]50 min[/TD]
[TD]>=60 min[/TD]
[TD]0%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]95.0%[/TD]
[TD]>=100%[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]>=0.01[/TD]
[TD]0%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]80%[/TD]
[TD]>=90%[/TD]
[TD]0%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]80%[/TD]
[TD]>=90%[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]0.95[/TD]
[TD]>=1[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]>=0.01[/TD]
[TD]0%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]180 min[/TD]
[TD]>=120 min[/TD]
[TD]0%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]95%[/TD]
[TD]>=100%[/TD]
[TD]0%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]40min[/TD]
[TD]>=30min[/TD]
[TD]0%[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]0.95[/TD]
[TD]>=1[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]0.85[/TD]
[TD]>=0.9[/TD]
[TD]0%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]85%[/TD]
[TD]>=90%[/TD]
[TD]0%[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]


Sanket