+ Reply to Thread
Results 1 to 9 of 9

Help Recalculating A Group Of Numbers To Equal A Specific Number

  1. #1
    Registered User
    Join Date
    08-07-2017
    Location
    Philadelphia
    MS-Off Ver
    Microsoft 365
    Posts
    18

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    325

    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.
    Attached Images Attached Images
    Last edited by curiouscat408; 10-09-2021 at 12:29 AM.

  3. #3
    Registered User
    Join Date
    08-07-2017
    Location
    Philadelphia
    MS-Off Ver
    Microsoft 365
    Posts
    18

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

    Thank you for your help

    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?
    Attached Files Attached Files
    Last edited by AliGW; 10-09-2021 at 03:41 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,426

    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. #5
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    325

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

    Quote Originally Posted by appletree943 View Post
    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?
    There are many answers.

    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.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by curiouscat408; 10-09-2021 at 04:25 AM.

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,426

    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. #7
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    325

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

    Quote Originally Posted by josephteh View Post
    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!
    Last edited by curiouscat408; 10-09-2021 at 04:26 AM.

  8. #8
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,426

    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. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,426

    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.

+ 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. [SOLVED] Count specific numbers within a group of numbers
    By ickle1999 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-06-2021, 11:06 AM
  2. Replies: 1
    Last Post: 03-01-2016, 04:55 PM
  3. Insert a specific text If the number is equal to 1
    By oozden in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 09:30 AM
  4. How to divide a group of numbers in to 3 groups as equal as possible.
    By herbeey in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-27-2013, 08:10 PM
  5. How do I group many numbers into 100 equal groups?
    By drdirt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2013, 11:29 PM
  6. Sum of 2 cells in a column equal a specific number
    By philippe1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-14-2011, 01:43 AM
  7. Finding what numbers equal another number
    By all2sober in forum Excel General
    Replies: 1
    Last Post: 06-26-2008, 01:54 PM

Tags for this Thread

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