How to get a sample with a specific % of success

[RccScln]

I am trying to modelling a leasing portfolio.
Now I have to generate a sample from the original population of the assets that will be repossessed.
Ex.
We have 10 cars. The percentage of the cars that will be repossessed is 20%.
We have 10 boats. The percentage of the boats that will be repossessed is 70%.

So I need to obtain a variable (that has value 0 if not repossess and 1 if will be repossess) that follows those rules.

Which formula should I use?

What I did (not very sophisticated) was ordering them by asset type and then, for the 20% prob I gave a value of 1 every 5 rows.

Thank you so much in advance.

Rocco

 

[dermie_72]

I'm not sure what headings you're using but if you used a sumif/countif it would give you the percentage of repossessed over total.

 

[RccScln]

Maybe I was not clear. This what I mean:

ASSET TYPE	Last Appraisal Value	Repossessable
strumentale	154.147	1
strumentale	92.052	0
strumentale	365.668	0
strumentale	2.384	0
strumentale	12.147	1
strumentale	20.049	0
strumentale	498	0
strumentale	11.473	0
strumentale	12.966	1
strumentale	50.208	0
strumentale	14.072	0
strumentale	53.619	0
strumentale	53.619	1
strumentale	997	0
strumentale	797	0
strumentale	996	0
strumentale	2.042	1
strumentale	847	0
strumentale	41.142	0
strumentale	8.199	0

<tbody>
</tbody>

Thank,
rocco

 

[dermie_72]

in that example what would you expect the output to be?

 

[shg]

One way:

	A​	B​	C​
1​	Boats​	Repo​
2​	10​	7​
3​	Boat #​	Repo?​
4​	1​	1​	B4 and down: =--(RAND() < (($B$2 - SUM(B$3:B3)) / ($A$2 - ROWS(B$3:B3) + 1)))
5​	2​	1​
6​	3​	1​
7​	4​	1​
8​	5​	0​
9​	6​	1​
10​	7​	0​
11​	8​	1​
12​	9​	1​
13​	10​	0​
14​		7​	B14: =SUM(B4:B13)

 

[dermie_72]

Hi RccScln, as you have not replied to either of us, I assume this is now resolved. I shall unsubscribe, however, should you require additional assistance, do not hesitate to message me directly.