Proportial mutiplication problem

[syeda]

Dear All


my model has 100 healthy people moving over 100 cycle with constant weight of 5. Once a person gets infected, he(she) loses weight at a rate of 0.0168 at each cycle. the probability of a person moving from healthy state to infected state per cycle is 0.108.


My problem is each cycle a proportion moves from healthy to infected e.g:

[pre]

cycle healthy                  infected                 total weight
0       100                           0                   5*100
1      100-(100*(0.108))       100*0.108       5(100-(100*(0.108))+((5-0.0168)*0.108)

[/pre]
The people in the cycle 2 moving from health to infected have a drop of 0.0168 while people already in infected state have a drop of 2*0.0168.


How to write a macros for this..


PLEASE need help.

 

[Luke M]

Don't really need a macro, could do this with formulas. If this doesn't work, we'll try the macro (which in reality will just be doing the formulas itself)

Taking your example, cells A2:C2 are constants, being values 0, 100, and 0. Formula in D2:

=B2*5+C2*MAX(0,FV(-0.0168,A2,0,5))

This will calculate our total weight for us in each cycle. The MAX function is there since I'm assuming we can't have negatve weight values. =P

Formula in A3:

=A2+2

Formula in B3:

=MAX(0,ROUND(B2-B2*0.108,0))

Formula in C3:

=$B$2-B3


Copy A3:D3 down as far as needed. With starting sample of 100, by cycle 26 you'll reach a constant state with 4 heatlhy, 96 infected. Note that the weight will continue to drop.


EDIT: After re-reading, I see that the weight loss is not so much a rate decrease but a constant decrease. Formula in D2 then is:

=B2*5+C2*MAX(0,5-0.0168*A2)

 

[syeda]

Thank you so much


since the weight is constant for healthy and drops at a constant rate once a person is infected, the proportiona of people drpooing from 56 will differ each cycle i.e. each cycle fresh no. of patients will come to infected state, thier weight will drop from 56 while the weight of the already infected will drop from the previos rate


fresh infected: weight change is 56 -0.0168

while alreasdy infected will be 56 - (2* 0.0168) so the total weight will be thealthy *weight by infected _cycle1(2*0.0168+infectedcycle2(0.0168)


i dont think its working the way i think it should work.


it needs to rember the cycle number as well the proportion in each cycle under infected..


i hope i made my question more clear

 

[NARAYANK991]

Hi Syeda ,


Can you check the first few formulae in column F of this file , and confirm whether that is the kind of calculation that you want to do ?


http://speedy.sh/jrsVM/Book11111.xlsm


Narayan

 

[syeda]

Yes, thank you so much!


But i was wondering if it could be simplified because i have 500 cycles in each such 8 process.. I am trying using arrays but i dont know how each cycle number can be replace only for the that proportion...

 

[NARAYANK991]

Hi Syeda ,


The purpose of asking you to confirm the calculations was so that based on your confirmation / clarification , a macro could be written.


Please wait ; I'll post the macro or upload the file with the macro in the next few hours.


Narayan

 

[NARAYANK991]

Hi Syeda ,


Can you try this formula ?


=SUM($E$2:E2*($I$3-$I$5*(ROW(A2)-ROW($A$2:A2)+1)))


Enter this in cell F2 ( of the workbook I had posted earlier ) , as an array formula , using CTRL SHIFT ENTER.


Copy this downwards.


Alternatively , download this file :


http://speedy.sh/ta9Sv/Book11111-1.xlsx


Narayan