Simulation problem

[jane11]

Question not needed.

[Jacc]

I think the policy here is not to do people's homework/assignments for them.
However, if you get started yourself you can ask for help on details and get hints along the way.

[Crooza]

Sounds like homework. Happy to help if you want to post your thoughts up. Pretty much worked out in my head how I'd do it. Only thing is have you given us the full question? The reason I ask is how do you calculate the last part - the profit when there's no $ amounts?

[Kaper]

Hi Jane,

Ill do the step by step description - but will not publish the attachment, and final part, beacause as Jacc and Crooza noticed - to me it also looks loke a homework,

To do it with functions I'd use following approach:

Your steps will be in rows, while fields in columns

Let's assume (no data on this) that in "step 0" no fields were infected

The simulation shall base mostly on RAND function.

Let's leave first row and 2 first columns column empty (see below why) and start in C2 (and copy right to N2) with (easy) infection equation for step1:

Please Login or Register  to view this content.

Hope it is clear that this would return 1 if field is infected and 0 if not. (originally thought about reverse, but this will make life easier with next steps and only a bit harder with yield)

Let's think about step 2. Here we again have the same wider environment factor, but two next factors, which depend on previous step of simulation.
Any od these 3 will cause infection, so we shall consider OR between these 3 conditions.

Let's start from cell N3 (because it has a firld one and two fielsd upstream). The formula:

Please Login or Register  to view this content.

shall simulate chances that the field is infected because of any of 3 factors.

We can copy it left at least to E3 but ... as B2 is empty (for excel meaning the same as zero) in this case last argument in OR (infection from n-2) will never lead to infection of this field. So can copy also to D3. And in C3 too - (we look at both A2 and B2 (both empty - so no infection from these "artifitial field-1 and field0").

Moreover - we can also copy this formula to row 2 (first stage of simulation, because stage 0 was empty - no field infected before simulation starts).

So now we have in C2 formula:

Please Login or Register  to view this content.

Copy it to all fields in all simulation steps (here is my favourite way to do it effectively with such a big range):
- cell C2 selected
- Ctrl+C
- Ctrl+G
- C2:N1001 written in "Reference", <Enter>
- Ctrl+V

The yield from particular field on particular step could be written (for instance in P2) as:

Please Login or Register  to view this content.

and again - copy it to P2:AA1001.

Calculating total, mean, SD etc. shall be pretty easy now.

Hope this was not only solution, but some teaching too.