Results 1 to 4 of 4

Thread: Looking for Help

  1. #1
    Neophyte Dirtbikindad393's Avatar
    Join Date
    May 2019
    Posts
    2
    Articles
    0
    Excel Version
    2010

    Looking for Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    184
    Articles
    0
    Excel Version
    2019
    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)

  3. #3
    Neophyte Dirtbikindad393's Avatar
    Join Date
    May 2019
    Posts
    2
    Articles
    0
    Excel Version
    2010
    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.

  4. #4
    Seeker gue's Avatar
    Join Date
    Nov 2018
    Posts
    19
    Articles
    0
    Excel Version
    Office Professional Plus 2016
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •