# Picking names by percentage

1. ## 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
There's got to be a better/easier way to do this.

2. ## 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. ## 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?

4. ## 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.

5. ## 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.

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

#### 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