• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assign a binary value to a list based on a given percentage

W

wlerner

New Member
Say I have a list of items, similar to what is below:


cat

cat

cat

cat

dog

dog

dog

dog


Given a cell where the user can input a value which represents what percentage of "cat" in the list should be assigned a one value, how would I assign "one" based on the percentage entered, then a "zero" to the remaining "cat" entries. I would then like to do the same with "dog".


Thank you all for your consideration in advance. This is one of THE most friendly and helpful forums I have ever used.
 
Hi ,


Will the percentage remain the same for all the items , or will it change from item to item ?


If it changes from item to item , how will you specify that a particular percentage value applies to one particular item ?


If the percentage will remain the same for all items , the following formula should do the job :


=IF(COUNTIF($A$1:A1,A1)<=ROUNDUP($C$1*COUNTIF($A$1:$A$14,A1),0),1,0)


where :


Your list of items extends from $A$1:$A$14


Your percentage value is in $C$1


Narayan
 
Thank you Narayank for the quick reply. The percentage will change from item to item. I thought that the specification could be computed by Excel, i.e. if I want 76% of the items to have a "one" assigned to them, then the remaining 24% should have a "zero" assigned to them. It is my intention to create the list then "shuffle" it so I have a random representation of possibilities.


Does this sound feasible to you? Thanks again!
 
I think Narayan's formula will do what you want. Changing your list a little so the quantities are different

[pre]
Code: 
Item	Marking	Percentage
cat	1	50%
cat	1
cat	0
cat	0
dog	1
dog	1
dog	1
dog	0
dog	0
dog	0
bird	1
bird	0
sheep	1
[/pre]
You'll see that half (50%) of each respective item has been marked with 1. If this isn't what you wanted, could you show us a quick example of what you'd like to see?
 
Thank you Luke, that post was most helpful. I was hoping to see the percentage not applied to the whole list, but each individual element has its own percentage. For example, mark 50% of the cats 1, the other 50% of the cats 0. Mark 25% of the cats 1, the other 75% 0. Does this help?


Thanks again for your previous post!
 
Something is not working correctly with the formula. When I paste it into a sheet, is provides only all 1s or all 0s, regardless of the percentage in C1.


Thanks again everyone!
 
Ah! Formula works, but the percentage must be between 0 and 1, not a whole number! Thank you! Now the only problem is that the list is not in order and the percentage cahnges from item to item.
 
Hi ,


Based on the data sample you gave in your first post , an answer has been suggested ; if this answer is not what you were looking for , talking about problems with the answer is not the way forward.


If you can upload your workbook , or even give a larger sample of data , along with the output that you expect , that will certainly help.


Let me again put your problem in my own words ; please confirm or clarify.


1. You have a list of items ; is this sorted or not ?


2. Let us assume it is sorted ; let us now assume that there are the following entries occurring in the list - cat 3 times , dog 5 times , lion 7 times i.e. the list is cat cat cat dog dog dog dog dog lion lion lion lion lion lion lion.


3. Suppose you enter a percentage of 30 % , by entering 0.3


4. Is this 0.3 to be applied to all the 3 items ( cat , dog and lion ) or is it to be applied to only one item ? If it is only one item , then which one ?


5. Suppose the 30 % is to be applied to each of the 3 items , it means 1 out of 3 cats will be marked 1 , and the remaining 2 cats will be marked 0. How many dogs and how many lions should be marked 1 ?


6. Suppose the 30 % is to be applied to all the items , we have a total of 15 items ( 3 + 5 + 7 ). Now , should the first 5 items be marked 1 , and the remaining 10 marked 0 ?


Narayan
 
Hi, wlerner!


Just passing by, read the topic, title sounded interesing, played a little with the examples, and I fully agree with Luke M: NARAYANK991's formula works fine and smooth as for the requirements you posted. I think that if you can't explain your issue in other words it'd be very useful if you could upload a sample file with an example covering all possibilities and manually written the output desired in column B.


Regards!


@NARAYANK991

Hi!

Just fyi.

Regards!
 
@wlerner

You could modify the formula to choose different amounts for each item.

Assuming a table like this:

[pre]
Code: 
Item	Marking	Table	Percent
cat	1	cat	50%
cat	1	dog	10%
cat	0	sheep	50%
cat	0	bird	100%
dog	1
dog	0
dog	0
dog	0
dog	0
dog	0
bird	1
bird	1
sheep	1
[/pre]
Formula in B2 would be:

=IF(COUNTIF($A$2:A2,A2)<=ROUNDUP(VLOOKUP(A2,$C$2:$D$5,2,0)*COUNTIF($A$2:$A$14,A2),0),1,0)

where C2:D5 is the table shown.
 
Solved! Luke M provided the solution which does what I want. I modified the formula to use the cells I was using, and this worked like a charm. I appreciate everyones input and want to thank all of you very much! Narayank provided the foundation for the formula and Luke Ms modification solved my issue.


Thank you Excel Ninjas!!!
 
You must log in or register to reply here.
Back
Top