Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count if (possibly?!) | Excel Worksheet Functions | |||
Please help! - Possibly need a Macro? | Excel Worksheet Functions | |||
HELP with function, possibly code! | Excel Worksheet Functions | |||
Lookup possibly? | Excel Discussion (Misc queries) | |||
If Statement possibly? | Excel Worksheet Functions |