I received great help on an early formula, and I'm trying to analyze my data
one other way, and can't seem to think of the best way to write out what I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at Improvement,
stayed the same or got worse for individuals in our CIC program separately
from individuals in TP or OP. What I want to obtain is a raw score value that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and 1
got worse. I'll do this for the individuals in OP and TP separately, but once
I get one formula I should be able to figure out the others. HELP, I can't
wrap my mind around it. I'm grabbling the data off of one sheet (entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I need
to reference/qualify the raw data sheet using the above string of formula as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan

hi
if i understand correctly, try something like this....
=IF(D5C5,"Got Better",IF(D5=C5,"Stayed Same","Got worse"))
adjust cell references as needed.

Regares
FSt1

"Dan the Man" wrote:

I received great help on an early formula, and I'm trying to analyze my data
one other way, and can't seem to think of the best way to write out what I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at Improvement,
stayed the same or got worse for individuals in our CIC program separately
from individuals in TP or OP. What I want to obtain is a raw score value that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and 1
got worse. I'll do this for the individuals in OP and TP separately, but once
I get one formula I should be able to figure out the others. HELP, I can't
wrap my mind around it. I'm grabbling the data off of one sheet (entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I need
to reference/qualify the raw data sheet using the above string of formula as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan

On Sat, 23 Aug 2008 09:14:01 -0700, Dan the Man
wrote:

I received great help on an early formula, and I'm trying to analyze my data
one other way, and can't seem to think of the best way to write out what I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at Improvement,
stayed the same or got worse for individuals in our CIC program separately
from individuals in TP or OP. What I want to obtain is a raw score value that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and 1
got worse. I'll do this for the individuals in OP and TP separately, but once
I get one formula I should be able to figure out the others. HELP, I can't
wrap my mind around it. I'm grabbling the data off of one sheet (entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I need
to reference/qualify the raw data sheet using the above string of formula as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan



Try the following formula to get the number of CICs that Improved:

