| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 8<0)). 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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$ 2468<0))<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 8<0)). 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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
