Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
Not exactly sure how to come up with the proper formula, but I do know the
requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
Dan,
It isn't an array formula but I can't see anything wrong with it and it works in the way I understand you to be describing. What it will do is give an overview of any 'shift' in satisfaction levels. i.e people generally are/aren't more satisfied upon discharge. I'm not qualified to coment on whether that is of statistical significance or not. Mike "Dan the Man" wrote: Not exactly sure how to come up with the proper formula, but I do know the requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
Good point I never noticed that.
"Sandy Mann" wrote: You have to qualify the M4:M5000 range as 'Raw Data'!M4:M5000 otherwise it will look at Column M in the sheet that the formula is on. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dan the Man" wrote in message ... Not exactly sure how to come up with the proper formula, but I do know the requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
Got it! Now it works. The next step is that I want to divide the total raw
score I've obtained through the formula, but the number of individuals in a specific program. So for example in program CIC, the following formula produces a raw score of 15: =SUMPRODUCT(--('Raw Data'!$W$4:$W$5000=DATE(2008,1,1)),--('Raw Data'!$W$4:$W$5000<=DATE(2008,12,31)),--('Raw Data'!$D$4:$D$5000="CIC"),--('Raw Data'!$AC$4:$AC$5000-('Raw Data'!$M$4:$M$5000))/J6) How do I DIVIDE by the number of indvidiuals in the CIC Program (which are 3). I must be placing my divider in the wrong place as I am getting an error. Thanks for all the great feedback! Dan "Mike H" wrote: Good point I never noticed that. "Sandy Mann" wrote: You have to qualify the M4:M5000 range as 'Raw Data'!M4:M5000 otherwise it will look at Column M in the sheet that the formula is on. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dan the Man" wrote in message ... Not exactly sure how to come up with the proper formula, but I do know the requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
I'd try:
=SUMPRODUCT(--('Raw Data'!$W$4:$W$5000=DATE(2008,1,1)), --('Raw Data'!$W$4:$W$5000<=DATE(2008,12,31)), --('Raw Data'!$D$4:$D$5000="CIC"), --('Raw Data'!$AC$4:$AC$5000-'Raw Data'!$M$4:$M$5000))/J6 Dan the Man wrote: Got it! Now it works. The next step is that I want to divide the total raw score I've obtained through the formula, but the number of individuals in a specific program. So for example in program CIC, the following formula produces a raw score of 15: =SUMPRODUCT(--('Raw Data'!$W$4:$W$5000=DATE(2008,1,1)),--('Raw Data'!$W$4:$W$5000<=DATE(2008,12,31)),--('Raw Data'!$D$4:$D$5000="CIC"),--('Raw Data'!$AC$4:$AC$5000-('Raw Data'!$M$4:$M$5000))/J6) How do I DIVIDE by the number of indvidiuals in the CIC Program (which are 3). I must be placing my divider in the wrong place as I am getting an error. Thanks for all the great feedback! Dan "Mike H" wrote: Good point I never noticed that. "Sandy Mann" wrote: You have to qualify the M4:M5000 range as 'Raw Data'!M4:M5000 otherwise it will look at Column M in the sheet that the formula is on. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dan the Man" wrote in message ... Not exactly sure how to come up with the proper formula, but I do know the requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
A bit of a complicated Array that I can't figure out! HELP!
NEVER MIND. I got it. I was placing my divider in the wrong position. Silly me!
Dan "Mike H" wrote: Good point I never noticed that. "Sandy Mann" wrote: You have to qualify the M4:M5000 range as 'Raw Data'!M4:M5000 otherwise it will look at Column M in the sheet that the formula is on. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dan the Man" wrote in message ... Not exactly sure how to come up with the proper formula, but I do know the requirements that I'm looking for. If anyone could help I'd appreciate it. I have two columns of data, and 5000 rows. The Columns are (M) and (AC).. Each cell in Columns (M) and (AC) have numerical values of 5, 10 & 15 in them. Upon admission we ask individuals to rate their response to a statement, and upon discharge we re-rate them in order to see if their was a shift in their responses based upon what they learned. Thus I'd be comparing the admission response in Column (M), to the discharge response in Column (AC), by subtracting the "discharge response (AC), from the Admission response (M). The other two variables include that individuals are in different programs, and I want to also compare the responses obtained across programs. I have come up with part of a formula, but I know I don't have the entire thing accurate. Any help would be appreciated. Here's is the the array formula that have so far (which doesn't really work correctly however). =SUMPRODUCT(--('Raw Data'!W4:W5000=DATE(2008,1,1)),--('Raw Data'!W4:W5000<=DATE(2008,12,31)),--('Raw Data'!D4:D5000="CIC"),--('Raw Data'!AC4:AC5000-M4:M5000)) Thus, the Columns in the formula are taking into consideration: Date parameters (Column W), the program assignment (Column D), and the response to the question (Column AC is the discharge response, and Column M the admission response). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a little complicated | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Help with a complicated array formula | Excel Worksheet Functions | |||
complicated array | Excel Worksheet Functions |