Parsing and evaluating formulas using "VALUE, "MID", "FIND", nested "IFERROR", and "OFFSET"

[Golden Age]

Hello, everyone here. I hope you're all well. I'm posting this thread looking for help with a few formulas that I've been struggling with and making minimal progress. The formulas I'm needing help with deal with dice rolling and randbetween stuff for a pen and paper role-playing game. Here is the HTML of my worksheet. It's in a Macro-Enabled Workbook using Excel 2016.

	K	L	M	N	O	P	Q	DI
IF(AND($E$5=1,$E$18=1),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceN,2,FALSE),IF(AND($E$5=1,$E$18=2),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceN,3,FALSE),IF(AND($E$5=2,$E$18=1),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceR,2,FALSE),IF(AND($E$5=2,$E$18=2),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceR,3,FALSE),IF(AND($E$5=3,$E$18=1),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceHU,2,FALSE),IF(AND($E$5=3,$E$18=2),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceHU,3,FALSE),IF(AND($E$5=4,$E$18=1),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceBtS,2,FALSE),IF(AND($E$5=4,$E$18=2),VLOOKUP(INDEX(ListRace,$E$8,1),TableRaceBtS,3,FALSE),IF(AND($E$5=5,$E$18=1),VLOOKUP(INDEX(ListRace,$E$8,1),TableRacePF,2,FALSE),IF(AND($E$5=5,$E$18=2),VLOOKUP(INDEX(ListRace,$E$8,1),TableRacePF,3,FALSE)))))))))))	K24	IF($L24=0,0,(RANDBETWEEN(RANDBETWEEN(1,1),(VALUE(MID($L24,(FIND("d",$L24))+1,IFERROR((IFERROR(FIND("+",$L24),FIND("-",$L24)))-(FIND("d",$L24))-1,LEN($L24)-(FIND("d",$L24)))))))))	IF($L24=0,0,(RANDBETWEEN(RANDBETWEEN(1,1),(VALUE(MID($L24,(FIND("d",$L24))+1,IFERROR((IFERROR(FIND("+",$L24),FIND("-",$L24)))-(FIND("d",$L24))-1,LEN($L24)-(FIND("d",$L24)))))))))	IF(ISERROR(SUM(OFFSET($M24,0,0,1,(IF((FIND("d",$L24))>1,VALUE(LEFT($L24,(FIND("d",$L24))-1)),1))),(IFERROR(VALUE(RIGHT($L24,LEN($L24)-(IFERROR(FIND("+",$L24),FIND("-",$L24)))+1)),0)))),0,SUM(OFFSET($M24,0,0,1,(IF((FIND("d",$L24))>1,VALUE(LEFT($L24,(FIND("d",$L24))-1)),1))),(IFERROR(VALUE(RIGHT($L24,LEN($L24)-(IFERROR(FIND("+",$L24),FIND("-",$L24)))+1)),0))))
3d12+5	3d12+5

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

[TD="align: center"]24[/TD]

[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]#VALUE![/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]34[/TD]

</tbody>

Formulas​

Here is the worksheet called Formulas. In row 24, I have the formulas shown minus the "=" sign at the beginning of the formula to show what's there. These were found on ExcelForum.com under the thread titled

A formula to read a dice code for board games and do the random rolling


I would have asked for further help on that site but I already had an account here so this is where I've come.

