Results 1 to 7 of 7

Thread: Amount needed to meet target

  1. #1
    Seeker The_excell_initiate's Avatar
    Join Date
    Nov 2015
    Posts
    9
    Articles
    0
    Excel Version
    2013, 2016

    Amount needed to meet target



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

    Hey Everyone,

    I was wondering if I could get help with the following. I am looking for a formula to calculate how many of a certain value you need to hit a target percentage.

    To clarify , let's say hypothetically I play pro Basketball and I wish to score 90% off all my shots. Currently I already missed 3 shots and scored 7 . Which means I have a total of 70% scored . Let's say my misses don't increase minimum amount of shots I need to take to hit 90% or above.

    A1 A2 A3 A4 A5
    Scored shots Missed Target Percentage new required scored shots missing scored shots
    7 3 90 Formula ??? =A4-A1

    I am looking for the formula to find for the above example the number : 27

    Every time I tried I have gotten stuck in a circular reference, I believe it's something to do with inverted percentages but just can't figure it out


    I would really appreciate some help

  2. #2
    Hi Neophyte, the below I think gets what you are after, no formula needed, column E will show you how many more successful hits are required with column B showing total of misses you entered.

    A1 B1 C1 D1 E1 F1 G1
    Target MISSUS HITS ACTUAL HITS NEEDED OF TOTAL SHOTS CONFIRM
    0.9 3 7 =C3/SUM(B3:C3) =(B3*A3)*10 =E3+B3 =E3/(E3+B3)

  3. #3
    Hi T_e_i, the below I think gets what you are after, no formula needed, column E will show you how many more successful hits are required with column B showing total of misses you entered.

    A1 B1 C1 D1 E1 F1 G1
    Target MISSUS HITS ACTUAL HITS NEEDED OF TOTAL SHOTS CONFIRM
    0.9 3 7 =C3/SUM(B3:C3) =(B3*A3)*10 =E3+B3 =E3/(E3+B3)

  4. #4
    Seeker The_excell_initiate's Avatar
    Join Date
    Nov 2015
    Posts
    9
    Articles
    0
    Excel Version
    2013, 2016
    Dear Axle11 ,

    Thanks for the reply!

    If I change the target and use your formula it does not always work , I don't understand either why you would do =(b3*A3)*10
    It looks as follows applying your formulas dto different targets and numbers :

    A1 B1 C1 D1 E1 F1 G1
    Target MISSUS HITS ACTUAL HITS NEEDED OF TOTAL SHOTS CONFIRM
    90% 3 7 0.7 27 30 90%
    80% 3 7 0.7 24 27 89%
    60% 4 7 0.63636 24 28 86%

  5. #5
    Apologies The_excell_initiate was a late night last night when I posted, hoping the below meets your requirements.

    A1 B1 C1 D1 E1 F1 G1
    Target MISSES HITS TOTAL SHOTS ACTUAL HITS NEEDED NEEDED TOTAL
    0.9 3 9 =SUM(B3:C3) =C3/SUM(B3:C3) =(D3*B3/(D3-(D3*A3)))-B3 =F3+B3

  6. #6
    Seeker The_excell_initiate's Avatar
    Join Date
    Nov 2015
    Posts
    9
    Articles
    0
    Excel Version
    2013, 2016
    Dear Axle11,

    This seems to do exactly what I was looking for, while waiting for your answer I did find two other ways I would like to share with you that do the same.

    So yours as far as I understand is :

    W: Current Misses
    X: current hits
    Y: Current Total
    Z: Target in decimal form
    x': Future x


    x' =roundup((Y*X/(Y-(Y*Z)-W),0)


    ______________________________________________

    The formula I managed to get the result with was :

    x : current Value
    Y: current total
    Z : target in whole numbers
    x' : future x


    x' = Roundup(((z*y)-(100*x)/(100-z)+x,0


    I also found that you can achieve the required result through excel Goal Seek , however this is tedious and only allows one cell at a time.
    You have been a great help


    THANKS !!!!!

    EDIT :

    I add Roundup , as you can't shoot for instance 0.25 of a ball

  7. #7
    Nice to see more than 1 way to skin a cat, good thinking with the roundup.

    Cheers

Tags for this Thread

Posting Permissions

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