ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Possibly an Array, but I'm not sure? (https://www.excelbanter.com/excel-discussion-misc-queries/199610-possibly-array-but-im-not-sure.html)

Dan the Man[_2_]
Possibly an Array, but I'm not sure?
 
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[_2_]
Possibly an Array, but I'm not sure?
 
=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



Herbert Seidenberg
Possibly an Array, but I'm not sure?
 
Use Excel 2007
Pivot Table
Group
Label Filter
No formulas needed.
File at:
http://www.savefile.com/files/1741747

Dan the Man[_2_]
Possibly an Array, but I'm not sure?
 
I have Office 2002. Peo your formula was PERFCT and it worked. Thank you very
much

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




Dan the Man[_2_]
Possibly an Array, but I'm not sure?
 
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
Possibly an Array, but I'm not sure?
 
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

Dan the Man[_2_]
Possibly an Array, but I'm not sure?
 
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
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

Dan the Man[_2_]
Possibly an Array, but I'm not sure?
 
Thank you Dave. That did it...............Your a lifesaver
Dan

"Dave Peterson" wrote:

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


All times are GMT +1. The time now is 12:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com