I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

=IF(Data!D2:D687<=100,COUNTIF(Data!I2:I687,"0")+C OUNTIF(Data!I2:I687,"n")+COUNTIF(Data!I2:I687,"d") +COUNTIF(Data!J2:J687, "0"),0)
ctrl+shift+enter, not just enter


"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

Thanks for the prompt response. I'll see if this does the trick.

"Teethless mama" wrote:

=IF(Data!D2:D687<=100,COUNTIF(Data!I2:I687,"0")+C OUNTIF(Data!I2:I687,"n")+COUNTIF(Data!I2:I687,"d") +COUNTIF(Data!J2:J687, "0"),0)
ctrl+shift+enter, not just enter


"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

The formula below doesn't work correctly. It provides the value if FALSE,
i.e., 0.

It should yield a value of 153 for those cases with a visit interval <=100.

"Teethless mama" wrote:

=IF(Data!D2:D687<=100,COUNTIF(Data!I2:I687,"0")+C OUNTIF(Data!I2:I687,"n")+COUNTIF(Data!I2:I687,"d") +COUNTIF(Data!J2:J687, "0"),0)
ctrl+shift+enter, not just enter


"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

I would add another column
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT



Oldersox wrote:
I would add another column
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

I agree with your answer but based my reply on the need to "focus my efforts
first on people who are
being monitored more closely" and as such thought the creation of a list to
target patients from the existing data file would be of use. Using a pivot
table will provide calculation of percentages and patient numbers for each
group and or a chart presentation of data.


"bda75" wrote:

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT



Oldersox wrote:
I would add another column
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.

The pivot table solution seems to be the one that worked. Instead of trying
to use an IF function with embedded COUNTIF functions, I switched to the
DCOUNt function. Here's the syntax:
=DCOUNT(Data!D1:J687,"EF",Results!J23:K24)+DCOUNT( Data!D1:K687,"NYSHC",Results!J23:K24)

I selected the area of the worksheet that contained the column with the
visit interval data, column D, as well as the column containing the data for
the information I was screening, column J with the EF or 'ejection
fraction"--a measure of the heart's pumping capacity. Then, I created a
reference table for the various levels of visit interval, i.e., <=100, etc.
From here, it was a pretty simple procedure.

I now have a new tool to add to my Excel arsenal.

Cheers.

Kurt

"Oldersox" wrote:

I agree with your answer but based my reply on the need to "focus my efforts
first on people who are
being monitored more closely" and as such thought the creation of a list to
target patients from the existing data file would be of use. Using a pivot
table will provide calculation of percentages and patient numbers for each
group and or a chart presentation of data.


"bda75" wrote:

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT



Oldersox wrote:
I would add another column
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.