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

Ampinz

New Member
Joined
Oct 13, 2016
Messages
10
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Criteria</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Account Balance</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jane Doe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Low</td><td style=";">High</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">24999</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Account Balance</td><td style=";">Discharge Date</td><td style=";">Denial Date</td><td style=";">Last User Work Date</td><td style="text-align: right;;"></td><td style=";">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">25000</td><td style="text-align: right;;">49999</td><td style="text-align: right;;">59</td><td style="text-align: right;;"></td><td style=";">Value</td><td style="text-align: right;;">57123</td><td style="text-align: right;;">321</td><td style="text-align: right;;">12</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">50000</td><td style="text-align: right;;">99999</td><td style="text-align: right;;">69</td><td style="text-align: right;;"></td><td style=";">Score</td><td style="text-align: right;;">69</td><td style="text-align: right;;">78</td><td style="text-align: right;;">0</td><td style="text-align: right;;">47</td><td style="text-align: right;;"></td><td style="text-align: right;;">194</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">100000</td><td style="text-align: right;;">500000</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">500000</td><td style="text-align: right;;">999999999</td><td style="text-align: right;;">89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Discharge Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Low</td><td style=";">High</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">0</td><td style="text-align: right;;">120</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">121</td><td style="text-align: right;;">180</td><td style="text-align: right;;">68</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">181</td><td style="text-align: right;;">300</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">301</td><td style="text-align: right;;">360</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">361</td><td style="text-align: right;;">9999</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Denial Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Low</td><td style=";">High</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">29</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">30</td><td style="text-align: right;;">60</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">61</td><td style="text-align: right;;">9999</td><td style="text-align: right;;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Last User Work Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">Low</td><td style=";">High</td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;">0</td><td style="text-align: right;;">44</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;">45</td><td style="text-align: right;;">9999</td><td style="text-align: right;;">47</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">F5,$A$4:$C$8,3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G5,$A$12:$C$16,3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">H5,$A$20:$C$22,3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">I5,$A$26:$C$27,3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K6</th><td style="text-align:left">=SUM(<font color="Blue">F6:I6</font>)</td></tr></tbody></table></td></tr></table><br />

But what do you want to do with the scores? Average them, sum them?
 

Ampinz

New Member
Joined
Oct 13, 2016
Messages
10
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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
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

New Member
Joined
Jun 22, 2017
Messages
36
CriteriaRangeScore
Account Balance$500,000.00 and $999,999,999.9989
Account Balance$100,000.00 and $499,999.9979
Account Balance$50,000.00 and $99,999.9969
Account Balance$25,000.00 and $49,999.9959
Discharge Date121 - 18068
Discharge Date181 - 30048
Discharge Date301 - 36078
Discharge Date361 - 999938
Denial Date30 to 6026
Denial Date61 and 999936
Last User Work Date45 and 999947

<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.


CriteriaRangeScore
Account Balance$500,000.00 and $999,999,999.9989 500 000 900 000
Account Balance$100,000.00 and $499,999.9979 100 000 499 999
Account Balance$50,000.00 and $99,999.9969 50 000 99 999
Account Balance$25,000.00 and $49,999.9959 25 000 49 999
Discharge Date121 - 18068
Discharge Date181 - 30048
Discharge Date301 - 36078
Discharge Date361 - 999938
Denial Date30 to 6026
Denial Date61 and 999936
Last User Work Date45 and 999947
256322
256322=+RANDBETWEEN(E5,F2)
$500,000.00 and $999,999,999.990=AND((MIN($B$15,F2)<F2),(MAX($B$15,E2)>E2))*C2
$100,000.00 and $499,999.9979=AND((MIN($B$15,F3)<F3),(MAX($B$15,E3)>E3))*C3
$50,000.00 and $99,999.990=AND((MIN($B$15,F4)<F4),(MAX($B$15,E4)>E4))*C4
$25,000.00 and $49,999.990=(MIN($B$15,F5)<F5)*C5
Final79=((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

New Member
Joined
Oct 13, 2016
Messages
10
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

New Member
Joined
Jun 22, 2017
Messages
36
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">111</td><td style="text-align: right;;">161</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">114</td><td style="text-align: right;;">162</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">36</td><td style="text-align: right;;">115</td><td style="text-align: right;;">163</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">38</td><td style="text-align: right;;">116</td><td style="text-align: right;;">164</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">47</td><td style="text-align: right;;">117</td><td style="text-align: right;;">167</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">48</td><td style="text-align: right;;">121</td><td style="text-align: right;;">170</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">59</td><td style="text-align: right;;">123</td><td style="text-align: right;;">172</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">64</td><td style="text-align: right;;">125</td><td style="text-align: right;;">173</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">68</td><td style="text-align: right;;">126</td><td style="text-align: right;;">174</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">69</td><td style="text-align: right;;">127</td><td style="text-align: right;;">180</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">73</td><td style="text-align: right;;">131</td><td style="text-align: right;;">183</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">74</td><td style="text-align: right;;">132</td><td style="text-align: right;;">184</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">78</td><td style="text-align: right;;">133</td><td style="text-align: right;;">190</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">79</td><td style="text-align: right;;">136</td><td style="text-align: right;;">193</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">83</td><td style="text-align: right;;">137</td><td style="text-align: right;;">194</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">84</td><td style="text-align: right;;">141</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">85</td><td style="text-align: right;;">142</td><td style="text-align: right;;">203</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">89</td><td style="text-align: right;;">143</td><td style="text-align: right;;">204</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">94</td><td style="text-align: right;;">144</td><td style="text-align: right;;">210</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">95</td><td style="text-align: right;;">147</td><td style="text-align: right;;">214</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">97</td><td style="text-align: right;;">151</td><td style="text-align: right;;">220</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">104</td><td style="text-align: right;;">152</td><td style="text-align: right;;">230</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">105</td><td style="text-align: right;;">153</td><td style="text-align: right;;">240</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">106</td><td style="text-align: right;;">154</td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">107</td><td style="text-align: right;;">157</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br />
 

Mathexcel

New Member
Joined
Jun 22, 2017
Messages
36
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
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

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

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top