Running code on a list of variables

[kwaldman]

Hi all,
I'm very new to VB for Excel and could use some help from experienced
users. I have a worksheet that contains the probability for developing
20 different diseases. If I know how many people are in my population,
I want to determine how many of those people will develop each
disease. Basically, I want to write one formula with a variable that
can be changed depending on the disease, for example, Num(disease) =
probability(disease) * people. The key is that I want to say something
like "run this code for my list of 20 diseases, replacing the word
'disease' above with the name of each disease.

Does this make sense, and is it easily done in VB?

Thanks very much.

 

[Mike Woodhouse]

Hi all,
I'm very new to VB for Excel and could use some help from experienced
users. I have a worksheet that contains the probability for developing
20 different diseases. If I know how many people are in my population,
I want to determine how many of those people will develop each
disease. Basically, I want to write one formula with a variable that
can be changed depending on the disease, for example, Num(disease) =
probability(disease) * people. The key is that I want to say something
like "run this code for my list of 20 diseases, replacing the word
'disease' above with the name of each disease.

Does this make sense, and is it easily done in VB?

I can't see why you would need VB, although it would be
straightforward if I understand your problem. However, I think you can
achieve what you say you want in a worksheet.

Say you have "Malaria" in cell A2 and 0.25 (as the probability) in B2.
Now let's put our population figure in C1. In C2, put the following
formula: =$C$1 * B2. If you put 1000000 in C1, then C2 should display
250000. Add diseases and probabilities in columns A and B below the
first one and copy C2 to the new rows - the calculation should update
for each, the $ signs in $C$1 will keep the reference to the
population figure pointing to the correct value.

Of course, the result is not the number who *will* develop the
disease, it's just the mathematical expectation...

HTH

Mike

 

[Damien McBain]

Hi all,
I'm very new to VB for Excel and could use some help from experienced
users. I have a worksheet that contains the probability for developing
20 different diseases. If I know how many people are in my population,
I want to determine how many of those people will develop each
disease. Basically, I want to write one formula with a variable that
can be changed depending on the disease, for example, Num(disease) =
probability(disease) * people. The key is that I want to say something
like "run this code for my list of 20 diseases, replacing the word
'disease' above with the name of each disease.

Does this make sense, and is it easily done in VB?

I don't think you need to use VBA if I understand what you are asking.
I'm assumaing you have a worksheet with 20 disaeses listed in column A and
a corresponding probability in column B (or something like that).
If this is the case, can't you just have your population number somewhere
else (say in Sheet2!A1) and a formula in each corresponding cell in column
C something like "=Sheet2!$A$1*B2"?

 

[kwaldman]

Thanks - I guess I should be more clear on what exactly I'm working
with. We have patients who will accumulate a higher risk of developing
the disease based on how much time we've been observing them. So for
example, someone who's been in the clinic for 1 year will have less of
a chance of developing the disease than someone who's been in the
clinic for 2 years. The way I've worked this out is like this:

1. Create a counter to count the number of days elapsed between the
start and end date of your observation period
2. Input a daily enrollment rate
3. Input a daily probability of developing a disease

So then we calculate how many people will get the disease in the
observation period like this:

On the first day of observation, number of cases = 1 (number of days
enrolled) * daily probability of disease * number people enrolled

But on the second day of observation, number of cases = 2 * daily
probability of disease * number of people enrolled so that the
probability becomes cululative.

I've done this using a VB do loop, and I basically want to run this
calculation for each disease without having to make a formula for
each, replacing the daily probability of disease for each disease.

Thanks in advance for any help!