I am hoping for help for a new challenge in my veterinary research project. I would like a way to cross check values in different columns in my Excel spreadsheet. I want to make sure that for every case (bird) that has been assigned a category that it has at least one diagnosis (subcategory) for that category; and that no diagnoses were incorrectly assigned to a bird that didn't have that category assigned. I have 6500 cases (birds), 27 categories, and over 200 diagnoses (subcategories) and so I am hoping for a formula to help save me time and improve accuracy. CATEGORY1 DIAGNOSIS1 DIAGNOSIS2 DIAGNOSIS3 CROSS-CHECK BIRD1 N N N N 0 BIRD2 Y N N N 2 BIRD3 Y N N Y 1 BIRD4 Y Y Y N 1 BIRD5 N N Y N 3 So in the table above, there would be two CORRECT conditions in the CROSS-CHECK (output) column: "0" and "1". "0" would be that the particular category was not assigned to the bird (CATEGORY1="N") and no diagnosis (subcategory) for that category was assigned (DIAGNOSIS1:DIAGNOSIS3="N"). "1" would be that a category was assigned (CATEGORY1="Y") and at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y"). There would be two ERROR conditions in the CROSS-CHECK (output) column, "2" and "3". "2" would be that the category was assigned to the bird (CATEGORY1="Y"), but the bird was not assigned any diagnosis (subcategory) for that category (DIAGNOSIS1:DIAGNOSIS3="N") "3" would be that the category was not assigned to the bird (CATEGORY1="N"), but at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y") To add to the challenge, the "N" and "Y" in the CATEGORY and DIAGNOSIS columns have been populated using LAMBDA and BYROW functions. I have tried several formulas, some including IF(COUNTIF), but can only come up with 2 potential condition (0/1, or Y/N) for the cross-check column. I need a more complex formula and I am having trouble writing it myself. Additionally, because of LAMBDA/BYROW I think, I have to do a hard return to each row in the cross-check column to get it to give the correct result. I do have Microsoft 365.

excelexcel-formulaconditional-statementsrowcell

Reputation: 55

Need formula to help cross-check values and conditions in different columns

I am hoping for help for a new challenge in my veterinary research project. I would like a way to cross check values in different columns in my Excel spreadsheet. I want to make sure that for every case (bird) that has been assigned a category that it has at least one diagnosis (subcategory) for that category; and that no diagnoses were incorrectly assigned to a bird that didn't have that category assigned. I have 6500 cases (birds), 27 categories, and over 200 diagnoses (subcategories) and so I am hoping for a formula to help save me time and improve accuracy.

	CATEGORY1	DIAGNOSIS1	DIAGNOSIS2	DIAGNOSIS3	CROSS-CHECK
BIRD1	N	N	N	N	0
BIRD2	Y	N	N	N	2
BIRD3	Y	N	N	Y	1
BIRD4	Y	Y	Y	N	1
BIRD5	N	N	Y	N	3

So in the table above, there would be two CORRECT conditions in the CROSS-CHECK (output) column: "0" and "1".

"0" would be that the particular category was not assigned to the bird (CATEGORY1="N") and no diagnosis (subcategory) for that category was assigned (DIAGNOSIS1:DIAGNOSIS3="N").
"1" would be that a category was assigned (CATEGORY1="Y") and at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y").

There would be two ERROR conditions in the CROSS-CHECK (output) column, "2" and "3".

"2" would be that the category was assigned to the bird (CATEGORY1="Y"), but the bird was not assigned any diagnosis (subcategory) for that category (DIAGNOSIS1:DIAGNOSIS3="N")
"3" would be that the category was not assigned to the bird (CATEGORY1="N"), but at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a "Y")

To add to the challenge, the "N" and "Y" in the CATEGORY and DIAGNOSIS columns have been populated using LAMBDA and BYROW functions.

I have tried several formulas, some including IF(COUNTIF), but can only come up with 2 potential condition (0/1, or Y/N) for the cross-check column. I need a more complex formula and I am having trouble writing it myself. Additionally, because of LAMBDA/BYROW I think, I have to do a hard return to each row in the cross-check column to get it to give the correct result. I do have Microsoft 365.

Upvotes: 1

Answers (5)

P.b

Reputation: 11628

=BYROW(B2:E6,LAMBDA(b,LET(c,SUM(N(DROP(b,,1)="Y"))>0,IF(TAKE(b,,1)="Y",IF(c,1,2),IF(c,3,0)))))

An if statement is used on the first column's value (IF(TAKE(b,,1)="Y"), next we check the count(/sum) of the remaining column's values equal to "Y" is greater than 0 (SUM(N(DROP(b,,1)="Y"))>0 declared as c) to get the differentiated results.

Or a bit shorter: =BYROW(B2:E6,LAMBDA(b,LET(c,OR(DROP(b,,1)="Y"),IF(@T(b)="Y",IF(c,1,2),IF(c,3,0)))))

Upvotes: 2

nkalvi

Reputation: 2614

With minor addition for accommodating future entries automatically (COUNTA(A:A)).

Hope your birds won't mind the rows of cats here :)

Upvotes: 0

Black cat

Reputation: 6271

This formula converts the input to a binary then decimal value and with CHOOSE define the output value of the formula.

In CrossCheck column row 2: (and drag down)

=CHOOSE(BIN2DEC(VALUE(TEXTJOIN("",FALSE,IF(B2:E2="N",0,1))))+1,0,3,3,3,3,3,3,3,2,1,1,1,1,1,1,1,1)

Upvotes: 1

Mayukh Bhattacharya

Reputation: 27438

One another way, without using the LAMBDA() helper functions, and using MMULT() + XMATCH():

• Formula used in cell F2

=XMATCH(B2:B6&IF(MMULT(N(C2:E6="Y"),TOCOL(RIGHT(C1:E1))^0),"Y","N"),
 {"NN";"YY";"YN";"NY"})-1

Using MMULT() return the counts for the Diagnosis, If greater than 0 then Y else N, join with the Category Col, to create a reference and using XMATCH() to find the relative positions of the item in the array.

If there are always three columns for Diagnosis then bit shorter may be:

=XMATCH(B2:B6&IF(MMULT(N(C2:E6="Y"),{1;1;1}),"Y","N"),{"NN","YY","YN","NY"})-1

Upvotes: 1

Nelson

Reputation: 339

Lauren,

P.b's solution should work. As I still use Excel 2010, I do not have access to Excel 365 functions.

I suggest the following approach:

a) create a new column "Has diagnosis" with the formula:

=IF(COUNTIF(C2:E2,"Y")=0,0,1)

b) Put in "CROSS-CHECK" column:

=IF(AND(G2=0,B2="N"),0,IF(AND(G2>0,B2="Y"),1,IF(AND(G2>0,B2="N"),3,2)))

The advantages of my approach (in my point of view) are: 1) maintains backwards compatibility with Excel previous versions and 2) more readable code.

I tested formulas like that:

Upvotes: 2

Need formula to help cross-check values and conditions in different columns

Answers (5)

Related Questions