Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
I need a formula which looks at the following factors: (Column U) clients who
showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
On a quick look... that last term is being multiplied by the SUMPRODUCT...
it is not part of it, so the array range it contains is not being evaluated by it. I think you may want this... =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand"),--('Raw Data'!$Y$4:$Y$5000="completed")) -- Rick (MVP - Excel) "Dan the Man" wrote in message ... I need a formula which looks at the following factors: (Column U) clients who showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
Your problem is you have two *and* conditions for Column F. Column F cannot
be "Mand" and "Eval-Mand" at the same time. I assume you wanted an *or* condition for column F? Col U has to be 0, Col F has to be either Mand or Eval-Mand, and Col Y is "completed"? If so, try: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--(('Raw Data'!$F$4:$F$5000="Mand")+('Raw Data'!$F$4:$F$5000="Eval-Mand")0),--('Raw Data'!$Y$4:$Y$5000="completed")) Which could also be expressed =SUMPRODUCT(('Raw Data'!$U$4:$U$50000)*('Raw Data'!$F$4:$F$5000={"Mand","Eval-Mand"})*('Raw Data'!$Y$4:$Y$5000="completed")) "Dan the Man" wrote: I need a formula which looks at the following factors: (Column U) clients who showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
Hey Rick, thank you for your suggestion. I forgot to mention that the
"Eval-Mand" clients are not tracked for improvement (only those identified as "Mand" are). Sorry about forgetting that important piece. Thus the array should be evaluating: (a) Mandated clients who completed treatment and showed improvement, and (b) Evaluation mandated clients who merely completed treatment. When checking the math by hand I know that I have 222 mandated clients who completed treatment and showed improvement, and 64 evaluation mandated clients who completed treatment. Thus, my formula should result with a raw score of 286. Again, I apologize for leaving that piece of critical information out. I'm a bit of a "space case" today............Dan "Rick Rothstein" wrote: On a quick look... that last term is being multiplied by the SUMPRODUCT... it is not part of it, so the array range it contains is not being evaluated by it. I think you may want this... =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand"),--('Raw Data'!$Y$4:$Y$5000="completed")) -- Rick (MVP - Excel) "Dan the Man" wrote in message ... I need a formula which looks at the following factors: (Column U) clients who showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
based on your follow up to Rick - do you want "Mand" clients who showed
improvement and completed the program plus "Eval-Mand" clients who completed the program? You could try: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$Y$4:$Y$5000="completed"))+SUMPRODUCT(--('Raw Data'!$F$4:$F$5000="Eval-Mand"),--('Raw Data'!$Y$4:$Y$5000="completed")) although there may be a better or shorter way to do it "JMB" wrote: Your problem is you have two *and* conditions for Column F. Column F cannot be "Mand" and "Eval-Mand" at the same time. I assume you wanted an *or* condition for column F? Col U has to be 0, Col F has to be either Mand or Eval-Mand, and Col Y is "completed"? If so, try: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--(('Raw Data'!$F$4:$F$5000="Mand")+('Raw Data'!$F$4:$F$5000="Eval-Mand")0),--('Raw Data'!$Y$4:$Y$5000="completed")) Which could also be expressed =SUMPRODUCT(('Raw Data'!$U$4:$U$50000)*('Raw Data'!$F$4:$F$5000={"Mand","Eval-Mand"})*('Raw Data'!$Y$4:$Y$5000="completed")) "Dan the Man" wrote: I need a formula which looks at the following factors: (Column U) clients who showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem. Anyone up for the challenge, lol?
Just got home. Thanks for all the help everyone...................I
appreciate having this group.............You all have saved my life more than once.............Dan "JMB" wrote: based on your follow up to Rick - do you want "Mand" clients who showed improvement and completed the program plus "Eval-Mand" clients who completed the program? You could try: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$Y$4:$Y$5000="completed"))+SUMPRODUCT(--('Raw Data'!$F$4:$F$5000="Eval-Mand"),--('Raw Data'!$Y$4:$Y$5000="completed")) although there may be a better or shorter way to do it "JMB" wrote: Your problem is you have two *and* conditions for Column F. Column F cannot be "Mand" and "Eval-Mand" at the same time. I assume you wanted an *or* condition for column F? Col U has to be 0, Col F has to be either Mand or Eval-Mand, and Col Y is "completed"? If so, try: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--(('Raw Data'!$F$4:$F$5000="Mand")+('Raw Data'!$F$4:$F$5000="Eval-Mand")0),--('Raw Data'!$Y$4:$Y$5000="completed")) Which could also be expressed =SUMPRODUCT(('Raw Data'!$U$4:$U$50000)*('Raw Data'!$F$4:$F$5000={"Mand","Eval-Mand"})*('Raw Data'!$Y$4:$Y$5000="completed")) "Dan the Man" wrote: I need a formula which looks at the following factors: (Column U) clients who showed program improvement, (Column F) clients who were mandated to treatemnt, (Column F) clients who had a mandated evaluaiton, and (Column Y) clients who completed treatment. Please note that Column F has a drop down menu with three options: mand, vol, Eval-Mand. The formula I have below renders the outcome to be "0", so I know I ham missing something.: =SUMPRODUCT(--('Raw Data'!$U$4:$U$50000),--('Raw Data'!$F$4:$F$5000="Mand"),--('Raw Data'!$F$4:$F$5000="Eval-Mand")*('Raw Data'!$Y$4:$Y$5000="completed") It may also be helpful to know that I originally had the formula only looking at improvement for those who were mandated to treatment, and completted, however I need to add in the additonal factor of those who were mandated to have an evaluation. Again, both mandated to treatment, and mandated to have an evaluation are "drop down" menu items in Column F. Any help would be greatly appreciated. Thanks in advance! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge for a formula | Excel Discussion (Misc queries) | |||
challenge formula | Excel Worksheet Functions | |||
PLEASE HELP!!! Formula Question (Here's a challenge) :) | Excel Worksheet Functions | |||
SUMPRODUCT formula challenge | Excel Worksheet Functions | |||
Can this formula be created - Challenge | Excel Discussion (Misc queries) |