=SUMPRODUCT(--('Raw Data'!B1:B100="CIC")*('Raw Data'!D1:D100'Raw
Data'!C1:C100))

Change the to = and < to get the number of "stayed the same" and
"got worse"

Change 100 to reflect the size of your data table on sheet 'Raw Data'.

"CIC" can be replaced to a reference to a cell where the name of the
program is stored.

Hope this helps. / Lars-Åke

If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze my
data
one other way, and can't seem to think of the best way to write out what I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program separately
from individuals in TP or OP. What I want to obtain is a raw score value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and
1
got worse. I'll do this for the individuals in OP and TP separately, but
once
I get one formula I should be able to figure out the others. HELP, I can't
wrap my mind around it. I'm grabbling the data off of one sheet (entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I
need
to reference/qualify the raw data sheet using the above string of formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze my
data
one other way, and can't seem to think of the best way to write out what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and
1
got worse. I'll do this for the individuals in OP and TP separately, but
once
I get one formula I should be able to figure out the others. HELP, I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I
need
to reference/qualify the raw data sheet using the above string of formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan

Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10 questions,
accross 5 programs, so I was able to use your formula to get EXACTLY what I
need. I appreciate the time you took to answer my question and provide me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze my
data
one other way, and can't seem to think of the best way to write out what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same, and
1
got worse. I'll do this for the individuals in OP and TP separately, but
once
I get one formula I should be able to figure out the others. HELP, I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know I
need
to reference/qualify the raw data sheet using the above string of formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the formula!

Dan

You are very welcome, Im glad that it worked for you.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10
questions,
accross 5 programs, so I was able to use your formula to get EXACTLY what
I
need. I appreciate the time you took to answer my question and provide me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze my
data
one other way, and can't seem to think of the best way to write out
what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got
worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score
value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same,
and
1
got worse. I'll do this for the individuals in OP and TP separately,
but
once
I get one formula I should be able to figure out the others. HELP, I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know
I
need
to reference/qualify the raw data sheet using the above string of
formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the
formula!

Dan

The only other thing I'd like to do is to limit raw score data entry into
certain cells. (J4:J5000-S4:S5000, and Z4:Z5000-AI4:AI5000) that should allow
only certain raw scores. The rating scale we've developed allows for scores
of 5, 10 or 15 only. Unfortunately, some of our secretarial staff types fast
and makes errors. When checking their data, I often find accidental entries
of 0, 4, 16, etc. If there is a way (possibly using vba I suspect) to only
allow scores of 5, 10, or 5 to be entered into columns J, S, Z, and AI this
would prevent accidental entry of numbers that shouldn't be input.

I had this idea of a pop up message alert advising the data entry person
that only scores of 5, 10, and 15 can be input. Any suggestions?

Thanks much Dan

"Sandy Mann" wrote:

You are very welcome, Im glad that it worked for you.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10
questions,
accross 5 programs, so I was able to use your formula to get EXACTLY what
I
need. I appreciate the time you took to answer my question and provide me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze my
data
one other way, and can't seem to think of the best way to write out
what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got
worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score
value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the same,
and
1
got worse. I'll do this for the individuals in OP and TP separately,
but
once
I get one formula I should be able to figure out the others. HELP, I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I know
I
need
to reference/qualify the raw data sheet using the above string of
formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the
formula!

Dan

If I understand you correctly then Data Validation should do that you want.

Hightlight the range that you want then select Data Validation Allow
Custom and enter the formula:

=OR(C2=5,C2=10,C2=15)

then OK

Ensure that if you click on the cell to insert the reference Excel does not
enter an absolute references.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
The only other thing I'd like to do is to limit raw score data entry into
certain cells. (J4:J5000-S4:S5000, and Z4:Z5000-AI4:AI5000) that should
allow
only certain raw scores. The rating scale we've developed allows for
scores
of 5, 10 or 15 only. Unfortunately, some of our secretarial staff types
fast
and makes errors. When checking their data, I often find accidental
entries
of 0, 4, 16, etc. If there is a way (possibly using vba I suspect) to only
allow scores of 5, 10, or 5 to be entered into columns J, S, Z, and AI
this
would prevent accidental entry of numbers that shouldn't be input.

I had this idea of a pop up message alert advising the data entry person
that only scores of 5, 10, and 15 can be input. Any suggestions?

Thanks much Dan

"Sandy Mann" wrote:

You are very welcome, Im glad that it worked for you.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10
questions,
accross 5 programs, so I was able to use your formula to get EXACTLY
what
I
need. I appreciate the time you took to answer my question and provide
me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze
my
data
one other way, and can't seem to think of the best way to write out
what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at
Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5
15
(Improvement)
Sep 15, 08 TP 10
10
(Stayed the Same)
Sep 21, 08 CIC 10
5
(Got Worse)
Sep 22, 08 CIC 5
5
(Stayed the Same)
Sep 22, 08 TP 15
10
(Got Worse)

What I want to compare a improvement, stayed the same, and got
worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score
value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the
same,
and
1
got worse. I'll do this for the individuals in OP and TP
separately,
but
once
I get one formula I should be able to figure out the others. HELP,
I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I
know
I
need
to reference/qualify the raw data sheet using the above string of
formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the
formula!

Dan

I forgot to add: put an error mesaage in the error message tab before you
click OK.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I understand you correctly then Data Validation should do that you
want.

Hightlight the range that you want then select Data Validation Allow
Custom and enter the formula:

=OR(C2=5,C2=10,C2=15)

then OK

Ensure that if you click on the cell to insert the reference Excel does
not enter an absolute references.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
The only other thing I'd like to do is to limit raw score data entry into
certain cells. (J4:J5000-S4:S5000, and Z4:Z5000-AI4:AI5000) that should
allow
only certain raw scores. The rating scale we've developed allows for
scores
of 5, 10 or 15 only. Unfortunately, some of our secretarial staff types
fast
and makes errors. When checking their data, I often find accidental
entries
of 0, 4, 16, etc. If there is a way (possibly using vba I suspect) to
only
allow scores of 5, 10, or 5 to be entered into columns J, S, Z, and AI
this
would prevent accidental entry of numbers that shouldn't be input.

I had this idea of a pop up message alert advising the data entry person
that only scores of 5, 10, and 15 can be input. Any suggestions?

Thanks much Dan

"Sandy Mann" wrote:

You are very welcome, Im glad that it worked for you.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10
questions,
accross 5 programs, so I was able to use your formula to get EXACTLY
what
I
need. I appreciate the time you took to answer my question and provide
me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in
message
...
I received great help on an early formula, and I'm trying to
analyze my
data
one other way, and can't seem to think of the best way to write
out
what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at
Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5 15
(Improvement)
Sep 15, 08 TP 10 10
(Stayed the Same)
Sep 21, 08 CIC 10 5
(Got Worse)
Sep 22, 08 CIC 5 5
(Stayed the Same)
Sep 22, 08 TP 15 10
(Got Worse)

What I want to compare a improvement, stayed the same, and got
worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score
value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the
same,
and
1
got worse. I'll do this for the individuals in OP and TP
separately,
but
once
I get one formula I should be able to figure out the others. HELP,
I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I
know
I
need
to reference/qualify the raw data sheet using the above string of
formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the
formula!

Dan

works like a charm..............Thanks Sandy. I also got a second idea. I
love options, lol!

Dan

"Sandy Mann" wrote:

If I understand you correctly then Data Validation should do that you want.

Hightlight the range that you want then select Data Validation Allow
Custom and enter the formula:

=OR(C2=5,C2=10,C2=15)

then OK

Ensure that if you click on the cell to insert the reference Excel does not
enter an absolute references.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
The only other thing I'd like to do is to limit raw score data entry into
certain cells. (J4:J5000-S4:S5000, and Z4:Z5000-AI4:AI5000) that should
allow
only certain raw scores. The rating scale we've developed allows for
scores
of 5, 10 or 15 only. Unfortunately, some of our secretarial staff types
fast
and makes errors. When checking their data, I often find accidental
entries
of 0, 4, 16, etc. If there is a way (possibly using vba I suspect) to only
allow scores of 5, 10, or 5 to be entered into columns J, S, Z, and AI
this
would prevent accidental entry of numbers that shouldn't be input.

I had this idea of a pop up message alert advising the data entry person
that only scores of 5, 10, and 15 can be input. Any suggestions?

Thanks much Dan

"Sandy Mann" wrote:

You are very welcome, Im glad that it worked for you.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
Sandy!

THANK YOU SO VERY MUCH. Your formula did it. I have a total of 10
questions,
accross 5 programs, so I was able to use your formula to get EXACTLY
what
I
need. I appreciate the time you took to answer my question and provide
me
with formula help.

Best,

Dan

"Sandy Mann" wrote:

Unfortunate line breaks try:

="CIC: "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))
&" Improved, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))
&" Stayed the Same, "&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),
--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))
&" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
If I follow you then try:

