SOLVED

Please help! Tangled up in a nested IF formula for DnD!

Copper Contributor

HI everyone, first time posting - I've come here for some much needed help!  I've spent days creating a ginormous excel workbook that randomly generates everything I need to run my Dungeons and Dragons game.  I've used this nested IF formula in the workbook already and it's worked fine but I'm lost as to why it is getting tangled up here.

 

I appreciate that it is a lengthy one but I'm just a keen, self-taught amateur so apologies if I'm doing it in a very messy way!...

 

This is my formula (return spaces added to make it easier to read):

 

=@IF('NPC Descriptions'!K7="human",(INDEX(Names!$AW$47:$AW$48,RANDBETWEEN(1,COUNTA(Names!$AW$47:$AW$48)),1)),

 

IF('NPC Descriptions'!K7="dwarf",(INDEX(Names!$AW$32:$AW$33,RANDBETWEEN(1,COUNTA(Names!$AW$32:$AW$33)),1),

 

IF('NPC Descriptions'!K7=”elf”,(INDEX(Names!$AW$35:$AW$36,RANDBETWEEN(1,COUNTA(AND(Names!$AW$35:$AW$36)),1),

 

IF('NPC Descriptions'!K7=”half-elf”,(INDEX(AND(Names!$AW$35:$AW$36, Names!$AW$47:$AW$48),RANDBETWEEN(1,COUNTA(AND(Names!$AW$35:$AW$36, Names!$AW$47:$AW$48))),1),

 

(IF('NPC Descriptions'!K7="halfling",(INDEX(Names!$AW$38:$AW$39,RANDBETWEEN(1,COUNTA(Names!$AW$38:$AW$39)),1),

 

IF('NPC Descriptions'!K7="half-orc",(INDEX(AND(Names!$AX$7:$AX$8,Names!$AW$47:$AW$48),RANDBETWEEN(1,COUNTA(AND(Names!$AX$7:$AX$8,Names!$AW$47:$AW$48))),1),

 

IF('NPC Descriptions'!K7="tiefling",(INDEX(Names!$AW$50:$AW$51,RANDBETWEEN(1,COUNTA(Names!$AW$50:$AW$51)),1),

IF('NPC Descriptions'!K7="dragonborn",

 

(INDEX(Names!$AW$53:$AW$54,RANDBETWEEN(1,COUNTA(Names!$AW$53:$AW$54)),1)),(INDEX(Names!$AW$41:$AW$42,RANDBETWEEN(1,COUNTA(Names!$AW$41:$AW$42)),1))))))))

 

The cells point at a single column containing race-specific names from a sheet full of possible ones (if you're not familiar, that's the 'halfing', 'elf' etc logical tests).  The reason I have the random pick going on in this formula is because I want it to pick 50:50 the randomly selected male or female name.

 

One attached screenshot shows the 'NPC Descriptions' sheet, which randomly selects a race from the race column.  The other screenshot shows the 'Names' sheet, which has a table of a randomly selected male and female name for each race.  The table is where the formula should be getting the names from after checking what race has been generated.  But it's giving me #REF, #VALUE and FALSE results... :(

Please help!

7 Replies
best response confirmed by tickleyfeet29 (Copper Contributor)
Solution
Is there a reason why this has to be a SINGLE formula?
I'd be surprised if there's any Excel manual out there that recommends writing formulas this long.
I KNOW that there are Excel manuals that warn AGAINST writing formulas this long.

It might be fun when it works, but as you've encountered here, formulas this long are opportunities for Orcs and all other kinds of creatures to get in and make mischief.

A suggestion: assuming each section (i.e., each INDEX section) works independently, and effectively, give each of them a cell of their own; and then use IF or CHOOSE based on race to go pick whichever of those cells is appropriate. Another way to describe this recommendation: use helper columns to break up a nearly unintelligible formula into discrete sections, and then extract the one you want.

@mathetes 

 

I agree with the idea of re-evaluating how your data is organized. It struck me when I took a whole 4 minutes looking at your data, that a single table might be helpful. You'll want to add columns for 'species' and perhaps other attributes, but then you'll be able to use VLOOKUPs, and other methods for extracting data (including building intermediate, or 'helper', data).

 

(BTW, its been over 40 years since I played D&D ... didn't even know people were still paying.)

Then that's what I shall do! Thanks for taking the time to read through all my excessively long formula! :grinning_face_with_sweat:

Thank you for your help! Yeah, I understand DnD is experiencing quite a resurgence at the moment!

@tickleyfeet29 

Why does your formula start with an "@"?

Are you porting a workbook from an old version of Excel 365?  If so, changes are meant to be backward compatible and errors should be reported to Microsoft.

If you are using Office 365 other options become possible.  The attached shows a version of data with some rearrangement.  Then I have used XLOOKUP to return a name corresponding to the given species where 'gender' is a randomly chosen ±1 (search from start or search from end).  A unique instance will be returned either way.

 

Picture1.png

 

I'm using the Excel App that I am able to install at home through my workplace login; I assume that means I'm likely using 365?... Not 100%. :\

The formulas, using both IF and INDEX, keep correcting themselves, unprompted, to include an @ at the beginning. The first time it happened, there was a little pop up drawing attention to it and saying something along the lines of 'you may notice an @ sign appearing in your formulas, don't worry'.

Thank you so much for your help and having a go at solving my problem for me - I was toying around with the LOOKUP functions so for you to have given me an example is ideal. Thanks a lot!

@tickleyfeet29 

I have attached an Office 365 work file in the hope that it might work under your copy of Office.  The mockup of your data appears as a (blue bordered) table because I used Power Query to normalise it.  The output is the green-bordered table. on the next sheet.

The XLOOKUP returns the first or last instance of matched records depending on the (random) setting of 'gender'.

1 best response

Accepted Solutions
best response confirmed by tickleyfeet29 (Copper Contributor)
Solution
Is there a reason why this has to be a SINGLE formula?
I'd be surprised if there's any Excel manual out there that recommends writing formulas this long.
I KNOW that there are Excel manuals that warn AGAINST writing formulas this long.

It might be fun when it works, but as you've encountered here, formulas this long are opportunities for Orcs and all other kinds of creatures to get in and make mischief.

A suggestion: assuming each section (i.e., each INDEX section) works independently, and effectively, give each of them a cell of their own; and then use IF or CHOOSE based on race to go pick whichever of those cells is appropriate. Another way to describe this recommendation: use helper columns to break up a nearly unintelligible formula into discrete sections, and then extract the one you want.

View solution in original post