Strictly speaking, what you want is impossible. If the probabilities decrease in a geometric or exponential way, that means multiplying each successive term by a ratio (r) that is non-zero. So the last term will never be exactly zero, but rounding errors might work in your favor.
Also, when I did the math, what you're essentially asking for is taking the formula for the sum of a geometric series Sn=a(1-r^n)/(1-r), setting a = p (your first value), and setting Sn to 1 (since the sum of probabilities must add up to 1), then solving for r. r would be the factor you need to multiply p by to get the probability of the next item. You can't solve this equation for r in a closed formula, which is probably why you had some difficulties.
All is not lost though. Consider:
Set B2 to p, B3 to N. Then use Goal Seek to set B5 to 1 by changing B4. Once you have r, you can get the probabilities of all the items with the D2 formula. Then the E2 formula gets you the aggregate sums of the probabilities. Finally, in H2 you can pick a random number from 0 to 1 with RAND(), and use that as a search value in the E1:E5 table, and it will give you a number from 1 to 5 in the probabilities from column D.
Some issues. First, I'm sure you'd rather not have to use Goal Seek. We know the ratio will be <1 (or the probabilities will get bigger), so we can create an array formula that check all 2-digit values from .01 to .99 and pick the closest. If you need more accuracy, we can do .001 to .999, but that'll take 10 times longer.
Next, you probably want it in a single formula. Possible, if it's a LET and it's complicated enough.
Before I work on combining all the pieces, I wanted to see if this is in fact what you're looking for. Let me know.