G
Guest
I have a large block of data on a sheet from a1 to IV 3000. Each row
represents one person, with the first 30 columns representing epidemiologic
identifiers (e.g. age, sex, socioeconomic status etc). This block of data is
constantly growing with the addition of new people to the database.
I have analyses I need to do on this block of data which I can do by placing
key formulas into a group of cells below the block of data (or on another
worksheet would be better). There may be several hundred of these formulas
looking at different aspects of the dataset.
However, also have to look at this data according to many differnt
epidemiologic combinations (e.g. all male patients over the age of 50 in the
lowest 1/3 income range). There are dozens (almost 100) of these variations.
I can do this by adding a countif or sumif function to the basic analytic
formulas. As below
=COUNT(IF($A$2:$A$4330>=($A$4344),IF($A$2:$A$4330<($A$4345),
IF(BD$2:BD$4330>0, IF(BD$2:BD$4330<2.2,BD$2:BD$4330)))))
However, manually keying in these formulas for every indivdual condition is
tedious since each combination of the filter function and calculation is
unique i.e. for every one of the several hundred formulas, I would have to
key in the filter manually in each formula.
The question is whether there is a way to separate the filter function
(countif, sumif) from the cell's formula but have the formula in the cell
refer to another location to extract the formula. So essentially, the given
cell would say "Do this calculation but get the filter function from that
cell over there". If I can do that (and do it iin a way that the "cell over
there" floats depending on the position of the new block of
calculation/analytic cells, then I can just copy the analytic block of cells
with formulas, make sure that the cell containing the filter directions stays
in the same position relative to the basic formula cells and the result
should be that I can do this analysis much more easily. In addition, I
should be able to add data into the database and when formula cells move
(because of the addition of rows into the full database), Excel should adjust
and still give me good data. Or I can do on a separate page but things
should still adjust?
represents one person, with the first 30 columns representing epidemiologic
identifiers (e.g. age, sex, socioeconomic status etc). This block of data is
constantly growing with the addition of new people to the database.
I have analyses I need to do on this block of data which I can do by placing
key formulas into a group of cells below the block of data (or on another
worksheet would be better). There may be several hundred of these formulas
looking at different aspects of the dataset.
However, also have to look at this data according to many differnt
epidemiologic combinations (e.g. all male patients over the age of 50 in the
lowest 1/3 income range). There are dozens (almost 100) of these variations.
I can do this by adding a countif or sumif function to the basic analytic
formulas. As below
=COUNT(IF($A$2:$A$4330>=($A$4344),IF($A$2:$A$4330<($A$4345),
IF(BD$2:BD$4330>0, IF(BD$2:BD$4330<2.2,BD$2:BD$4330)))))
However, manually keying in these formulas for every indivdual condition is
tedious since each combination of the filter function and calculation is
unique i.e. for every one of the several hundred formulas, I would have to
key in the filter manually in each formula.
The question is whether there is a way to separate the filter function
(countif, sumif) from the cell's formula but have the formula in the cell
refer to another location to extract the formula. So essentially, the given
cell would say "Do this calculation but get the filter function from that
cell over there". If I can do that (and do it iin a way that the "cell over
there" floats depending on the position of the new block of
calculation/analytic cells, then I can just copy the analytic block of cells
with formulas, make sure that the cell containing the filter directions stays
in the same position relative to the basic formula cells and the result
should be that I can do this analysis much more easily. In addition, I
should be able to add data into the database and when formula cells move
(because of the addition of rows into the full database), Excel should adjust
and still give me good data. Or I can do on a separate page but things
should still adjust?