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

[Ampinz]

Criteria	Range	Score
Account Balance	$500,000.00 and $999,999,999.99	89
Account Balance	$100,000.00 and $499,999.99	79
Account Balance	$50,000.00 and $99,999.99	69
Account Balance	$25,000.00 and $49,999.99	59
Discharge Date	121 - 180	68
Discharge Date	181 - 300	48
Discharge Date	301 - 360	78
Discharge Date	361 - 9999	38
Denial Date	30 to 60	26
Denial Date	61 and 9999	36
Last User Work Date	45 and 9999	47

<tbody>
</tbody>

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.

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Criteria					Name
2	Account Balance					Jane Doe
3	Low	High	Score
4	0	24999	0			Account Balance	Discharge Date	Denial Date	Last User Work Date		Total
5	25000	49999	59		Value	57123	321	12	46
6	50000	99999	69		Score	69	78	0	47		194
7	100000	500000	79
8	500000	999999999	89
9
10	Discharge Date
11	Low	High	Score
12	0	120	0
13	121	180	68
14	181	300	48
15	301	360	78
16	361	9999	38
17
18	Denial Date
19	Low	High	Score
20	0	29	0
21	30	60	26
22	61	9999	36
23
24	Last User Work Date
25	Low	High	Score
26	0	44	0
27	45	9999	47
Sheet3

Cell Formulas
Range	Formula
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?

 

[Ampinz]

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.

 

[Eric W]

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.

 

[Mathexcel]

Criteria	Range	Score
Account Balance	$500,000.00 and $999,999,999.99	89
Account Balance	$100,000.00 and $499,999.99	79
Account Balance	$50,000.00 and $99,999.99	69
Account Balance	$25,000.00 and $49,999.99	59
Discharge Date	121 - 180	68
Discharge Date	181 - 300	48
Discharge Date	301 - 360	78
Discharge Date	361 - 9999	38
Denial Date	30 to 60	26
Denial Date	61 and 9999	36
Last User Work Date	45 and 9999	47

<tbody>
</tbody>

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.

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.

Criteria	Range	Score
Account Balance	$500,000.00 and $999,999,999.99	89		500 000	900 000
Account Balance	$100,000.00 and $499,999.99	79		100 000	499 999
Account Balance	$50,000.00 and $99,999.99	69		50 000	99 999
Account Balance	$25,000.00 and $49,999.99	59		25 000	49 999
Discharge Date	121 - 180	68
Discharge Date	181 - 300	48
Discharge Date	301 - 360	78
Discharge Date	361 - 9999	38
Denial Date	30 to 60	26
Denial Date	61 and 9999	36
Last User Work Date	45 and 9999	47
	256322
	256322	=+RANDBETWEEN(E5,F2)
$500,000.00 and $999,999,999.99	0	=AND((MIN($B$15,F2)<F2),(MAX($B$15,E2)>E2))*C2
$100,000.00 and $499,999.99	79	=AND((MIN($B$15,F3)<F3),(MAX($B$15,E3)>E3))*C3
$50,000.00 and $99,999.99	0	=AND((MIN($B$15,F4)<F4),(MAX($B$15,E4)>E4))*C4
$25,000.00 and $49,999.99	0	=(MIN($B$15,F5)<F5)*C5
Final	79	=((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)

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Best,
Mat

 

[Ampinz]

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?

 

[Mathexcel]

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?

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.

 

[Mathexcel]

Did you manage?

 

[Eric W]

Using this macro:

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
	E	F	G
1	0	111	161
2	26	114	162
3	36	115	163
4	38	116	164
5	47	117	167
6	48	121	170
7	59	123	172
8	64	125	173
9	68	126	174
10	69	127	180
11	73	131	183
12	74	132	184
13	78	133	190
14	79	136	193
15	83	137	194
16	84	141	200
17	85	142	203
18	89	143	204
19	94	144	210
20	95	147	214
21	97	151	220
22	104	152	230
23	105	153	240
24	106	154	250
25	107	157
Sheet3

 

[Mathexcel]

Using this macro:

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:

	E	F	G
1	0	111	161
2	26	114	162
3	36	115	163
4	38	116	164
5	47	117	167
6	48	121	170
7	59	123	172
8	64	125	173
9	68	126	174
10	69	127	180
11	73	131	183
12	74	132	184
13	78	133	190
14	79	136	193
15	83	137	194
16	84	141	200
17	85	142	203
18	89	143	204
19	94	144	210
20	95	147	214
21	97	151	220
22	104	152	230
23	105	153	240
24	106	154	250
25	107	157

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

Sheet3​

Epic !