If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If then
I have a multi column (10), multi row(1000s) table. I am trying the
following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. |
#2
|
|||
|
|||
If then
PAL wrote:
I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. Is this what you are looking for? =IF(INDEX(Raw!$R$2:$R$2368,MATCH(F2,Raw!$D$2:$D$23 68,0))"", "Compliant","Non-Compliant") |
#3
|
|||
|
|||
If then
Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR
do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. |
#4
|
|||
|
|||
If then
Sorry should have asked better. What I am trying to do is....
Look at all the records for each individual (F2),and ask if there is a date in the column? If there is a date in all the records (ie 10/10) for the individual (F2), then I want to put in "compliant", if only (ie 9/10) have dates), then "non-compliant" "Gary Brown" wrote: Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. |
#5
|
|||
|
|||
If then
No, the first few are NA, the rest are compliant. There is a random
assortment. Thanks for trying. "Glenn" wrote: PAL wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. Is this what you are looking for? =IF(INDEX(Raw!$R$2:$R$2368,MATCH(F2,Raw!$D$2:$D$23 68,0))"", "Compliant","Non-Compliant") . |
#6
|
|||
|
|||
If then
Array formula (commit with CTRL+SHIFT+ENTER):
=IF(MIN(IF(Raw!$D$2:$D$2368=F2, --(Raw!$R$2:$R$2368""),""))=1, "","Non-")&"Compliant" OR =IF(SUM(--(A1:A21=D4))= SUM((A1:A21=D4)*(B1:B21"")), "","Non-")&"Compliant" PAL wrote: Sorry should have asked better. What I am trying to do is.... Look at all the records for each individual (F2),and ask if there is a date in the column? If there is a date in all the records (ie 10/10) for the individual (F2), then I want to put in "compliant", if only (ie 9/10) have dates), then "non-compliant" "Gary Brown" wrote: Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. |
#7
|
|||
|
|||
If then
Forgot to correct the cell references in the second one...should have been this:
=IF(SUM(--(Raw!$D$2:$D$2368=F2))= SUM((Raw!$D$2:$D$2368=F2)*(Raw!$R$2:$R$2368"")), "","Non-")&"Compliant" Glenn wrote: Array formula (commit with CTRL+SHIFT+ENTER): =IF(MIN(IF(Raw!$D$2:$D$2368=F2, --(Raw!$R$2:$R$2368""),""))=1, "","Non-")&"Compliant" OR =IF(SUM(--(A1:A21=D4))= SUM((A1:A21=D4)*(B1:B21"")), "","Non-")&"Compliant" PAL wrote: Sorry should have asked better. What I am trying to do is.... Look at all the records for each individual (F2),and ask if there is a date in the column? If there is a date in all the records (ie 10/10) for the individual (F2), then I want to put in "compliant", if only (ie 9/10) have dates), then "non-compliant" "Gary Brown" wrote: Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. |
#8
|
|||
|
|||
If then
No Dice. It comes back as all compliant. I did I did modify your formulas
to reference the correct cells. In order to verify your formulas, I created this one in an adjacent cell... =SUMPRODUCT((Raw!$D$2:$D$2468=B8)*(Raw!$N$2:$N$246 80)). This will give me a number. If the number is 0, I count it as compliant. Here is yours with the modified reference. =IF(MIN(IF(Raw!$D$2:$D$2368=B8,--(Raw!$R$2:$R$2368""),""))=2,"","Non-")&"Compliant" I did the same for the second formula. "Glenn" wrote: Forgot to correct the cell references in the second one...should have been this: =IF(SUM(--(Raw!$D$2:$D$2368=F2))= SUM((Raw!$D$2:$D$2368=F2)*(Raw!$R$2:$R$2368"")), "","Non-")&"Compliant" Glenn wrote: Array formula (commit with CTRL+SHIFT+ENTER): =IF(MIN(IF(Raw!$D$2:$D$2368=F2, --(Raw!$R$2:$R$2368""),""))=1, "","Non-")&"Compliant" OR =IF(SUM(--(A1:A21=D4))= SUM((A1:A21=D4)*(B1:B21"")), "","Non-")&"Compliant" PAL wrote: Sorry should have asked better. What I am trying to do is.... Look at all the records for each individual (F2),and ask if there is a date in the column? If there is a date in all the records (ie 10/10) for the individual (F2), then I want to put in "compliant", if only (ie 9/10) have dates), then "non-compliant" "Gary Brown" wrote: Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. . |
#9
|
|||
|
|||
If then
Two things:
Did you enter both of mine as array formulas (commit with CTRL+SHIFT+ENTER)? If your SUMPRODUCT gives you predictable results, can't you just wrap it in an IF to give the desired output? =IF(SUMPRODUCT((Raw!$D$2:$D$2468=B8)*(Raw!$N$2:$N$ 24680))0,"Non-","")&"Compliant" PAL wrote: No Dice. It comes back as all compliant. I did I did modify your formulas to reference the correct cells. In order to verify your formulas, I created this one in an adjacent cell... =SUMPRODUCT((Raw!$D$2:$D$2468=B8)*(Raw!$N$2:$N$246 80)). This will give me a number. If the number is 0, I count it as compliant. Here is yours with the modified reference. =IF(MIN(IF(Raw!$D$2:$D$2368=B8,--(Raw!$R$2:$R$2368""),""))=2,"","Non-")&"Compliant" I did the same for the second formula. "Glenn" wrote: Forgot to correct the cell references in the second one...should have been this: =IF(SUM(--(Raw!$D$2:$D$2368=F2))= SUM((Raw!$D$2:$D$2368=F2)*(Raw!$R$2:$R$2368"")), "","Non-")&"Compliant" Glenn wrote: Array formula (commit with CTRL+SHIFT+ENTER): =IF(MIN(IF(Raw!$D$2:$D$2368=F2, --(Raw!$R$2:$R$2368""),""))=1, "","Non-")&"Compliant" OR =IF(SUM(--(A1:A21=D4))= SUM((A1:A21=D4)*(B1:B21"")), "","Non-")&"Compliant" PAL wrote: Sorry should have asked better. What I am trying to do is.... Look at all the records for each individual (F2),and ask if there is a date in the column? If there is a date in all the records (ie 10/10) for the individual (F2), then I want to put in "compliant", if only (ie 9/10) have dates), then "non-compliant" "Gary Brown" wrote: Are you trying to fill a SINGLE cell with 'Compliant' or 'Non-Complient' OR do you want to test each row for Compliance? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "PAL" wrote: I have a multi column (10), multi row(1000s) table. I am trying the following formula, with if two conditions are true. =IF(AND(Raw!$D$2:$D$2368=F2,Raw!$R$2:$R$2368""), "Compliant", "Non-Compliant"). It doesn't seem to work. Ideas. Thanks. . |
Thread Tools | |
Display Modes | |
|
|