In Cell K24, is a function using VLOOKUP and Named Ranges to find the dice to roll based on the type of creature you are creating. The location of the data the VLOOKUP is going to is on a separate worksheet called Data. At the moment it's just a list of several columns of data but in the near future I will be turning that into a proper Table.
The values that this VLOOKUP might look something like 1d4+16, or 3d12-4, or 3d6 as some examples. The "d" indicates the word "die/dice". So in the case of the first example, it is asking for a roll of 1 die with 4 sides. The "+" and "-" portion is dealt with in Cell DI24, (which I'll get to).

In Cell L24, is a function that reads =$k$24. I know this is a redundant duplication of the VLOOKUP and I might eliminate this in due course but for now, I'm going to leave it thus.

In Cell M24, is the first function I'm struggling to alter. At the moment it is able to parse out the value of the digits that show between the "d" and the "+" or "-". Putting together the RANDBETWEEN at the start of the formula, and the VALUE that follows, the formula simulate the roll of 1 of the dice indicated and again does not deal with the bonus just yet.

Cells M24 to DH24 (some of which are hidden to save space have the identical formula as cell M24) but they are random integers.

Finally, Cell DI24, uses the formula shown, to SUM the correct number of columns starting at cell M24 that susses out the number of dice asked for. The formula also deals with the appropriate bonuses ("+" or "-") if required to do so. Sometimes there are no "+" or "-". This is the other formula that I need help with.

I am humbly asking for help to alter these formulas to deal with the following possible lookup returns that might show up with the VLOOKUP back in Cell K24.
Sometimes that lookup finds a number, or rather a constant with no "d" or "+"/"-" of any sort, such as 12.
Sometimes the lookup finds a text string that includes a multiplication factor, that might also have a "+"/"-" component,like 4d4x10 or 2d8x20+5. At the moment the existing formulas cannot deal with constants or multiplication factors.

I still want the Cells M24 to DH24 to only deal with the rolls of an individual dice or regurgitate a constant if that comes up from the VLOOKUP. Cell DI24 is where I want the OFFSET to take care of the multiplication, and/or "+"/"-" following standard BEDMAS rules. That is to say, for 3d6x10+6 (for example), Cell DI24 is going to SUM 3 columns starting at M (M, N, O), (which will be random numbers between 1 and 6), then multiply that SUM by "10", and finally account for the "+" or"-".

Reading back over my thread, it makes as much sense to me as it could. But, for all you all, it might be as confusing as it gets. I still have hope that some here might have a lash at it and help me out. I will answer any questions to clarify what I'm hoping for if I've left you perplexed. Thank you, ever so much in advance.

 

[Andrew Fergus]

Hello

Those look complex but I think I understand what you are trying to achieve.

Currently you select a character and you have a lookup table that determines what dice to throw - do you have that part working ok?

Assuming you do, and the lookup returns say 3d6x10+6 - you now want to roll three 6-sided dice to get 3 random values and then apply BEDMAS to change the raw dice score to something else. Is that correct so far?

Assuming that is the case, then I'm thinking that you should "unpick" the dice rolling and store those values on the data sheet (e.g. the 3, the 6, and the x10+6). Then you take those factors and apply them to your formulas in cells M24:DH24. I also think that we should force the values for the dice rolls not required to zero, so that the formula in cell DI24 is a simple sum of M24:DH24.

To make life easier, I recommend the math adjusters be presented in the sequence / * + -, that will make life easier rather than applying BEDMAS to a random collection of modifiers. I'm guessing the modifiers will be quite simple, but if you want something more complex then we will probably need to use a custom VBA function to evaluate the function (unfortunately there is no native evaluate function - there are other methods using named ranges but they also have issues with refreshing).

Something like this? Nnotice I have used named ranges in the formula but column A tells you those, notice also the array formula in cell B9.

Data

A

B

C

D

E

F

G

H

I

J

K

L

TOTAL

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88.8px;"><col style="width:100.8px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Named Range[/TD]
[TD="align: center"]Value[/TD]
[TD="align: left"]Explanation[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]raw result->[/TD]
[TD="align: center"]3d6*5+12-5[/TD]
[TD="align: left"]value returned by the lookup function[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]result ->[/TD]
[TD="align: center"]3d6*5+12-5[/TD]
[TD="align: left"]dice roll required with modifiers[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]dpos ->[/TD]
[TD="align: center"]2[/TD]
[TD="align: left"]position of "d"[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]numdice ->[/TD]
[TD="align: center"]3[/TD]
[TD="align: left"]number of dice to throw[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]mpos->[/TD]
[TD="align: center"]4[/TD]
[TD="align: left"]position of math function[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]dicesides->[/TD]
[TD="align: center"]6[/TD]
[TD="align: left"]number of sides on each dice[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]mathfunc->[/TD]
[TD="align: center"]*5+12-5[/TD]
[TD="align: left"]raw dice modifier[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]mathlen->[/TD]
[TD="align: center"]7[/TD]
[TD="align: left"]overall math description length[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: left"]math function[/TD]
[TD="align: center"]/[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]+[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]end+1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: left"]position[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: left"]first[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="align: left"]last[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="align: left"]value[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]Dice:[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: right"]rawscore->[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: right"]adjval->[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]61[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B4 =SUBSTITUTE(rawresult," ","") B7 =FIND("d",result) B8 =VALUE(LEFT(result,dpos-1)) B9 {=MIN(IFERROR(FIND({"/","*","+","-"},result),"z"))} B10 =VALUE(MID(result,dpos+1,IF(mpos>0,mpos-dpos-1,LEN(result)-dpos))) B11 =RIGHT(result,LEN(result)-B9+1) B12 =LEN(mathfunc) C15 =IFERROR(SEARCH("/",mathfunc),0) D15 =IFERROR(SEARCH("~*",mathfunc),0) E15 =IFERROR(SEARCH("+",mathfunc),0) F15 =IFERROR(SEARCH("-",mathfunc),0) G15 =B12+1 C16 =IF(C15=0,0,C15+1) D16 =IF(D15=0,0,D15+1) E16 =IF(E15=0,0,E15+1) F16 =IF(F15=0,0,F15+1) C17 {=IF(C15=0,0,MIN(IF($C$15:$G$15>C15,$C$15:$G$15,$G$15))-1)} D17 {=IF(D15=0,0,MIN(IF($C$15:$G$15>D15,$C$15:$G$15,$G$15))-1)} E17 {=IF(E15=0,0,MIN(IF($C$15:$G$15>E15,$C$15:$G$15,$G$15))-1)} F17 {=IF(F15=0,0,MIN(IF($C$15:$G$15>F15,$C$15:$G$15,$G$15))-1)} C18 =IF(C15=0,1,VALUE(MID(mathfunc,C16,C17-C16+1))) D18 =IF(D15=0,1,VALUE(MID(mathfunc,D16,D17-D16+1))) E18 =IF(E15=0,0,VALUE(MID(mathfunc,E16,E17-E16+1))) F18 =IF(F15=0,0,VALUE(MID(mathfunc,F16,F17-F16+1))) C21 =B21+1 D21 =C21+1 E21 =D21+1 F21 =E21+1 G21 =F21+1 H21 =G21+1 I21 =H21+1 J21 =I21+1 K21 =J21+1 B22 =IF(B21<=numdice,RANDBETWEEN(1,dicesides),0) C22 =IF(C21<=numdice,RANDBETWEEN(1,dicesides),0) D22 =IF(D21<=numdice,RANDBETWEEN(1,dicesides),0) E22 =IF(E21<=numdice,RANDBETWEEN(1,dicesides),0) F22 =IF(F21<=numdice,RANDBETWEEN(1,dicesides),0) G22 =IF(G21<=numdice,RANDBETWEEN(1,dicesides),0) H22 =IF(H21<=numdice,RANDBETWEEN(1,dicesides),0) I22 =IF(I21<=numdice,RANDBETWEEN(1,dicesides),0) J22 =IF(J21<=numdice,RANDBETWEEN(1,dicesides),0) K22 =IF(K21<=numdice,RANDBETWEEN(1,dicesides),0) B23 =IF(B22>0,B22/$C$18*$D$18+$E$18-$F$18,0) C23 =IF(C22>0,C22/$C$18*$D$18+$E$18-$F$18,0) D23 =IF(D22>0,D22/$C$18*$D$18+$E$18-$F$18,0) E23 =IF(E22>0,E22/$C$18*$D$18+$E$18-$F$18,0) F23 =IF(F22>0,F22/$C$18*$D$18+$E$18-$F$18,0) G23 =IF(G22>0,G22/$C$18*$D$18+$E$18-$F$18,0) H23 =IF(H22>0,H22/$C$18*$D$18+$E$18-$F$18,0) I23 =IF(I22>0,I22/$C$18*$D$18+$E$18-$F$18,0) J23 =IF(J22>0,J22/$C$18*$D$18+$E$18-$F$18,0) K23 =IF(K22>0,K22/$C$18*$D$18+$E$18-$F$18,0) L23 =SUM(B23:K23) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Golden Age]

The "Reply" feature is not working on my PC. This is the third reply I've written but the button "Post Quick Reply" kicks me off the site without posting what I've written. Maybe it'll work this time.

Andrew, your brilliant solution worked like a charm. I had to change the layout to horizontal rather than vertical so there were some tweaking of cell formulas to accomplish that but it works. I'm so happy!!! Thank you very much for taking the time to help me. You're amazing. This thread is solved and can be closed.