How to Calculate Scoring for High, Medium and Low from a Series of Criteria

A

Ampinz

New Member
Joined
Oct 13, 2016
Messages
14
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Criteria
[/TD]
[TD]Range
[/TD]
[TD]Score
[/TD]
[/TR]
[TR]
[TD]Account Balance
[/TD]
[TD]$500,000.00 and $999,999,999.99
[/TD]
[TD]89
[/TD]
[/TR]
[TR]
[TD]Account Balance
[/TD]
[TD]$100,000.00 and $499,999.99
[/TD]
[TD]79
[/TD]
[/TR]
[TR]
[TD]Account Balance
[/TD]
[TD]$50,000.00 and $99,999.99
[/TD]
[TD]69
[/TD]
[/TR]
[TR]
[TD]Account Balance
[/TD]
[TD]$25,000.00 and $49,999.99
[/TD]
[TD]59
[/TD]
[/TR]
[TR]
[TD]Discharge Date
[/TD]
[TD]121 - 180
[/TD]
[TD]68
[/TD]
[/TR]
[TR]
[TD]Discharge Date
[/TD]
[TD]181 - 300
[/TD]
[TD]48
[/TD]
[/TR]
[TR]
[TD]Discharge Date
[/TD]
[TD]301 - 360
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]Discharge Date
[/TD]
[TD]361 - 9999
[/TD]
[TD]38
[/TD]
[/TR]
[TR]
[TD] Denial Date
[/TD]
[TD]30 to 60
[/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD] Denial Date
[/TD]
[TD]61 and 9999
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]Last User Work Date
[/TD]
[TD]45 and 9999
[/TD]
[TD]47
[/TD]
[/TR]
</tbody>[/TABLE]




















Hi Excel Gurus!

Can someone please help me with a formula or set of formulas to calculate the scoring possibilities of the following criteria? There are 4 categories with specific values. From all the scoring possibilities, I will calculate the potential H/M/L ranges.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
It would help if you could give a few examples of what you're looking for. It's easy enough to create tables to find the individual scores. For example:


Book1
ABCDEFGHIJK
1CriteriaName
2Account BalanceJane Doe
3LowHighScore
40249990Account BalanceDischarge DateDenial DateLast User Work DateTotal
5250004999959Value571233211246
6500009999969Score6978047194
710000050000079
850000099999999989
9
10Discharge Date
11LowHighScore
1201200
1312118068
1418130048
1530136078
16361999938
17
18Denial Date
19LowHighScore
200290
21306026
2261999936
23
24Last User Work Date
25LowHighScore
260440
2745999947
Sheet3
Cell Formulas
RangeFormula
F6=VLOOKUP(F5,$A$4:$C$8,3)
G6=VLOOKUP(G5,$A$12:$C$16,3)
H6=VLOOKUP(H5,$A$20:$C$22,3)
I6=VLOOKUP(I5,$A$26:$C$27,3)
K6=SUM(F6:I6)


But what do you want to do with the scores? Average them, sum them?
 
Upvote 0
I only need to use the SCORE column. I need to calculate the possibility of each category calculating a score, then after analyzing the scoring, I can come to H/M/L. Some scenarioes will have all 4 categories of criteria, some 3, some 2 and some will only calculate 1. Make more sense?
I don't need to get specific within the criteria ranges as in your example, our system will take care of that. I'm trying to "score our system" so to speak. Hope that helps.
 
Last edited:
Upvote 0
I'm afraid I'm more lost than ever. Could you give an example, or work through the process and explain what you have as inputs, and what you want as outputs, and how to calculate them? Be specific.
 
