Looking to add formula in Supplier Scorecard file

S

Star1566

New Member
Joined
May 19, 2017
Messages
15
Hi I built a supplier scorecard and am looking to be able to still calculate the score accurately if there are N/A's. I am having the hardest time and have tried several methods. Please Help :( I have included a link to the file where the scorecard is located. It is the Supplier Scorecard tab that I would need some input or help on, thank you in advance!


https://drive.google.com/open?id=1HndaMH47O1QdC1ktYJmaPQrmBXqHzEif
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I don't feel that it is properly calculating the score. My apologies the formulas driving this are hidden in columns O through S
 
Upvote 0
I think this is correct. I did some research on the topic, but I've never worked with weighted averages before, so maybe someone with more experience can check my math...

Excel 2007 32 bit
ABCDEFGHIJKLMNOPQR
12018 SUPPLIER SCORECARD
2SUPPLIER:PERCENTAGE EARNEDOVERALL GRADED SCORE
3DATE:=P30=IF(E3>=0.9,"A",IF(E3>=0.8,"B",IF(E3>=0.7,"C",IF(E3>=0.6,"D","F"))))
4REVEWED BY:
5SUPPLIER CONTACT:
6CONTACT EMAIL:
7WEIGHT % /GRADE (LEVEL)
10LEVELDescriptionWeight %A (4)B (3)C (2)D (1)F (0)ActualSCORESCOREWeight% of Total
11COCLatest Audit 0.1295% - 100%90% - 94.9%80 - 89.9%70 - 79.9%0 - 69.9%B=IF(K11="A",1,IF(K11="B",0.8,IF(K11="C",0.7,IF(K11="D",0.6,IF(K11="F",0,IF(K11="N/A",0))))))=IF(K11="N/A",0,D11)=IFERROR(Q11/$Q$30,0)
12CAPA Evidence Review0.0695% - 100%90% - 94.9%80 - 89.9%70 - 79.9%0 - 69.9%B=IF(K12="A",1,IF(K12="B",0.8,IF(K12="C",0.7,IF(K12="D",0.6,IF(K12="F",0,IF(K12="N/A",0))))))=IF(K12="N/A",0,D12)=IFERROR(Q12/$Q$30,0)
13CAPA Response On Time0.0295% - 100%90% - 94.9%80 - 89.9%70 - 79.9%0 - 69.9%B=IF(K13="A",1,IF(K13="B",0.8,IF(K13="C",0.7,IF(K13="D",0.6,IF(K13="F",0,IF(K13="N/A",0))))))=IF(K13="N/A",0,D13)=IFERROR(Q13/$Q$30,0)
14OVERALL CATEGORY WEIGHT %:0.2TOTAL %=IF(SUM(P11:P13)=0,"N/A",SUM(P11:P13)/COUNTIF(K11:K13,"<>*N/A*"))
15COSTLEVELDescriptionWeight %A (4)B (3)C (2)D (1)F (0)SCORE
16Payment Terms0.1> 60 days44-6030-4415-30< 15 daysF=IF(K16="A",1,IF(K16="B",0.8,IF(K16="C",0.7,IF(K16="D",0.6,IF(K16="F",0,IF(K16="N/A",0))))))=IF(K16="N/A",0,D16)=IFERROR(Q16/$Q$30,0)
17SICR0.1< 5%3-4.9%0.020.010D=IF(K17="A",1,IF(K17="B",0.8,IF(K17="C",0.7,IF(K17="D",0.6,IF(K17="F",0,IF(K17="N/A",0))))))=IF(K17="N/A",0,D17)=IFERROR(Q17/$Q$30,0)
18OVERALL CATEGORY WEIGHT %:0.2TOTAL %=IF(SUM(P16:P17)=0,"N/A",SUM(P16:P17)/COUNTIF(K16:K17,"<>*N/A*"))
19QUALITYLEVELDescriptionWeight %A (4)B (3)C (2)D (1)F (0)SCORE
20QSA0.1290-100%80-89%70-79%60-69%59% >A=IF(K20="A",1,IF(K20="B",0.8,IF(K20="C",0.7,IF(K20="D",0.6,IF(K20="F",0,IF(K20="N/A",0))))))=IF(K20="N/A",0,D20)=IFERROR(Q20/$Q$30,0)
21CAR's0.09< .25%.26 - .9%1 - 1.9%2-2.9%> 3%A=IF(K21="A",1,IF(K21="B",0.8,IF(K21="C",0.7,IF(K21="D",0.6,IF(K21="F",0,IF(K21="N/A",0))))))=IF(K21="N/A",0,D21)=IFERROR(Q21/$Q$30,0)
22IQC Date from Nogales0.0690-100%80-89%70-79%60-69%59% >A=IF(K22="A",1,IF(K22="B",0.8,IF(K22="C",0.7,IF(K22="D",0.6,IF(K22="F",0,IF(K22="N/A",0))))))=IF(K22="N/A",0,D22)=IFERROR(Q22/$Q$30,0)
233rd Party Inspection0.0390-100%80-89%70-79%60-69%59% >A=IF(K23="A",1,IF(K23="B",0.8,IF(K23="C",0.7,IF(K23="D",0.6,IF(K23="F",0,IF(K23="N/A",0))))))=IF(K23="N/A",0,D23)=IFERROR(Q23/$Q$30,0)
24OVERALL CATEGORY WEIGHT %:0.3TOTAL %=IF(SUM(P20:P23)=0,"N/A",SUM(P20:P23)/COUNTIF(K20:K23,"<>*N/A*"))=SUM(R21:R23)
25DELIVERYLEVELDescriptionWeight %A (4)B (3)C (2)D (1)F (0)SCORE
26LEAD TIMESuppliers WALT, (FY17)0.12< 6061-90.991-119.9120-149.9> 150C=IF(K26="A",1,IF(K26="B",0.8,IF(K26="C",0.7,IF(K26="D",0.6,IF(K26="F",0,IF(K26="N/A",0))))))=IF(K26="N/A",0,D26)=IFERROR(Q26/$Q$30,0)
27OTDSupplier's OTD percentage (FY17)0.1298-100%95-97.9%92-94.990-91.9%< 90%C=IF(K27="A",1,IF(K27="B",0.8,IF(K27="C",0.7,IF(K27="D",0.6,IF(K27="F",0,IF(K27="N/A",0))))))=IF(K27="N/A",0,D27)=IFERROR(Q27/$Q$30,0)
28Fill RateSupplier's OTD percentage (FY17)0.060-0.9%1-5.9%6-10%11-15.9%> 16%C=IF(K28="A",1,IF(K28="B",0.8,IF(K28="C",0.7,IF(K28="D",0.6,IF(K28="F",0,IF(K28="N/A",0))))))=IF(K28="N/A",0,D28)=IFERROR(Q28/$Q$30,0)
29OVERALL CATEGORY WEIGHT %:0.3TOTAL %=IF(SUM(P26:P28)=0,"N/A",SUM(P26:P28)/COUNTIF(K26:K28,"<>*N/A*"))
30=SUMPRODUCT(P11:P28,R11:R28)/SUM(R11:R28)=SUM(Q11:Q29)=SUM(R11:R29)
31
32
33

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Supplier Scorecard​

