How do Imake a formula to- (Part 2)

[litefrozen]

Ok I've been working on my prize list sheet formulas.

I have copy of my sheet here if anyone interested-
http://home.earthlink.net/~litefrozen/prizelistsheet_v1.xls

After trying some different things I've got to here.
I used a prize list I made by "hand" as a reference and to compute percentages for each place.
So for the payouts I used the formula (Payouts=Prizefund x percent) then rounded off.

This is a prizelist that has a $15756.00 prizefund. There are 32 teams with 4 players on each team.
----------------------------------------------------------------------------
The Formulas

A B C D E
1 Prize Fund Position Payouts Payout per Player Percent of Prize Fund
2 $15,756.00 1 =ROUND(($A$2*E2),0) =C2/$A$8 6.35%

#Players
8 4

33 32 =ROUND(($A$2*E33),0) =C33/$A$8 1.15%

35 Total of payouts =SUM(C2:C34) =SUM(D234)*$A$8 =SUM(E2:E34)
-----------------------------------------------------------------------------
The Display

A B C D E
1 Prize Fund Position Payouts Payout per Player Percent of Prize Fund
2 $15,756.00 1 $1,001.0000 $250.2500 6.35%

33 32 $181.0000 $45.2500 1.15%

35 Total of payouts $15,758.0000 $15,758.0000 100.00%

-------------------------------------------------------------------------------------------------

The problem here is that the "Total of Payouts"(C35) is different than the prize fund(A2).
$15,756.00<These are not the same>$15,758.0000

I know this is because of the ROUND function. I used it to get rid of the odd cents that came up
(like $1000.5060) in the team payouts. This also helped with the per player payouts.
It limited the cents to just .00, .25, .50, .75 cents.

Now I need to know if theres a way to adjust the payout formula or something else.
So that the two amounts match. I've tried the diff. ROUND functions and TRUNC they don't seem to fix it.
I can change one cells formula and fix it, but if I change the Prize fund amount its off again.

I feel I'm close to it working. There are other things I would like to add (like a way to vary the percentages
without just redoing them by hand.) but I want to get this down first.

Thanks for the help.

 

[Geoff Lilley]

Here's the biggest question I have: How is the prize fund aggregated?
Does each team or individual contribute a certain amount of money to
the pot? If so, post back with how much each team contributes, and we
can go from there.

The other question is, are those percentages the basis of the prize
payout? If so, you can create a two-column table:

Position Percent
1 6.35%
2 5.70%
3 5.08%
4 4.70%
5 4.57%

Name this range "payout."

Then, in column C, where you had the "Payout:"

=vlookup(B2,payout,2,false)*A$2 (with A2 being where the total prize
money was.)

Column D, "Payout per Player," then, could be: =C2/A$8 (split the prize
money by however many players there are per team.)

HTH.
Cheers,
Geoff Lilley
Microsoft Office Master Instructor (2000/XP)
Apple HelpDesk Specialist

 

[litefrozen]

Here's the biggest question I have: How is the prize fund aggregated?
Does each team or individual contribute a certain amount of money to
the pot? If so, post back with how much each team contributes, and we
can go from there.

Ok how we get the money is
Each person pays $3.55 each night they bowl.There are 128 people. They bowl for 34 weeks.
34 x $3.55=$120.70
128 x $120.70=$15449.60

Also each team pays a sponsor fee of $24.00 and there are 32 teams.
32 x $24.00=$768.00
Also we have a leagues sponsor that added in $300.00
So
$15449.60+$768.00+$300.00=$16517.60
The total of the main prize fund is=$16517.60
Now $761.60 is set aside for other prizes.
$16517.60-$761.60=$15756.00

The $15756.00 prize fund is divided up between the 32 teams. With 1st place getting the most & 32 getting the least.

The other question is, are those percentages the basis of the prize
payout?

Yes I'm using those percentages at the moment. They were determined from the current prize list.

If so, you can create a two-column table:
Position Percent
1 6.35%
2 5.70%
3 5.08%
4 4.70%
5 4.57%
Name this range "payout."
Then, in column C, where you had the "Payout:"
=vlookup(B2,payout,2,false)*A$2 (with A2 being where the total prize
money was.)
Column D, "Payout per Player," then, could be: =C2/A$8 (split the prize
money by however many players there are per team.)

HTH.
Cheers,
Geoff Lilley
Microsoft Office Master Instructor (2000/XP)
Apple HelpDesk Specialist

