Possibly an Array, but I'm not sure?

[Dan the Man]

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

 

[Peo Sjoblom]

=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

 

[Herbert Seidenberg]

Use Excel 2007
Pivot Table
Group
Label Filter
No formulas needed.
File at:
http://www.savefile.com/files/1741747

 

[Dan the Man]

I have Office 2002. Peo your formula was PERFCT and it worked. Thank you very
much

Dan

 

[Dan the Man]

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

 

[Dave Peterson]

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]

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]

If I understand correctly:

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

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:u500>0))

 

[Dan the Man]

Thank you Dave. That did it...............Your a lifesaver
Dan