Worksheet Formulas
CellFormula
E3=P30
H3=IF([COLOR=rgb(255]E3>=0.9,"A",IF(E3>=0.8,"B",IF([COLOR=0)]E3>=0.7,"C",IF([COLOR=rgb(0]E3>=0.6,"D","F"[/COLOR])[/COLOR]))[/COLOR])
P11=IF([COLOR=rgb(255]K11="A",1,IF(K11="B",0.8,IF([COLOR=0)]K11="C",0.7,IF([COLOR=rgb(0]K11="D",0.6,IF(K11="F",0,IF([COLOR=255)]K11="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q11=IF([COLOR=rgb(255]K11="N/A",0,D11[/COLOR])
R11=IFERROR([COLOR=rgb(255]Q11/$Q$30,0[/COLOR])
P12=IF([COLOR=rgb(255]K12="A",1,IF(K12="B",0.8,IF([COLOR=0)]K12="C",0.7,IF([COLOR=rgb(0]K12="D",0.6,IF(K12="F",0,IF([COLOR=255)]K12="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q12=IF([COLOR=rgb(255]K12="N/A",0,D12[/COLOR])
R12=IFERROR([COLOR=rgb(255]Q12/$Q$30,0[/COLOR])
P13=IF([COLOR=rgb(255]K13="A",1,IF(K13="B",0.8,IF([COLOR=0)]K13="C",0.7,IF([COLOR=rgb(0]K13="D",0.6,IF(K13="F",0,IF([COLOR=255)]K13="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q13=IF([COLOR=rgb(255]K13="N/A",0,D13[/COLOR])
R13=IFERROR([COLOR=rgb(255]Q13/$Q$30,0[/COLOR])
K14=IF([COLOR=rgb(255]SUM(P11:P13)=0,"N/A",SUM(P11:P13)/COUNTIF(K11:K13,"<>*N/A*")[/COLOR])
P16=IF([COLOR=rgb(255]K16="A",1,IF(K16="B",0.8,IF([COLOR=0)]K16="C",0.7,IF([COLOR=rgb(0]K16="D",0.6,IF(K16="F",0,IF([COLOR=255)]K16="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q16=IF([COLOR=rgb(255]K16="N/A",0,D16[/COLOR])
R16=IFERROR([COLOR=rgb(255]Q16/$Q$30,0[/COLOR])
P17=IF([COLOR=rgb(255]K17="A",1,IF(K17="B",0.8,IF([COLOR=0)]K17="C",0.7,IF([COLOR=rgb(0]K17="D",0.6,IF(K17="F",0,IF([COLOR=255)]K17="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q17=IF([COLOR=rgb(255]K17="N/A",0,D17[/COLOR])
R17=IFERROR([COLOR=rgb(255]Q17/$Q$30,0[/COLOR])
K18=IF([COLOR=rgb(255]SUM(P16:P17)=0,"N/A",SUM(P16:P17)/COUNTIF(K16:K17,"<>*N/A*")[/COLOR])
K24=IF([COLOR=rgb(255]SUM(P20:P23)=0,"N/A",SUM(P20:P23)/COUNTIF(K20:K23,"<>*N/A*")[/COLOR])
P20=IF([COLOR=rgb(255]K20="A",1,IF(K20="B",0.8,IF([COLOR=0)]K20="C",0.7,IF([COLOR=rgb(0]K20="D",0.6,IF(K20="F",0,IF([COLOR=255)]K20="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q20=IF([COLOR=rgb(255]K20="N/A",0,D20[/COLOR])
R20=IFERROR([COLOR=rgb(255]Q20/$Q$30,0[/COLOR])
P21=IF([COLOR=rgb(255]K21="A",1,IF(K21="B",0.8,IF([COLOR=0)]K21="C",0.7,IF([COLOR=rgb(0]K21="D",0.6,IF(K21="F",0,IF([COLOR=255)]K21="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q21=IF([COLOR=rgb(255]K21="N/A",0,D21[/COLOR])
R21=IFERROR([COLOR=rgb(255]Q21/$Q$30,0[/COLOR])
P22=IF([COLOR=rgb(255]K22="A",1,IF(K22="B",0.8,IF([COLOR=0)]K22="C",0.7,IF([COLOR=rgb(0]K22="D",0.6,IF(K22="F",0,IF([COLOR=255)]K22="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q22=IF([COLOR=rgb(255]K22="N/A",0,D22[/COLOR])
R22=IFERROR([COLOR=rgb(255]Q22/$Q$30,0[/COLOR])
P23=IF([COLOR=rgb(255]K23="A",1,IF(K23="B",0.8,IF([COLOR=0)]K23="C",0.7,IF([COLOR=rgb(0]K23="D",0.6,IF(K23="F",0,IF([COLOR=255)]K23="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q23=IF([COLOR=rgb(255]K23="N/A",0,D23[/COLOR])
R23=IFERROR([COLOR=rgb(255]Q23/$Q$30,0[/COLOR])
R24=SUM([COLOR=rgb(255]R21:R23[/COLOR])
P26=IF([COLOR=rgb(255]K26="A",1,IF(K26="B",0.8,IF([COLOR=0)]K26="C",0.7,IF([COLOR=rgb(0]K26="D",0.6,IF(K26="F",0,IF([COLOR=255)]K26="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q26=IF([COLOR=rgb(255]K26="N/A",0,D26[/COLOR])
R26=IFERROR([COLOR=rgb(255]Q26/$Q$30,0[/COLOR])
P27=IF([COLOR=rgb(255]K27="A",1,IF(K27="B",0.8,IF([COLOR=0)]K27="C",0.7,IF([COLOR=rgb(0]K27="D",0.6,IF(K27="F",0,IF([COLOR=255)]K27="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q27=IF([COLOR=rgb(255]K27="N/A",0,D27[/COLOR])
R27=IFERROR([COLOR=rgb(255]Q27/$Q$30,0[/COLOR])
P28=IF([COLOR=rgb(255]K28="A",1,IF(K28="B",0.8,IF([COLOR=0)]K28="C",0.7,IF([COLOR=rgb(0]K28="D",0.6,IF(K28="F",0,IF([COLOR=255)]K28="N/A",0[/COLOR]))[/COLOR])[/COLOR]))[/COLOR])
Q28=IF([COLOR=rgb(255]K28="N/A",0,D28[/COLOR])
R28=IFERROR([COLOR=rgb(255]Q28/$Q$30,0[/COLOR])
K29=IF([COLOR=rgb(255]SUM(P26:P28)=0,"N/A",SUM(P26:P28)/COUNTIF(K26:K28,"<>*N/A*")[/COLOR])
P30=SUMPRODUCT([COLOR=rgb(255]P11:P28,R11:R28[/COLOR])/SUM([COLOR=rgb(255]R11:R28[/COLOR])
Q30=SUM([COLOR=rgb(255]Q11:Q29[/COLOR])
R30=SUM([COLOR=rgb(255]R11:R29[/COLOR])

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
157
harmless92
H
A
  • Question
vlookup won't look up.
Replies
1
Views
192
ajm
A
R
  • Question
Loop through Range and Force Formula Calculation
Replies
0
Views
170
reberryjr
R
D
  • Question
Excel Office 365/ Excel Linked workbooks on network drive.
Replies
0
Views
172
dcovert
D
J
  • Question
Commission Sheet auto load into commission log
Replies
3
Views
105
Rijnsent
Rijnsent

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top