+ Reply to Thread
Results 1 to 5 of 5

Picking names by percentage

  1. #1
    Registered User
    Join Date
    05-04-2015
    Location
    Atlanta GA
    MS-Off Ver
    2010
    Posts
    44

    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
    2016
    Posts
    3,616

    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
    9,481

    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 Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    22,925

    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

    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 Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    4,040

    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 Sambo kid; 12-22-2017 at 01:47 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam C

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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