Looking for Help

Dirtbikindad393

New member
Joined
May 13, 2019
Messages
2
Reaction score
0
Points
0
Excel Version(s)
2010
I am trying to make a formula for a fantasy league sheet where there will be multiple names and a currency number that each player donated and a total of all money in the league. The league pays top 3 at a rate of
1st 50%
2nd 30%
3rd 20%
How can I make it so all I have to do is put 1, 2 or 3 and it will tell me what the currency number is for that spot?

Lets say 10 names each donate 20.00 total is 200.00 I want to put the number 1 in a box and have it tell me what 50% of the total is. @ in a box and have it tell me what 30% of the total is etc
 
Data Range
A
B
C
D
E
1
Player​
Donation​
Placement​
Amounts​
2
B​
20​
1st​
100​
3
C​
20​
2nd​
60​
4
D​
20​
3rd​
40​
5
E​
20​
6
F​
20​
7
G​
20​
8
H​
20​
9
I​
20​
10
J​
20​
11
K​
20​
12
Total​
200​


Formulas
Data Range
A
B
C
D
E
1
Player​
Donation​
Placement​
Amounts​
2
B​
20​
1st​
=0.5*B12​
3
C​
20​
2nd​
=0.3*B12​
4
D​
20​
3rd​
=0.2*B12​
5
E​
20​
6
F​
20​
7
G​
20​
8
H​
20​
9
I​
20​
10
J​
20​
11
K​
20​
12
Total​
=SUM(B2:B11)​
 
Doesn't it need an "If" so the formula can be in all cells in Column D then where ever I place the 1 or 1st it will show the payment in Column E? In the scenario you show I would have to sort the sheet to make top 3 appear on top.
 
you could use in each cell
Code:
=IFERROR(CHOOSE($D2;0,5;0,3;0,2)*$B$12;"")

if you fill down column "D" $D2 will change to $D3 ...$Dn. According the table above $B$12 contains the sum.

if you put a 1, 2, 3 somewhere this is used as index and will be multiplied by the sum.
if nothing (blank) is in a cell, this cannot be used as an index and generates an error (#VALUE) --> replaced with a blank.

you can also use nested ifs instead.

NOTE: this will not handle 2 identical places like for example 2x1, 0x2 and 1x3
 
Back
Top