Trying to use EXCEL to Figure out Mathematical calculations

[RudeBoy]

I am trying to set up a formula using maybe the MOD feature in excel to show break downs for pay-out spots (Golf Tournament) for this example 1st thru 7th position.


Total amount of money is $4,680

Finishing
1st
2nd
3rd
4th
5th
6th
7th

Percentage Used by place %
11
10
9
8
7.5
6.5
6

Results by place
$514
$468
$421
$374
$351
$304
$280

HOW CAN I SET THIS UP IN EXCEL USING the MOD feature or another formula item?

Thank for your Help - Take Care

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

Total​

[/td][td]

4680​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Place​

[/td][td]

%​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1st​

[/td][td]

11​

[/td][td]

514,8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2nd​

[/td][td]

10​

[/td][td]

468,0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

3rd​

[/td][td]

9​

[/td][td]

421,2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

4th​

[/td][td]

8​

[/td][td]

374,4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

5th​

[/td][td]

8​

[/td][td]

351,0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

6th​

[/td][td]

7​

[/td][td]

304,2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

7th​

[/td][td]

6​

[/td][td]

280,8​

[/td][/tr]
[/table]

Formula in C4 copied down
=C$1*B4/100

M.

 

[RudeBoy]

How can I make sure the number and even with no change? But still adds up correctly? Thank for your help

$514.80 11
$468.00 10
$421.20 9
$374.40 8
$351.00 7.5
$304.20 6.5
$280.80 6

 

[Marcelo Branco]

How can I make sure the number and even with no change? But still adds up correctly? Thank for your help

$514.80 11
$468.00 10
$421.20 9
$374.40 8
$351.00 7.5
$304.20 6.5
$280.80 6

Don't understand what you're trying to do

M.

 

[philwojo]

How can I make sure the number and even with no change? But still adds up correctly? Thank for your help

$514.80 11
$468.00 10
$421.20 9
$374.40 8
$351.00 7.5
$304.20 6.5
$280.80 6

I understand what you want, but your 7 places don't total out to 100%, so you really can't check to make sure that the total is right. You could use the Round function and specify zero places to just have a whole number, like $515. Then you could also SUM up those totals and compare it to your total payout to make sure it doesn't exceed that number, if it does you would then need to adjust something by hand.

Just surround Marcelo's formula with Round(formula here,0) to get whole numbers.

Not sure if that will work for you but might get you closer.

 

[RudeBoy]

This is how it looks for 18 spots for full amount:
514
468
421
374
351
304
280
257
234
210
200
188
176
164
152
140
130
117
Total $4,680

Percentages are:
11
10
9
8
7.5
6.5
6
5.5
5
4.5
4.25
4
3.75
3.5
3.25
3
2.75
2.5
-------
100 %

Trying to get those break-down results with excel formula(s)
So need to get rid or Cents .80 cents, .20 cents etc etc Thanks for your help.
$514.80
$468.00
$421.20
$374.40
$351.00
$304.20
$280.80

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Total​

[/TD]
[TD]

4680​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Place​

[/TD]
[TD]

%​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

11​

[/TD]
[TD]

515​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

10​

[/TD]
[TD]

468​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

3​

[/TD]
[TD]

9​

[/TD]
[TD]

421​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

4​

[/TD]
[TD]

8​

[/TD]
[TD]

374​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

5​

[/TD]
[TD]

7,5​

[/TD]
[TD]

351​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

6​

[/TD]
[TD]

6,5​

[/TD]
[TD]

304​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

281​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

8​

[/TD]
[TD]

5,5​

[/TD]
[TD]

257​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

9​

[/TD]
[TD]

5​

[/TD]
[TD]

234​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

10​

[/TD]
[TD]

4,5​

[/TD]
[TD]

211​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

11​

[/TD]
[TD]

4,25​

[/TD]
[TD]

199​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[TD]

187​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

13​

[/TD]
[TD]

3,75​

[/TD]
[TD]

176​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

14​

[/TD]
[TD]

3,5​

[/TD]
[TD]

164​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

15​

[/TD]
[TD]

3,25​

[/TD]
[TD]

152​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

16​

[/TD]
[TD]

3​

[/TD]
[TD]

140​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

17​

[/TD]
[TD]

2,75​

[/TD]
[TD]

129​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

18​

[/TD]
[TD]

2,5​

[/TD]
[TD]

117​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

Total​

[/TD]
[TD]

100,00​

[/TD]
[TD]

4680​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C4 copied down
=ROUND(C$1*B4/100,0)

M.

 

[shg]

Another way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td]

$ 4,680.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

Place​

[/td][td="bgcolor:#F3F3F3"]

%​

[/td][td="bgcolor:#F3F3F3"]

Prize​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1​

[/td][td]

11.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 515.00​

[/td][td="bgcolor:#CCFFCC"]C4: =ROUND(B4 / (1 - SUM(B$3:B3)) * ($C$2 - SUM(C$3:C3)), 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

2​

[/td][td]

10.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 468.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

3​

[/td][td]

9.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 421.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td]

8.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 374.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5​

[/td][td]

7.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 351.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

6​

[/td][td]

6.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 304.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

7​

[/td][td]

6.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 281.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

8​

[/td][td]

5.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 257.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

9​

[/td][td]

5.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 234.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

10​

[/td][td]

4.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 211.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

10​

[/td][td]

4.25%​

[/td][td="bgcolor:#CCFFCC"]

$ 199.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

10​

[/td][td]

4.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 187.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

10​

[/td][td]

3.75%​

[/td][td="bgcolor:#CCFFCC"]

$ 176.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

10​

[/td][td]

3.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 164.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

10​

[/td][td]

3.25%​

[/td][td="bgcolor:#CCFFCC"]

$ 152.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

10​

[/td][td]

3.00%​

[/td][td="bgcolor:#CCFFCC"]

$ 140.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

10​

[/td][td]

2.75%​

[/td][td="bgcolor:#CCFFCC"]

$ 129.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

10​

[/td][td]

2.50%​

[/td][td="bgcolor:#CCFFCC"]

$ 117.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td="bgcolor:#E5E5E5"]

100.00%​

[/td][td="bgcolor:#E5E5E5"]

$ 4,680.00​

[/td][td][/td][/tr]
[/table]

Marcelo, I think it's just happystance that your prizes total to the correct number and aren't off by a dollar or two.

 

[Marcelo Branco]

Marcelo, I think it's just happystance that your prizes total to the correct number and aren't off by a dollar or two.

You're absolutely right! I know that just by chance my formula worked. I was waiting for some comment...

As a workaround, I would change only the formula in the last row, C21, for
C1 - SUM(C4:C20)
In your example would be
C2 - SUM(C4:C20)

By the way, your formula is more robust.

M.

 

[RudeBoy]

AWESOMEeeeeeeeeeeeeeee Thanks So much it work