View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Possibly an Array, but I'm not sure?

If I understand correctly:

=SUMPRODUCT(--('2007 Discharges'!A4:A500=DATE(2008,1,1)),
--('2007 discharges'!A4:A500<=DATE(2008,3,31)),
--('2007 Discharges'!u4:u5000))

or if you have a "completed" indicator in column f):

=SUMPRODUCT(--('2007 Discharges'!A4:A500=DATE(2008,1,1)),
--('2007 discharges'!A4:A500<=DATE(2008,3,31)),
--('2007 discharges'!f4:f500="completed"),
--('2007 Discharges'!u4:u5000))




Dan the Man wrote:

That worked Dave!

The last thing I want to do is to examine data over three columns. Below is
an example:

Column A Column F Column U
(Date) (Discharge Status) (Improvement Status Raw
Scores)

Jan 1, 2008 Completed 5

Again, I will have multiple rows of data (with dates). The question I will
be attempting to answer is: "of all people in quarter one" (e.g. Jan 1-March
31, 2008) who "completed" our program, how many people showed raw score
improvement (e.g. scores of greater than 0-in Column U).

I will also be placing this outcome data, on a separate sheet, and therefore
the formula needs to link to the sheet "2007 discharges" (where the raw data
lives).

Again much appreciated with some help. My various formulas don't seem to be
working!

Dan

"Dave Peterson" wrote:

You missed the worksheet name on the second date and a few ! characters.


=SUMPRODUCT(--('2007 Discharges'!A4:A500=DATE(2008,1,1)),
--('2007 discharges'!A4:A500<=DATE(2008,3,31)),
--('2007 Discharges'!W4:W500=91%))

Dan the Man wrote:

Forgot to ask. If I put all of my data outcomes on a separate sheet, I know I
need to reference the original sheet where the raw data is coming from. The
original formula that Peo gave me works just fine, however I had difficulty
adding in the additional formula ('2007 Discharges') string when I am using a
different page to reference back. Peo's original formula was:

=SUMPRODUCT(--(A4:A500=DATE(2008,1,1)),--(A4:A500<=DATE(2008,3,31)),--(W4:W500=91%))

I tried adding in without success:

=SUMPRODUCT(--('2007 Discharges'A4:A500=DATE(2008,1,1)),--(
A4:A500<=DATE(2008,3,31)),--( '2007 Discharges'W4:W500=91%))

I'll assume I'm missing something. Again thanks!

Dan

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A50=DATE(2008,1,1)),--(A2:A50<=DATE(2008,3,31)),--(C2:C50=91%))


for quarter1 2008 greater than or equal to 91%

change the cell refs to fit your own




--


Regards,


Peo Sjoblom

"Dan the Man" wrote in message
...
Column A Column B Columm C

Row 3 Aug 1, 2008 Joe Jones 91%
Row 4 Mar 15, 2008 Mary Doe 86%

Lookinig for a formula to provide me with a statistical information by the
four quarters of the yeaer(e.g. quarter one would be Jan 1-March 30,
2008).
What I want to anaylyze is a percentage breakdown. For example, I want to
know how many individuals show outcomes at 91% or more. Thus, the formula
might generate an outcome that tells me that 15 of the total individuals
on
my spreadsheet scored at 91% or greater.

I'll also have to breakdown other percentage parameters (e.g. outcomes
between 75-89%, etc), however once I get the outline of a formula I should
be
able to do the rest. I'm thinking I need an array formula, but I just
wasn't
sure how to get what I wanted. Thanks much in advance.

Dan




--

Dave Peterson


--

Dave Peterson