I call it a scorecard...

[Cat743]

I have a list of criteria, 1 through, for example, which measures different aspects of a "deal", rates it simply if it is complete or completed accurately or not. The criteria list will be weighted based on importance. Not all the criteria will be applicable. so either 1, 0, or N\A with final score for each based on 100 maximum points. But how do I create a scoring matrix to which will score consistently from one deal to the next. For example if deal #1 only has 8 of 10 criteria applicable versus the next deal #2 has 10 of 10 criteria applicable, how can these two be scored and compared consistently without the lesser used criteria deal score being impacted as a result of using less fields. make sense? Any insight on how to set up appreciated!!

 

[Eric W]

Welcome to the forum.

You could sum the complete aspects, then scale it to 100 based on what aspects were used in the deal:

	A	B	C	D	E
Criteria	Weights	Complete?	Score:
A
B
C
D
E
F
G	N/A
H
I	N/A
J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]75.90361[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=SUMPRODUCT(B2:B11,C2:C11)/SUM(IF(ISNUMBER(C2:C11),B2:B11))*100}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[AFPathfinder]

So, if the 10 criteria (one per column) is in columns B:K, cell L2:

=SUM(B2:K2)/COUNTIFS(B2:K2,"<>N/A",B2:K2,"<>"&"")