="CIC:
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20<D2:D20))&"
Improved,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20=D2:D20))&"
Stayed the Same,
"&SUMPRODUCT(--(A2:A20=DATE(2008,9,1)),--(A2:A20<=DATE(2008,9,30)),--(B2:B20="CIC"),--(C2:C20D2:D20))&
" Got worse"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dan the Man" wrote in message
...
I received great help on an early formula, and I'm trying to analyze
my
data
one other way, and can't seem to think of the best way to write out
what
I
need. Here goes an example. Any help would be greatly appreciated:

Disch Program Q #1 Q #1
Date at Admit at
Disch

Column A Column B Column C Column D

Sep 1, 08 CIC 5
15
(Improvement)
Sep 15, 08 TP 10
10
(Stayed the Same)
Sep 21, 08 CIC 10
5
(Got Worse)
Sep 22, 08 CIC 5
5
(Stayed the Same)
Sep 22, 08 TP 15
10
(Got Worse)

What I want to compare a improvement, stayed the same, and got
worse
within each program. Thus, in the above example, I would look at
Improvement,
stayed the same or got worse for individuals in our CIC program
separately
from individuals in TP or OP. What I want to obtain is a raw score
value
that
would tell me:

Of the (3) CIC individuals discharged: 1 Improved, 1 stayed the
same,
and
1
got worse. I'll do this for the individuals in OP and TP
separately,
but
once
I get one formula I should be able to figure out the others. HELP,
I
can't
wrap my mind around it. I'm grabbling the data off of one sheet
(entitle:
'Raw Data'!), and placing it on another sheet (outcomes). Thus I
know
I
need
to reference/qualify the raw data sheet using the above string of
formula
as
I attempt to generate the information on my outcomes sheet.

Any suggestions? Hope I provided enough information to solve the
formula!

Dan