I'll take a look at the table idea. but it appears to just do the same thing my sheet does now, just in a different way.
But it doesnt address the problem of the amounts not matching. Do to how excel rounds numbers.

I believe I have fixed that now .By placing this formula in the last place position
=$A$2-SUM(C2:C32)
Now the total of the payouts match the prize fund.
I got this from reading a reply in another group/

Hello:

You will never make this work out exactly for all values. This is due to
inherent difference between the accuracy of currency (2 decimal places)
and the accuracy of Excel which is approximately 15 decimal places. On
top of that you add the problem of binary approximations for most
decimal
values. We all understand that there is no decimal equivalent for 2/3
but in binary there is no equivalent value for .8 either, in fact many
decimals values do not have an exact equivalent. So expecting exact
outcomes will drive you to drink.

As a practical matter, since you apparently can control the the
percentage
just make sure that one of the percentages forces the total to 1.00 by
say making the last one say something like: in cell e8:
"=1-sum(e3:e7)",
assuming the percentages are going to be in cells e3 to e8. Then if you
do the same for the cell that contains the dollars, you will make it
work
out even. ie. assume the dollars are in cell f8, there put a formula
that
takes the: total dollars [in your example thats a2] - sum(f3:f7) and it
should work out even. That is until the winners complain about the way
you rounded.

Thanks for the help.
AL

 

[litefrozen]

Here's my formulas as they stand now.
The change to this =$A$2-SUM(C2:C32) in the 32nd place seems to have
the amounts matching now. Also I changed ,0) to ,-1) in the formulas.

(A2) Prize Fund $15756.00
(A8) # of Players 4

Position Payout Payout per Player Percent of Prize Fund

1 =ROUND(($A$2*E2),-1) =C2/$A$8 0.0635
2 =ROUND(($A$2*E3),-1) =C3/$A$8 0.057
3 =ROUND(($A$2*E4),-1) =C4/$A$8 0.0508
4 =ROUND(($A$2*E5),-1) =C5/$A$8 0.047
5 =ROUND(($A$2*E6),-1) =C6/$A$8 0.0457
6 =ROUND(($A$2*E7),-1) =C7/$A$8 0.0444
7 =ROUND(($A$2*E8),-1) =C8/$A$8 0.0432
8 =ROUND(($A$2*E9),-1) =C9/$A$8 0.0419
9 =ROUND(($A$2*E10),-1) =C10/$A$8 0.0406
10 =ROUND(($A$2*E11),-1) =C11/$A$8 0.0394
11 =ROUND(($A$2*E12),-1) =C12/$A$8 0.038
12 =ROUND(($A$2*E13),-1) =C13/$A$8 0.0367
13 =ROUND(($A$2*E14),-1) =C14/$A$8 0.0355
14 =ROUND(($A$2*E15),-1) =C15/$A$8 0.0343
15 =ROUND(($A$2*E16),-1) =C16/$A$8 0.033
16 =ROUND(($A$2*E17),-1) =C17/$A$8 0.0317
17 =ROUND(($A$2*E18),-1) =C18/$A$8 0.0305
18 =ROUND(($A$2*E19),-1) =C19/$A$8 0.0292
19 =ROUND(($A$2*E20),-1) =C20/$A$8 0.0279
20 =ROUND(($A$2*E21),-1) =C21/$A$8 0.0267
21 =ROUND(($A$2*E22),-1) =C22/$A$8 0.0248
22 =ROUND(($A$2*E23),-1) =C23/$A$8 0.0194
23 =ROUND(($A$2*E24),-1) =C24/$A$8 0.0188
24 =ROUND(($A$2*E25),-1) =C25/$A$8 0.0185
25 =ROUND(($A$2*E26),-1) =C26/$A$8 0.0182
26 =ROUND(($A$2*E27),-1) =C27/$A$8 0.0175
27 =ROUND(($A$2*E28),-1) =C28/$A$8 0.0168
28 =ROUND(($A$2*E29),-1) =C29/$A$8 0.0157
29 =ROUND(($A$2*E30),-1) =C30/$A$8 0.0148
30 =ROUND(($A$2*E31),-1) =C31/$A$8 0.014
31 =ROUND(($A$2*E32),-1) =C32/$A$8 0.013
32 =$A$2-SUM(C2:C32) =C33/$A$8 =C33/A2

Totals =SUM(C2:C34) =SUM(D234)*$A$8 =SUM(E2:E34)