Google Sheets Combining Like Dice Rolls (While removing Integers)

Question

My apologies for asking an incomplete question previously.

This is what I'm trying to accomplish.

I'm building a TTRPG sheet that automatically combines dice rolls, bonuses (additive) and penalties (subtractive) from a variety of sources. All of this data is expressed as either dice notation (D4, D6, D8, D10, D12, D20, and D100) or an Integer (1, 2, 4, 6), or both (combined). These also include negative values (-1D4, -1D6, -2, etc.). The goal isn't to generate the random numbers, but instead combine like dice together for the player to roll manually (I tried the automatic random numbers... Players were not happy about it.)

So, the goal is to combine likes, so something like: "1D6+1D6" would become "2D6". However, because penalties could outweigh the bonus, you can't combine "1D6+1D6+-1D6" into "1D6". (Since each of the rolls could be a different number, such as "6+6-1" compared to "1+1-6").

Additionally, Integers (2, 4, 6, 8, etc.) are by necessity handled in a different part of the sheet, so the goal is to strip the integers out from the output. (The reason for stripping them out has nothing to do with formula complexity, but other game factors that require it to be viewed separately.)

Here are some examples of typical inputs and expected outputs:

1D6+1D4+1D8+-1D4+1D6+2 = 1D4+-1D4+2D6+1D8 (Notice the integer is removed)

1D6+2+0+1+8 = 1D6 (Because all integers have been stripped out)

1D20+-1D4+2D6+0+1D6+-1D6 = +-1D4+3D6+-1D6+1D20

(Yes, negative numbers will have the "+-" in front of them).

My original "mostly working" formula was 2 solid pages long when copied/pasted into MS Word. This formula will be repeated THOUSANDS of times, so smaller/faster makes a huge difference in the overall scheme of things. Two previous amazing Spreadsheet Wizards (Player0 and TheMaster) gave great answers, but I failed to disclose the integer as a part of the overall process.

The table below shows the formula that works for the first example, but not the second (gives "2D" in the output).

TheMaster · Accepted Answer · 2022-09-20T16:10:35.710

1

For original explanation, see Google Sheets Formula for combining dice rolls

After the first split by +, check if the result is a TEXT and if not, FILTER it out:

=JOIN("+",BYROW(QUERY(REDUCE({"",""},SEQUENCE(2),LAMBDA(a,c,{a;QUERY({ARRAYFORMULA(SPLIT(TRANSPOSE(LAMBDA(ar,FILTER(ar,ISTEXT(ar)))(SPLIT(B1,"+"))),"D"))}," select sum(Col1),Col2 where Col1"&IF(c=1,">","<")&"0 group by Col2 label sum(Col1) ''")})),"order by Col2"),LAMBDA(r,JOIN("D",r))))

For no negative values, add a empty array {"",""} for NA:

=JOIN("+",BYROW(QUERY(REDUCE({"",""},SEQUENCE(2),LAMBDA(a,c,{a;IFNA(QUERY({ARRAYFORMULA(SPLIT(TRANSPOSE(LAMBDA(ar,FILTER(ar,ISTEXT(ar)))(SPLIT(B1,"+"))),"D"))},"select sum(Col1),Col2 where Col1"&IF(c=1,">","<")&"0 group by Col2 label sum(Col1) ''"),{"",""})})),"where Col1 is not null order by Col2"),LAMBDA(r,JOIN("D",r))))

edited Sep 20 '22 at 16:10

answered Sep 20 '22 at 15:31

TheMaster

45,448
6
62
85

If you wouldn't mind, what does "ar" mean in this context? For example: TEXT(ar), LAMBDA(ar,. I haven't been able to define that in my mind. Also, THANK YOU SO MUCH! – Rogue Scientist Allen Sep 20 '22 at 15:57
@RogueScientistAllen Just a custom name. `ar` for `array`. You could just use `a` or `row` or `rng` or any other irrelevant character `_uttutt`, if you want. The array from `SPLIT(rng,"+")` is assigned to that `ar`. – TheMaster Sep 20 '22 at 15:59
In application, I tried it with this notation: 3D6+0+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6+1D6 and it gave the same error. Is there something different about this that doesn't work? All parts of it are positive (no negative dice), and an integer. – Rogue Scientist Allen Sep 20 '22 at 16:00
@RogueScientistAllen Yes, that'll give `NA` and upset the virtual array structure making it unequal. Fixed it in post. – TheMaster Sep 20 '22 at 16:13
Plugged it into my "Master Calculator" and it works!!! With about a quarter million calculations going on in this sheet, having this shortened (from my original 2 page long formula), will save a LOT of horsepower later on!!! Thank you again!!! – Rogue Scientist Allen Sep 20 '22 at 16:15

score 1 · Answer 2 · answered Sep 20 '22 at 18:13

try:

=INDEX(REGEXREPLACE(TEXTJOIN("+", 1, FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(IFERROR(IFNA(TRANSPOSE({
 REGEXEXTRACT(SPLIT(C5, "+"), "^\d+")*1;  REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "^-\d+")*1; REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "D(\d+)")*1}), 0)), 
 "select sum(Col1),Col2,'+',sum(Col3),Col4,Col5 
  where Col2 is not null group by Col2,Col4,Col5 order by Col5"), 
 "select Col1,Col2,Col3,Col4,Col5 offset 1", )),,9^9))), " |\+ 0 D\d+", ))

Google Sheets Combining Like Dice Rolls (While removing Integers)

2 Answers2