+ Reply to Thread
Results 1 to 5 of 5

Picking names by percentage

  1. #1
    Forum Contributor
    Join Date
    05-04-2015
    Location
    Atlanta GA
    MS-Off Ver
    2010
    Posts
    106

    Picking names by percentage

    I have a bunch of running backs with the following amounts of carries and the percentage that they carried the ball:

    cell name carries % 100-sided dice is in cell E5
    A1 Miles 249 51
    B1 Ted 100 20
    C1 Hal 77 16
    D1 Tim 62 13

    Total is 488

    I guess the only stat you need is the percentage, which is in cells C1-C4

    So, what I'm doing now is
    Please Login or Register  to view this content.
    There's got to be a better/easier way to do this.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Picking names by percentage

    What is it that you are looking to do? What does the 100-sided dice have to do with this?

    Perhaps creating a small representative sample of your data along with the desired result of the formula/s will help us get a better understanding.

    You can upload an Excel workbook by clicking on GO ADVANCED and then scrolling down to Manage Attachments to open the upload window.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Picking names by percentage

    Your nested IF() looks to me like a basic lookup function using the binary search/approximate match option. Its not clear to me exactly how you have this data entered into the spreadsheet. Assuming that it is a table with names in column A, carries in column B, and % in column C, I would probably:
    1) Add a cumulative sum to column D D1=0 D2=SUM(C$1:C2) copy down.
    2) A MATCH() [3rd argument =1] to locate your dice roll in this column =MATCH(E5,D1:D4,1) https://support.office.com/en-us/art...9-533f4a37673a Pay particular attention to the behavior described for the binary search/approximate match options.
    3) An INDEX() function using the result of the MATCH() function to return the name from column A =INDEX(A1:A4,result of MATCH() function) https://support.office.com/en-us/art...2-b56b061328bd

    Does that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Picking names by percentage

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Picking names by percentage

    you could shorten this =if(e5<52,a1,if(and(e5>51,e5<72),a2,if(and(e5>71,e5<88),a3,a4 to this...
    =if(e5<52,a1,if(e5<72,a2,if(e5<88,a3,a4)))
    every time the formula is met it essentially stops so you don't need the and with between.

    EDIT: interesting other replies, I only addressed the formula, I'll be interested in seeing what responses the other contributors get.
    Last edited by Sam Capricci; 12-22-2017 at 01:47 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

+ 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: 09-20-2016, 07:33 PM
  2. [SOLVED] Duplicate Names When Picking Out Highest Scorer
    By webstmonkey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-02-2013, 09:51 PM
  3. [SOLVED] Vlookup – Picking up first date and then picking up second date if there is one.
    By CranstonWatts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2012, 11:32 AM
  4. [SOLVED] VBA code for converting set of item coding to full names and percentage
    By Seisouhen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-01-2012, 08:47 AM
  5. Replies: 1
    Last Post: 05-14-2012, 10:14 AM
  6. Sort through list picking out names (not repeats)
    By stevedes7 in forum Excel General
    Replies: 1
    Last Post: 10-18-2009, 02:54 AM
  7. Picking up the names that is not required using macros or formulas
    By share knowledge in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-08-2009, 05:55 AM

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