# Help Recalculating A Group Of Numbers To Equal A Specific Number

1. ## Help Recalculating A Group Of Numbers To Equal A Specific Number

(See Attachment)

Hi, I need help recalculating a group of numbers that doesn't equal the total I want it to (with built in restrictions)

So my problem is

Player A hits 95% of his 70 shots, which comes out to 67 shots made

Player B hits 84% of his 70 shots, which comes out to 59 shots made

Player C hits 58% of his 70 shots, which comes out to 41 shots made

Player D hits 16% of his 70 shots, which comes out to 11 shots made

So the group made 253% of their shots and Made 177 Of Their Shots Total As A Group

But I want the group to make 282% of their shots and thus 199 Shots Made As A Group
Without making them shoot again, given their current shot percentages and each players maximum and minimum number of shots their allowed to make and miss, how do I accurately “project up” their shots to meet these parameters? I'm so confused

2. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

Your math concepts are incorrect. We cannot simply sum the percentages.

The first image attachment (left) demonstrates the correct calculations.

First, the formula in E2:E5 should be rounded, since we cannot have a fractional shot. For example, E2 is:

=ROUND(C2*D2,0)

Since the total shots hit is 178 and the total shots tried is 280, the average total shots hit is 178/280 = 63.57% (rounded).

So the formula in C7 is:

=E7/D7

-----
PS.... More likely, the inputs are total shots hit (E2:E5) as well as total shots tried (D2:D5). Thus, E2:E5 should contain numbers, not a formula.

And the percentage hit for each player would be calculated in C2:C5, not entered as numbers. For example, C2 is:

=E2/D2

See the second image attachment (right) below.

3. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

So Now my question is how do I recalculate the numbers so they can hit 71% of their shots as a group? Without any player going over their max number of shots their allowed to make?

4. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

Based on individual's proportionate contribution of their shots to total shots made, subject to max shots:

Help Recalculating A Group Of Numbers To Equal A Specific Number.png

5. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

Originally Posted by appletree943
how do I recalculate the numbers so they can hit 71% of their shots as a group? Without any player going over their max number of shots their allowed to make?

As you know, 71% of 280 is 199 (rounded). So the team needs to hit 21 more shots (199 - 178).

We can distribute the additional 21 shots among the team in many ways.

As a technical matter, the simplest answer: Player D needs to hit 21 more shots, for a total 32 of 70, which is about 45.71%.

If you have some other criteria for choosing a particular distribution, that is your choice to make -- and to inform us, if you want further assistance.

-----

josephteh suggests distributing the additional shots among the team based on their current distribution, subject to each players limit (currently 70 for all).

Nothing wrong with that objective; again, one of many. But I would implement it differently. See the image and Excel file attachments below.

The formula in column F is (F2, for example):

=MIN(D2-E2, ROUND((\$F\$8-SUM(\$F\$1:F1))*E2/SUM(E2:\$E\$5), 0))

D2-E2 is the max number of additional shots allowed for the player, given his limit in D2.

\$F\$8-SUM(\$F\$1:F1) is the remaining additional shots required, where \$F\$8 is the total required, and SUM(\$F\$1:F1) is the sum of additional shots already distributed.

E2/SUM(E2:\$E\$5) is the proportion of the player's current contribution relative to the remaing total current contribution.

Those calculations and ROUND(...,0) ensure an integer result, such that the sum of each player's additional shots is the required total in F8.

(In contrast, in josephteh's implementation, the rounded non-integer results in his column M sum to 22, not 21.)

The calculations in columns G and H show the new number of shots hit for each player and their percentage hit.

6. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

curiouscat408 formula is much more simpler, but needs refinement. If you change Player D %hit to 5%, Player C needs to score 13 more shots, 2 more than the better player Player B.

Help Recalculating A Group Of Numbers To Equal A Specific Number 2.png

7. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

Originally Posted by josephteh
curiouscat408 formula [...] needs refinement. If you change Player D %hit to 5%, Player C needs to score 13 more shots, 2 more than the better player Player B.
So what? Why not?

Recall that the "simplest" solution requires that Player D make __all__ of the additional shots (to the extent possible within his limits); no additional for the better players. Nothing wrong with that, as a technical matter.

You seem to be making a value judgment. But the algorithm was yours, in the first place.

And my implementation of it seems to be correct. So the formulas in column F are technically correct, as-is.

We should not debate distribution algorithms here. That is appletree943's choice. I probably should not have made my "aside" comments (now deleted). Mea culpa!

8. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

Cool down! Why are you so angry and defensive? Didn't I said you have a simpler formula but just need to refine?

9. ## Re: Help Recalculating A Group Of Numbers To Equal A Specific Number

To expect Player D, the worst player to make all the additional shots, is the worst case scenario.

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