Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif?
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$43300, 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? |
#2
|
|||
|
|||
Hi Anand,
Just use Pivot Tables. They can do almost all that you want. Alok Joshi "anand" wrote: 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$43300, 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |