How do I make a formula to-

[litefrozen]

Hi I'm new at making spreadsheet formulas.Im trying to make a
spreadsheet that will make a prize list.
I want to enter in one cell a amount of money say a $10,000.00 prize
fund.
Then have it divide that up between say 32 teams with Ist place getting
so much going down to last place getting so much.
thanks for any help.

 

[JE McGimpsey]

Hi I'm new at making spreadsheet formulas.Im trying to make a
spreadsheet that will make a prize list.
I want to enter in one cell a amount of money say a $10,000.00 prize
fund.
Then have it divide that up between say 32 teams with Ist place getting
so much going down to last place getting so much.

I think you need to provide a bit more information.

What determines which place a team gets (and if ties are possible, what
should happen then)?

How is your data laid out?

Are the prizes each a fixed percentage of the total prize fund (and if
so, what percentages are they)?

Or are they a fixed dollar amount (or a minimum/maximum amount)?

Or some combination?

Will the split amounts/percentages change?

Do you need to round to a particular increment (i.e., $210 rather than
$208.43)?

 

[litefrozen]

I think you need to provide a bit more information.

What determines which place a team gets (and if ties are possible, what
should happen then)?

How is your data laid out?

Are the prizes each a fixed percentage of the total prize fund (and if
so, what percentages are they)?

Or are they a fixed dollar amount (or a minimum/maximum amount)?

Or some combination?

Will the split amounts/percentages change?

Do you need to round to a particular increment (i.e., $210 rather than
$208.43)?

Ok maybe this will help-
Say we have a bowling league. We find we have XX# of teams this gives
us $XXXX.XX dollars for the prize fund .
The number of teams = the payout positions 1st place,2nd,3rd.......

The team that gets the most points comes in first place, the least
points comes in last place.
No ties, no payout based on number of points won.
Each place would get a set amount.
with 1st place getting the most and last place the least.
__________________________________
Heres a example.
Input prize fund amount= $3000.00
Input Number of Team/Positions= 5

Output=
Place #1 gets $1000.00 ( which is about 33% of $3000.00= prize fund)
Place #2 gets $800.00 ( which is 40% of $2000.00=$3000-$1000)
Place #3 gets $600.00 ( which is 50% of $1200.00=$2000-$800)
Place #4 gets $400.00 ( which is about 66.66% of $600=$1200-$600)
Place #5 gets $200.00 ( which is 100% of $200=$600-$400)
------------------
Total payout= $3000.00 which = the prize fund amount.
_____________________________________
I assume the amounts for each place would be a percentage of the prize
fund or I would have to set a upper & lower limit of some kind and then
it would fill in the rest.

Sorry I'm not that good at explaining things

 

[JE McGimpsey]

Heres a example.
Input prize fund amount= $3000.00
Input Number of Team/Positions= 5

Output=
Place #1 gets $1000.00 ( which is about 33% of $3000.00= prize fund)
Place #2 gets $800.00 ( which is 40% of $2000.00=$3000-$1000)
Place #3 gets $600.00 ( which is 50% of $1200.00=$2000-$800)
Place #4 gets $400.00 ( which is about 66.66% of $600=$1200-$600)
Place #5 gets $200.00 ( which is 100% of $200=$600-$400)

I'm not sure how your "Input Number of Team/Positions" will work, since
you didn't make your percentages dependent on them, but given your
example, you could simply use a table:

J K

1 Place Percentages
2 1 33%
3 2 =(1-K2)*40% ===> 27%
4 3 =(1-SUM(K2:K3))*50% ===> 20%
5 4 =(1-SUM(K2:K4))*2/3 ===> 13%
6 5 =1-SUM(K2:K5) ===> 7%

Then if your total prize pool is in, say, B1, then the amounts are

L2: =ROUND($B$1*K2,-2)

which would round to the nearest hundred. Copy L2 down to L5, then in L6
enter

=$B$1-SUM(L2:L5)

I assume the amounts for each place would be a percentage of the prize
fund or I would have to set a upper & lower limit of some kind and then
it would fill in the rest.

If you're not going to have 5 fixed percentage prizes, the formulas
would likely be somewhat more complicated.

Sorry I'm not that good at explaining things

No problem, except that the quality of any solution is likely to depend
on your ability to clearly express what you're looking for...

 

[litefrozen]

I'm not sure how your "Input Number of Team/Positions" will work, since
you didn't make your percentages dependent on them, but given your
example, you could simply use a table:
J K
1 Place Percentages
2 1 33%
3 2 =(1-K2)*40% ===> 27%
4 3 =(1-SUM(K2:K3))*50% ===> 20%
5 4 =(1-SUM(K2:K4))*2/3 ===> 13%
6 5 =1-SUM(K2:K5) ===> 7%
Then if your total prize pool is in, say, B1, then the amounts are
L2: =ROUND($B$1*K2,-2)
which would round to the nearest hundred. Copy L2 down to L5, then in L6
enter
=$B$1-SUM(L2:L5)
If you're not going to have 5 fixed percentage prizes, the formulas
would likely be somewhat more complicated.
No problem, except that the quality of any solution is likely to depend
on your ability to clearly express what you're looking for...

Well Ive been working on this and have got closer.I came up with a
formula which looks similar to what you have shown me.
here it is.
The sheet I am working on here is made for 32 teams

where-
Cell B2 is the prize fund amount
Cell B5 is Place #1
Cell B16 is Place #12
Cell E2 is a percentage
-------------------------
Example for Place #1the formula is-
=$B$2*(1-E2)
---------------------------
Example forPlace #13-
Cell B17 which is place #13 has this formula
=($B$2-SUM($B$5:B16))*(1-$E$2)

This is taking the amount of the prize fund (B2) then subtracting the
sum of places 1to12 (B5:B16) to see whats left, then figuring a
percentage(E2) of that for what place #13 gets.
-----------------------------------
Now this seems to work . But it dosn't use all the prize fund. when I
total all the places its a little less than the total prize fund
amount.
I need to adjust it now to make it use it all. I'll see if what you
showed me helps in this.I'll see about the rounding part too, I hadn't
got that far yet.

I'm not sure how your "Input Number of Team/Positions" will work, since
you didn't make your percentages dependent on them

I really didn't think about making the percentage dependent on the # of
teams. That might be something I would do. if you got a example.

Thats how I really pick this stuff up.If I see a example of a formula
like or similar too what I want do. I can then adjust it to fit what
I'm doing.

Hey thanks for all the help!!