Golden Age
New Member
- Joined
- Nov 1, 2006
- Messages
- 26
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.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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.
| K | L | M | N | O | P | Q | DI | |
|---|---|---|---|---|---|---|---|---|
| 23 | ||||||||
| 24 | 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))))))))) | #VALUE! | #VALUE! | #VALUE! | 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)))) |
| 25 | 3d12+5 | 3d12+5 | 7 | 11 | 11 | 10 | 5 | 34 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.
This thread is solved and can be closed.
