Creating an RPG Style Loot Dropper

Hey guys,

I'm working on an Excel document which can automatically 'drop' RPG style items (swords, axes and such) and I'm having trouble creating a formula that will drop a specific tier of items based on a percentage drop chance.

For example, I'm looking to roll an item based on the information below.

Magic Item 80%
Rare Item 15%
Unique Item 5%

So there is an 80% chance to get a Magic item, 15% chance for a Rare and a 5% chance for a unique.

I can roll a number between 1-100 and just use a few If statements to check in between number ranges (Item will be Rare if >80 and <5) but I'm running into problems when the drop rates change for the items. Once the values change to say:

Magic Item 40%
Rare Item 50%
Unique Item 10%

Using the same nested If statement won't work in the above example now that the drop chance for Rare items is higher than Magical items.

I have sort of fixed the issue by sorting the values in ascending order depending on their drop chance but this method is cumbersome and I feel there is a cleaner way to accomplish it.

I used the search function but I'm fairly new to using formulas in Excel so I'm not exactly sure I was searching for the correct information. I hope I explained everything well enough and any help is appreciated.

Thanks

Does the attached sheet work? Just remember to hit F9 to refresh and use cell B6 as a check to make sure your percentages add up to 100.

Book2 (2).xlsx

Nice, this seems to work just fine. I knew a simple solution had to exist, but looking at this document for half the day caused me to way over think it.

Thanks for the help.

No problem.