A formula answer that I just can't wrap my brain around!

D

Dan the Man

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 are: 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
 
F

FSt1

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

Regares
FSt1
 
L

Lars-Åke Aspelin

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 are: 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
 
S

Sandy Mann

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:C20>D2:D20))&
" Got worse"

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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:C20>D2:D20))
&" Got worse"

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
D

Dan the Man

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
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
D

Dan the Man

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
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
D

Dan the Man

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

Dan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top