Upvote 0
Ampinz said:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Criteria[/TD]
[TD]Range[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$500,000.00 and $999,999,999.99[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$100,000.00 and $499,999.99[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$50,000.00 and $99,999.99[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$25,000.00 and $49,999.99[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]121 - 180[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]181 - 300[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]301 - 360[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]361 - 9999[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD] Denial Date[/TD]
[TD]30 to 60[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD] Denial Date[/TD]
[TD]61 and 9999[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]Last User Work Date[/TD]
[TD]45 and 9999[/TD]
[TD]47[/TD]
[/TR]
</tbody>[/TABLE]




















Hi Excel Gurus!

Can someone please help me with a formula or set of formulas to calculate the scoring possibilities of the following criteria? There are 4 categories with specific values. From all the scoring possibilities, I will calculate the potential H/M/L ranges.
Click to expand...


Hi,

Try this method using AND, MIN & MAX. I broke down the steps for you. Paste this in a sheet and replicate for the other ranges.


[TABLE="width: 794"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Criteria[/TD]
[TD]Range[/TD]
[TD]Score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$500,000.00 and $999,999,999.99[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD] 500 000[/TD]
[TD] 900 000[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$100,000.00 and $499,999.99[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD] 100 000[/TD]
[TD] 499 999[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$50,000.00 and $99,999.99[/TD]
[TD="align: right"]69[/TD]
[TD][/TD]
[TD] 50 000[/TD]
[TD] 99 999[/TD]
[/TR]
[TR]
[TD]Account Balance[/TD]
[TD]$25,000.00 and $49,999.99[/TD]
[TD="align: right"]59[/TD]
[TD][/TD]
[TD] 25 000[/TD]
[TD] 49 999[/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]121 - 180[/TD]
[TD="align: right"]68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]181 - 300[/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]301 - 360[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Discharge Date[/TD]
[TD]361 - 9999[/TD]
[TD="align: right"]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Denial Date[/TD]
[TD]30 to 60[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Denial Date[/TD]
[TD]61 and 9999[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last User Work Date[/TD]
[TD]45 and 9999[/TD]
[TD="align: right"]47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]256322[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]256322[/TD]
[TD="colspan: 4"]=+RANDBETWEEN(E5,F2)[/TD]
[/TR]
[TR]
[TD]$500,000.00 and $999,999,999.99[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 4"]=AND((MIN($B$15,F2)<F2),(MAX($B$15,E2)>E2))*C2[/TD]
[/TR]
[TR]
[TD]$100,000.00 and $499,999.99[/TD]
[TD="align: right"]79[/TD]
[TD="colspan: 4"]=AND((MIN($B$15,F3)<F3),(MAX($B$15,E3)>E3))*C3[/TD]
[/TR]
[TR]
[TD]$50,000.00 and $99,999.99[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 4"]=AND((MIN($B$15,F4)<F4),(MAX($B$15,E4)>E4))*C4[/TD]
[/TR]
[TR]
[TD]$25,000.00 and $49,999.99[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 4"]=(MIN($B$15,F5)<F5)*C5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Final[/TD]
[TD="align: right"]79[/TD]
[TD="colspan: 4"]=((MIN($B$15,F6)<F6)*C6)+(AND((MIN($B$15,F4)<F4),(MAX($B$15,E4)>E4))*C4)+(AND((MIN($B$15,F3)<F3),(MAX($B$15,E3)>E3))*C3)+(AND((MIN($B$15,F2)<F2),(MAX($B$15,E2)>E2))*C2)[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Best,
Mat
 
Upvote 0
Hi! Thanks for your response. Let me clarify further. One "Account" can have an Account Balance with a Range of $100,000 and it will score a 79 for that criteria, it will also score for Discharge Date in the range of 181 and score a 48, it can also score with a Last User Work Date for 47 . The total "SCORE" is 174. I need ALL The possible score value for possible scenarios an account might "hit". Then I can take that score and I can come up with the H/M/L ranges. Example 2: Critera scores of 89 + 68 +26 + 47 = 230. Example 3: Criteria Scores of 26+47 = 73. Using these 3 examples I have total scores of 174, 230, and 73. This will give me H=230, M=174, L=73. Make more sense?
 
Upvote 0
Ampinz said:
Hi! Thanks for your response. Let me clarify further. One "Account" can have an Account Balance with a Range of $100,000 and it will score a 79 for that criteria, it will also score for Discharge Date in the range of 181 and score a 48, it can also score with a Last User Work Date for 47 . The total "SCORE" is 174. I need ALL The possible score value for possible scenarios an account might "hit". Then I can take that score and I can come up with the H/M/L ranges. Example 2: Critera scores of 89 + 68 +26 + 47 = 230. Example 3: Criteria Scores of 26+47 = 73. Using these 3 examples I have total scores of 174, 230, and 73. This will give me H=230, M=174, L=73. Make more sense?
Click to expand...

That's ok, Ampiz!

You have 4 Criteria. So just repicate the formula I gave you and change the ranges to the other variables, namely, Discharge Date, Denial Date etc...

Then sum them together to get your SCORE.

Then, you can easily get your H / M / L values. Let me know if you need help with these formulas.
 
Upvote 0
Using this macro:

Code: 
Sub test2()
Dim op() As Variant


    a1 = Array(0, 59, 69, 79, 89)
    a2 = Array(0, 68, 48, 78, 38)
    a3 = Array(0, 26, 36)
    a4 = Array(0, 47)
    
    ub = 5 * 5 * 3 * 2
    ReDim op(1 To ub, 1 To 1)
    For b1 = 0 To UBound(a1)
        For b2 = 0 To UBound(a2)
            For b3 = 0 To UBound(a3)
                For b4 = 0 To UBound(a4)
                    r = r + 1
                    op(r, 1) = a1(b1) + a2(b2) + a3(b3) + a4(b4)
                Next b4
            Next b3
        Next b2
    Next b1
    
    Range("E1").Resize(ub).Value = op
    
    With ActiveSheet.Sort
        .SetRange Range("E:E")
        .Header = xlNo
        .Apply
    End With
    
    ActiveSheet.Range("E:E").RemoveDuplicates Columns:=1, Header:=xlNo
                    
End Sub
I generated the list of all values you can get with the ranges you specified. Here they are:


Book1
EFG
10111161
226114162
336115163
438116164
547117167
648121170
759123172
864125173
968126174
1069127180
1173131183
1274132184
1378133190
1479136193
1583137194
1684141200
1785142203
1889143204
1994144210
2095147214
2197151220
22104152230
23105153240
24106154250
25107157
Sheet3
 
Upvote 0
Eric W said:
Using this macro:

Code: 
Sub test2()
Dim op() As Variant


    a1 = Array(0, 59, 69, 79, 89)
    a2 = Array(0, 68, 48, 78, 38)
    a3 = Array(0, 26, 36)
    a4 = Array(0, 47)
    
    ub = 5 * 5 * 3 * 2
    ReDim op(1 To ub, 1 To 1)
    For b1 = 0 To UBound(a1)
        For b2 = 0 To UBound(a2)
            For b3 = 0 To UBound(a3)
                For b4 = 0 To UBound(a4)
                    r = r + 1
                    op(r, 1) = a1(b1) + a2(b2) + a3(b3) + a4(b4)
                Next b4
            Next b3
        Next b2
    Next b1
    
    Range("E1").Resize(ub).Value = op
    
    With ActiveSheet.Sort
        .SetRange Range("E:E")
        .Header = xlNo
        .Apply
    End With
    
    ActiveSheet.Range("E:E").RemoveDuplicates Columns:=1, Header:=xlNo
                    
End Sub
I generated the list of all values you can get with the ranges you specified. Here they are:

EFG

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

[TD="align: center"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]162[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]163[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]164[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]167[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]170[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]172[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]173[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]174[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]180[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]183[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]184[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]190[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]193[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]194[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]203[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]204[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]210[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]214[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]220[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]230[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]240[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​
Click to expand...

Epic !
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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