bootstrap

[daniviva]

Hi all!

I'm having problem in performing bootstrapping with excel, WITHOUT downloading extensions of the program.

My data are the folowinf ones:
I have one insect community made of 3 species, with different abundances:

species A --> 13 insects
species B --> 53 insects
species C --> 20 insects

TOT number of insects in the community: 86

From this community I have to drawn 1000 random samples, and the formula would be:

=INDEX(community;ROWS(community)*RAND()+1;COLUMNS(community)*RAND()+1)

with "community" as my data (13,53,20)

That works well, BUT my problem is that tha sample I have to take don't have to have 86 total insects, but less (50), so I need a random SAMPLE from the above community, for istance a good output would be:

species A --> 35 insects
species B --> 2 insects
species C --> 13 insects

TOT insects: 50

and this would be repeated randomly 1000 times

Do you have any idea how to put the total number of insect constraint?
Hope my headhache will be finally over!!!!!!

Thanks guys!!

Daniele

[mikerickson]

I think you are trying to:
Choose a random number of Species A, a random number of Species B and a random number of Species C.
I'm reading that A+B+C is constrained to be 50.

And do this 1000 times.

If that is what is needed, you could

Put "Tot" in E1 and 50 in K2.
This is where you input the total number of insects required.

in E2,
=INT(RAND()*($K$2-2))+1

in F2,
=INT(RAND()*($K$2-$E2))+1

in G2
=$K$2-E2-F2

This randomly chooses three numbers, E2:G2, whose sum is $K$2. It has a tendency to choose E2 larger than the others, so to correct for that by;
In I2, we choose one of the six ways to reorder 3 numbers.
In I2,
=INT(RAND()*6)+1

In A2, the number of species A,
=CHOOSE(I2,E2,E2,F2,F2,G2,G2)

in B2, the number of species B,
=CHOOSE(I2,F2,G2,E2,G2,E2,F2)

in C2, the number of species C,
=CHOOSE(I2,G2,F2,G2,E2,F2,E2)

Select A2:I1001 and fill down.

[daniviva]

Hi mikerickson!!

Good things:
thanks so much for your answer, you're a master!!


Bad things:
I made a mistake in my first message... what I need is that if my community is:

species A --> 13 insects
species B --> 53 insects
species C --> 20 insects

TOT number of insects in the community: 86

my 1000 random sample have to take randomly 50 insects from this pool, so that I can't take 35 insects from species A because I only have 13 of these (that's my mistake in the previous msg )). In this way, each species is constrained by ITS number (13 for A, 53 for B and 20 for C), and the total sample size has to be 50. Example:

species A --> 5 insects
species B --> 34 insects
species C --> 11 insects

TOT number of insects in the random sample: 50

Hoping this is not a big problem for Excel....

sorry for my previous msg!!

Daniele

[Richard Schollar]

Hello Daniele

In the attached workbook I've provided one way I think you can do this - the random population is made to sum to 50 by setting the number of Species B to equal 50 less the sum of species A & C. I have used RANDBETWEEN rather than RAND to make my formulas look prettier - RANDBETWEEN is from the Analsysis Toolpak and you would need to ensure this has been referenced through Tools>Add-Ins (it ships with Excel, in most cases).

Richard

[mikerickson]

I think this will do it.

Put the number of species A in A1, species B in B1, species C in C1.

From your figures that would be A1=13, B1=53, C1=20

Leave D1 blank.

Put the total sample size you want in E1. E1 = 50

Run this macro and 1000 random choices will be made and counted.
After the macro is run, G1:I100 will show the totals for each species per choice.

Sub randomBugs()
Dim allBugs() As String, bugCount As Long
Dim sampleSize As Long, numSelections As Long
Dim aCount As Long, bCount As Long, cCount As Long
Dim outRRay() As Double
Dim writeRange As Range
Dim i As Long, temp As String, rowNum As Long
Dim pointer As Long, randIndex As Long

Rem adjust the sheet name as needed
Set writeRange = ThisWorkbook.Sheets("Sheet1").Range("g1")

aCount = writeRange.Parent.Range("a1")
bCount = writeRange.Parent.Range("b1")
cCount = writeRange.Parent.Range("c1")
bugCount = aCount + bCount + cCount
sampleSize = writeRange.Parent.Range("E1")
numSelections = 1000

Rem fill allBugs {a,a,..,b,b,...,c,c...}
ReDim allBugs(1 To bugCount)
pointer = 1
For i = 1 To aCount
    allBugs(pointer) = "a"
    pointer = pointer + 1
Next i
For i = 1 To bCount
    allBugs(pointer) = "b"
    pointer = pointer + 1
Next i
For i = 1 To cCount
    allBugs(pointer) = "c"
    pointer = pointer + 1
Next i

  ReDim outRRay(1 To numSelections, 1 To 3)
Rem do 1000 times
For rowNum = 1 To numSelections

    Rem choose some bugs
    For i = 1 To sampleSize
        Randomize
        randIndex = 1 + Int(Rnd() * bugCount)
        temp = allBugs(i)
        allBugs(i) = allBugs(randIndex)
        allBugs(randIndex) = temp
    Next i
    
    Rem count them
    For i = 1 To sampleSize
        Select Case allBugs(i)
            Case "a"
                outRRay(rowNum, 1) = outRRay(rowNum, 1) + 1
            Case "b"
                outRRay(rowNum, 2) = outRRay(rowNum, 2) + 1
            Case "c"
                outRRay(rowNum, 3) = outRRay(rowNum, 3) + 1
         End Select
    Next i
Next rowNum

writeRange.Resize(UBound(outRRay, 1), UBound(outRRay, 2)).Value = outRRay

End Sub

I hope this helps.

[daniviva]

well....thanks all guys!!! I think this forum is a sort of nest of large-brained genius.....

Bye!!
Daniele