Results 1 to 5 of 5

How to get a sample with a specific % of success

This is a discussion on How to get a sample with a specific % of success within the Excel Questions forums, part of the Question Forums category; I am trying to modelling a leasing portfolio. Now I have to generate a sample from the original population of ...

  1. #1
    New Member
    Join Date
    Jul 2016
    Posts
    15

    Default How to get a sample with a specific % of success

    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

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,503

    Default Re: How to get a sample with a specific % of success

    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.
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  3. #3
    New Member
    Join Date
    Jul 2016
    Posts
    15

    Default Re: How to get a sample with a specific % of success

    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

    Thank,
    rocco

  4. #4
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,503

    Default Re: How to get a sample with a specific % of success

    in that example what would you expect the output to be?
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  5. #5
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,150

    Default Re: How to get a sample with a specific % of success

    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)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com