bootstrap

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

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.

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.

Please Login or Register  to view this content.

I hope this helps.