+ Reply to Thread
Results 1 to 9 of 9

Awarding points when conditions are met

  1. #1
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

    Awarding points when conditions are met

    Hello. I wonder if someone would be kind enough to create a formula for me?
    The finishing position in a race will be entered in cell A2
    Depending on the position, points will be awarded in B2
    In C2 the points won in B2 will be multiplied by 20000
    These are the positions and points awarded.
    1st - 25 points
    2nd - 18 points
    3rd - 15 points
    4th - 12 points
    5th - 10 points
    6th - 8 point
    7th - 6 points
    8th - 4 points
    9th - 2 points
    10th - 1 point

    Thank You
    Attached Files Attached Files
    Last edited by tom hatten; 03-27-2015 at 07:36 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Formula needed for the following conditions

    retitled....
    Last edited by protonLeah; 03-27-2015 at 10:14 PM.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

    Re: Awarding points when conditions are met

    Does my post now comply with Rule 1?

    Thank You

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Awarding points when conditions are met

    I would create a small table with positions and points E:F) and use vlookup...
    A
    B
    C
    D
    E
    F
    1
    Position
    Points
    Points Won
    2
    2
    18
    36000
    1
    25
    3
    4
    12
    24000
    2
    18
    4
    6
    8
    16000
    3
    15
    5
    1
    25
    50000
    4
    12
    6
    3
    15
    30000
    5
    10
    7
    5
    10
    20000
    6
    8
    8
    7
    6
    9
    8
    4
    10
    9
    2
    11
    10
    1

    B2=IF(A2="","",VLOOKUP(A2,$E$2:$F$11,2,0))
    C2=IF(A2="","",B2*2000)
    both copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Awarding points when conditions are met

    In B2: =INDEX({25;18;15;12;10;8;6;5;2;1}, A2)
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

    Re: Awarding points when conditions are met

    Sorry but I can't seem to get it to work.
    Points are awarded in cell D2 depending on the position in C2 (as shown in my first post) Points Won in E2 will be D2*20000

    I've attached a new file, hoping this will help.

    Thank you
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Awarding points when conditions are met

    The only difference between your 1st and 2nd files, is the 2nd file now has a column called Stake, which you dont meantion anywhere, and below that, a single value. Apart from that, bith files show pretty much notjhing apart from headings - hard to guess what you want, based on that

    Sorry but I can't seem to get it to work.
    Cant get what to work? Which suggestion, what are you getting and what to do want?

    Apart from the *2000, shg's formula gives exactly what mine does

  8. #8
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

    Re: Awarding points when conditions are met

    Thank you for taking time to help me.

    Based on these conditions:

    1st place - 25 points
    2nd place - 18 points
    3rd place - 15 points
    4th place - 12 points
    5th place - 10 points
    6th place - 8 point
    7th place - 6 points
    8th place - 4 points
    9th place - 2 points
    10th place - 1 point

    Jack's selection finishes in 2nd place and is awarded 18 points.
    Jack staked 20000 and therefore wins 360000.

    I will enter the finishing position in A2. I need a formula to work out the points won
    in B2 and how many points will be won in C2.

    Thank you.
    Attached Images Attached Images
    Last edited by tom hatten; 03-28-2015 at 05:10 PM.

  9. #9
    Forum Contributor
    Join Date
    08-21-2010
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft 365
    Posts
    215

    Re: Awarding points when conditions are met

    I'm sorry but for some reason I am unable to post attachments or pictures.
    My previous post is based on the table in post #4

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-11-2011, 01:53 PM
  2. Macro for consecutive conditions (7 in a row) needed
    By scottwhittaker2333 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-22-2010, 12:38 PM
  3. Replies: 1
    Last Post: 08-27-2009, 06:07 PM
  4. Replies: 12
    Last Post: 05-15-2009, 08:38 AM
  5. more conditions are needed in custom filter
    By ss_bb_24 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-12-2009, 07:29 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1