Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running code on a list of variables
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running code on a list of variables
On Apr 5, 10:16 am, wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running code on a list of variables
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running code on a list of variables
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to prevent code running when in a worksheet code | Excel Programming | |||
Using variables while running macro | Excel Programming | |||
inserting variables in code???? | Excel Programming | |||
Running code from list | Excel Programming | |||
Variables in VB Code | Excel Programming |