Help? Inverse factorial?

[43fan]

Not sure if I'm wording that right, or even if it's proper... or for that
matter, if what I'm talking about would be an inverse factorial(if there is
such a thing)... but... I asked this elsewhere but may have put a header
that caused people to not even read, so here goes again.

Basically what I'm trying to do is take a random number of places, and
divide 100% over those places, in a descending amount from first to last,
with first being the highest percentage.

IOW, let's say there were 4 places, the division would be something like
1st - 50%
2nd-25%
3rd-15%
4th-10%

I may have as few as say the 4 places above, or maybe as many as 100. I
don't know what that number will be.

I'm no mathematician(as you can see from my header*g*) but I'd think that
there has to be a mathematical formula to do this.

Thanks!
Shawn


--
It's not just based on number of championships won. Richard Petty won
200 races and 7 Daytona 500s in his 30+ year driving career. He also has
the most top-5s (555), top-10s (712), poles (126), laps completed
(307,836), laps led (52,194), races led (599) and consecutive races won
(10 in 1967) of any driver in NASCAR history.

 

[Jim]

Hard to say without knowing how the breakdown of the percentages to be
awarded. To find the amounts, the number is divided by the participants. 100
split equally among 10 participants would be =100/10. However, rewards are
seldom distributed equally, so...

What needs to be determined is the worth of the order of place (rank) and if
there is a cutoff beyond a certain number of participants. Usually (and I am
thinking of sporting competitions) only a set number share in
awards/medals/moving to the next round. All others receive a
sportmanship/participant trophy/sash. In a business setting everyone
trailing the top five may recieve an Attaboy award. <g>

 

[Ray Kanner]

Just sum the digits from 1 to N, where N is the number of
places (let's call it S) and then the weight for each
place in descending order is N/S, (N-1)/S, (N-2)/S ....
1/S. In the example you gave the sum of 1,2,3,4=10, so the
weights would be 40%, 30%, 20% 10%.

Ray Kanner

 

[43fan]

Jim,

That's just it, that's what I want the spreadsheet/program to figure for
me... the percentage breakdown. I suppose I could write it in Foxpro and
use a whole lot of If or Case statements, but I'd think there'd be a
mathematical formula to do it.

In most cases, the payout will be 1 in 4, IOW if there are 100 entrants,
there'll be 25 places paid. I'd like to be able to set the amount for the
1st place manually, so then would need the remaining amount divided among
the rest of the people. Say the prize fund was $1500, I'd set 1st place to
pay $500, then divide the $1000 among the rest of the people.

Of course I can do this all manually, but it'd be nice to be able to come up
with a set spreadsheet where I could enter the number of entries, the entry
fee, the portion of it going to the prize fund and the payout figure(1 in 4,
1 in 5, etc) and let it figure the actual payout breakdown.

 

[Jim]

Well, this is your project. If decisions can be made on the aspects you have
talked about, more help can be offered.

 

[Harlan Grove]

That's just it, that's what I want the spreadsheet/program to figure for
me... the percentage breakdown. I suppose I could write it in Foxpro and
use a whole lot of If or Case statements, but I'd think there'd be a
mathematical formula to do it.

In most cases, the payout will be 1 in 4, IOW if there are 100 entrants,
there'll be 25 places paid. I'd like to be able to set the amount for the
1st place manually, so then would need the remaining amount divided among
the rest of the people. Say the prize fund was $1500, I'd set 1st place to
pay $500, then divide the $1000 among the rest of the people.

...

The point is that there are an infinite number of possible ways to create a
decreasing finite sequence of percentages that sum to 100%. No program can
decide which one to use. That's up to you, the party in the process who's
presumably capable of independent thought and decision making. You can't
delegate it to the computer.

Your earlier example was a $100 total payout divided as $50 to 1st place, $25 to
2nd place, $15 to 3rd place and $10 to 4th place. That could be accomplised most
simply as 1/2 of what's left to the next place until you reach the final two
places, at which point you want to split what remains roughly two-to-one in
favor of the higher placing of the two rounded *down* to some base increment
($5). If that's acceptable, then enter the total payout in a cell named TPO.
Enter the base increment in a cell named INC. Create a list of the places which
will receive prizes in ascending order (so 1st place topmost) and name it POL.
In the cell immediately to the right of POL enter the simple formula

=TPO/2

Then in the cell below that enter the formula

=IF(ROW()-CELL("Row",POL)<ROWS(POL)-2,(TPO-SUM(OFFSET(POL,0,1,SUM(ROW()
-CELL("Row",POL)),1)))/2,IF(ROW()-CELL("Row",POL)<ROWS(POL)-1,ROUNDDOWN(
ROUNDUP((TPO-SUM(OFFSET(POL,0,1,SUM(ROW()-CELL("Row",POL)),1)))*2/3,0)
/INC,0)*INC,TPO-SUM(OFFSET(POL,0,1,ROW()-CELL("Row",POL),1))))

Select this cell and fill it down so that all cells to the right of POL contain
formulas. This doesn't handle ties.

If you want arbitrary payouts to the 1st place finisher (i.e., an arbitrary % of
TPO), then the simplistic approach would be to adapt the larger formula.

=IF(ROW()-CELL("Row",POL)<ROWS(POL)-2,(TPO-SUM(OFFSET(POL,0,1,SUM(ROW()
-CELL("Row",POL)),1)))*OFFSET(POL,0,1,1,1)/TPO,
IF(ROW()-CELL("Row",POL)<ROWS(POL)-1,ROUNDDOWN(ROUNDUP((TPO-SUM(
OFFSET(POL,0,1,SUM(ROW()-CELL("Row",POL)),1)))*2/3,0)/INC,0)*INC,TPO
-SUM(OFFSET(POL,0,1,ROW()-CELL("Row",POL),1))))

But that has problems when the 1st place % of TPO is less than 50%, in which
case the formula could give the second to last place entry more than the next
higher (third to last) placing entry. There are ways to deal with this by
expanding the ad hoc handling of the lowest placing finishers to include the
lowest 3, 4 or more, but it gets messy quickly. And if there are N entries in
POL, then the 1st place entry can't have a % of TPO less than 1/N unless some
lower placing finishers receive more. Probably the best way to approach variable
payouts is to specify the payouts for 1st place and the lowest place, then pick
a decreasing function to interpolate between the two, and spread any rounding
errors between the intermediate places in POL.

Like it or not, what you want to do isn't easy to do. It involves difficult
trade-offs which *YOU* must decide upon. If that's too much work for you, that's
just your bad luck.

 

[43fan]

Harlan,

I actually found a spreadsheet already created at Playpool.com. It
does pretty much exactly what I want/need it to do. If you're
interested, it's a free download, go take a look.

Shawn