I was able to figure it out last night, and here is how:
Images and formulas
Cell D2 and D3 define the number of dice and the number of sides for each die:
Count: 100d. Sides: d20
Cells A6:Bn list the result of the die and the number of results for each face:
Die, value and count for each face of the die rolls.
In cells A3:B3 I list the low and high with count in parentheses:
Low and high: 16(1)/6(11)
In order to write this function, I had to result in a quite horrible nested concatenate, but it got the job done:
=KJED.SAMAN(INDEKS(A6:A10005;
SAMANLIKNA(N.MINST(B6:B10005;
TEL.VISS(A6:A10005;">="&D3));B6:B10005;0));
"(";N.MINST(B6:B10005;
TEL.VISS(A6:A10005;">="&D3));")/";INDEKS(A6:A10005;
SAMANLIKNA(N.STØRST(B6:B10005;1);B6:B10005;0));
"(";MAKS(B6:B10005);")")
=CONCATENATE(INDEX(A6:A10005;
MATCH(SMALL(B6:B10005;
COUNT.IF(A6:10005;">="&D3));B6:B10005;0));
"(";SMALL(B6:B10005;
COUNT.IF(A6:A10005;">="&D3);")/";INDEX(A6:A10005;
SAMANLIKNA(LARGE(B6:B10005;1);B6:B10005;0));
"(";MAX(B6:B10005);")")
Explanation
What this does is in practice a left LOOKUP.
How MATCH works
`=MATCH(search criteria; search range; type[0,1])
By looking in a source cell, e.g. B1 containing the value 101, then looking in a data range, e.g. a list of products, the value returns which number in the list the data is found. Example:
A B
1 Enter product type: 902
2
3 Jackets 900
4 Trousers 901
5 Shoes 902
For this example, MATCH would return the value 3 if looking in the area B3:B5, as ‘102’ is the third row matching the product number entered in B1.
Combining this with INDEX
By looking at a specified source, we can collect the relevant data. We have already collected the number 4. The structure is INDEX(reference;row;column;area). Let us improve on our data example:
|
A |
B |
C |
D |
E |
F |
| 1 |
Product type: |
902 |
|
|
|
|
| 2 |
|
|
|
|
|
|
| 3 |
Prod. no. |
Item |
|
Brand |
Item |
Prod. no. |
| 4 |
900 |
|
|
Nykë |
Jackets |
900 |
| 5 |
901 |
|
|
Leevys |
Trousers |
901 |
| 6 |
902 |
|
|
Nykë |
Shoes |
902 |
| 7 |
903 |
|
|
Benzin |
Hats |
903 |
| 8 |
904 |
|
|
Leevys |
Scarfs |
904 |
|
A |
B |
C |
D |
E |
F |
In cell B4, we can enter the function =INDEX(E$4:E$8;MATCH(B4;F$4:F$8;0)), and then paste that down the range B4:B8. What this will do, is look in the range of items in the range E4:E8, it will then for its row go to the MATCH formula, in which we first get the data entered by the employee in B4 (item no. 902), then look in the range F4:F8 to find that number, and finally return this to the index, which lists the expected ‘Shoes’ in cell B4.
SMALL and LARGE
Finally, there was the issue of getting the correct small number. MIN didn’t work, as that included zeros. I needed the formula to look in the entire range, as the number of faces on the die could increase. (In Hackmaster, we even use a d10000 for critical hits.) Let us have a look at the formula again:
SMALL(B6:B10005;COUNT.IF(A6:10005;">="&D3))
What this does, is look in the range B6:B10005, which is where the number of hits for each result is listed, then use a COUNT.IF to find the number in the left column where the possible die faces are listed, and then finally collecting the maximum number from the cell (D3) where the number of die faces are stated. The reason the criterium is written the way it is (">="&D3), is that the criterium when not a number must be expressed within quotes. The expression is joined with the cell reference via the ampersand.
