# Thread: Amount needed to meet target

1. ## Amount needed to meet target

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. 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. 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. Dear Axle11 ,

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. 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. 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. Nice to see more than 1 way to skin a cat, good thinking with the roundup.

Cheers