how to use "IF" here

[Guest]

It is difficult for me, but I hope not for you peopl, I want to make column
"E" in my work book such that

if cells in column ""A"," B", "C", "D" have "severe" or "moderate" or
"mild", corresponding cell in column "E" should show "Yes"

but ifF Column "A", "B"," C", "D " ALL have "normal", corresponding cell in
"E" should show "NO"

AND IF COLUMNS "A", "B", "C", "D" all have "n/a", "n/d", E should show "n/a"

In this example,

A B C
D E
MILD NORMAL NORMAL NORMAL
YES
SEVERE MODERATE MILD MILD
YES
n/a n/a n/a n/a
n/a
n/a n/d n/d
n/a n/a
severe n/a n/a n/d
Yes

I have tried my best to explain the issue, thanking all in anticipation

 

[Rick Rothstein \(MVP - VB\)]

It is difficult for me, but I hope not for you peopl, I want to make

column
"E" in my work book such that

if cells in column ""A"," B", "C", "D" have "severe" or "moderate" or
"mild", corresponding cell in column "E" should show "Yes"

but ifF Column "A", "B"," C", "D " ALL have "normal", corresponding cell
in
"E" should show "NO"

AND IF COLUMNS "A", "B", "C", "D" all have "n/a", "n/d", E should show
"n/a"

In this example,

A B C
D E
MILD NORMAL NORMAL NORMAL
YES
SEVERE MODERATE MILD MILD
YES
n/a n/a n/a n/a
n/a
n/a n/d n/d
n/a n/a
severe n/a n/a n/d
Yes

This is a top of the head answer, so it may not be the best solution for you
(meaning, check back here for other answers later today). The formula
requires that the cells are filled with what you show... it will fail if you
misspell "NORMAL"; it will succeed if you use words other than MILD,
MODERATE or SEVERE; and any character can follow the N/ and it will act like
N/A or N/D. You can use any combination of upper and lower case letters
though.

=IF(LEN(SUBSTITUTE(UPPER(A1&B1&C1&D1),"NORMAL",""))=0,"No",IF(ISERR(FIND("N/",UPPER(A1&B1&C1&D1))),"Yes","N/A"))

Rick

 

[Guest]

One way ..

Assuming source data in cols A to D, from row2 down

Put in E2:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(A22,{"Severe";"Moderate";"Mild"},0)))>0,"Yes",IF(COUNTIF(A22,"Normal")=4,"No",IF(COUNTIF(A22,"n/a")+COUNTIF(A22,"n/d")=4,"n/a","")))
Copy down

 

[Rick Rothstein \(MVP - VB\)]

This is a top of the head answer, so it may not be the best solution for

you (meaning, check back here for other answers later today). The formula
requires that the cells are filled with what you show... it will fail if
you misspell "NORMAL"; it will succeed if you use words other than MILD,
MODERATE or SEVERE; and any character can follow the N/ and it will act
like N/A or N/D. You can use any combination of upper and lower case
letters though.

=IF(LEN(SUBSTITUTE(UPPER(A1&B1&C1&D1),"NORMAL",""))=0,"No",IF(ISERR(FIND("N/",UPPER(A1&B1&C1&D1))),"Yes","N/A"))

Of course, the COUNTIF function can shorten this up a little bit...

=IF(COUNTIF(A11,UPPER("normal"))=4,"No",IF(COUNTIF(A11,UPPER("n/?"))=0,"Yes","N/A"))

Same conditions as described above still apply.

Rick

 

[Guest]

I think, due to small space, columns were distorted, let me rewrite the
columns, as none of the above formulas is doing good for me, but any ways
thanks for your help guys

I want to make column "E" in my work book such that

if cells in column ""A"," B", "C", "D" have "severe" or "moderate" or
"mild", corresponding cell in column "E" should show "Yes"

but ifF Column "A", "B"," C", "D " "ALL" have "normal", corresponding cell
in
"E" should show "NO"

AND IF COLUMNS "A", "B", "C", "D" "ALL" have "n/a", "n/d", "E" should show
"n/a"

In this example,

A B C D E

MILD NORMAL MILD NORMAL YES
SEVERE MODERATE MILD MILD YES
n/a n/a n/a n/a n/a
n/a n/d n/d n/a n/a
severe n/a n/a n/d YES

THanking you

 

[Guest]

=IF(SUMPRODUCT(--ISNUMBER(MATCH(A77,{"Severe";"Moderate";"Mild"},0)))>0,"Yes",IF(COUNTIF(A77,"Normal")=4,"NO",IF(COUNTIF(A77,"n/a")+COUNTIF(A77,"n/d")=4,"n/a","")))


This one is working great, but when all 4 columns i.e from "A" to "D" are
"normal, E becomes empty, and I want to have "NO" in "e"

 

[Guest]

This one is working great, but when all 4 columns i.e from "A" to "D" are
"normal", E becomes empty, and I want to have "NO" in "e"

um, it should work as advertised. I've just re-tested it here. Maybe
re-check the data indications in cols A to D (typos?, white spaces?). If
there could be white spaces (leading or trailing spaces before/after
"Normal"), just replace the part:

COUNTIF(A77,"Normal")=4

with:
COUNTIF(A77,"*Normal*")=4

in the formula

 

[Rick Rothstein \(MVP - VB\)]

I think, due to small space, columns were distorted, let me rewrite the

columns, as none of the above formulas is doing good for me, but any ways
thanks for your help guys

I want to make column "E" in my work book such that

if cells in column ""A"," B", "C", "D" have "severe" or "moderate" or
"mild", corresponding cell in column "E" should show "Yes"

but ifF Column "A", "B"," C", "D " "ALL" have "normal", corresponding
cell
in
"E" should show "NO"

AND IF COLUMNS "A", "B", "C", "D" "ALL" have "n/a", "n/d", "E" should
show
"n/a"

In this example,

A B C D E

MILD NORMAL MILD NORMAL YES
SEVERE MODERATE MILD MILD YES
n/a n/a n/a n/a n/a
n/a n/d n/d n/a n/a
severe n/a n/a n/d YES

What did you want it to show if there were only a mixture of "NORMAL"s and
"N/A"s?

Rick

 

[Guest]

Thanks max you are just awesome
just one more thing, now every thing is going just great except if COLUMNS,
"A","B","C" have "NORMAL" and "D" contain "n/a", "E" is showing blank, in all
other cases this formula is giving me corrrect answer. I have checked by
their are no spaces infront or behind "n/a"


Thanking you once again

 

[Max]

if COLUMNS "A","B","C" have "NORMAL"

and "D" contain "n/a", "E" is showing blank

Yes, that's right, simply because the return for the above scenario wasn't
specified earlier by you.

Supposing a return of: zzz (instead of blank) for the above scenario is
desired, you could bolt-on an additional IF condition to take care of it,
viz:

IF(AND(COUNTIF(A7:C7,"*Normal*")=3,TRIM(D7)="n/a"),"zzz", ....

Implemented into the formula, it would now be:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(A77,{"Severe";"Moderate";"Mild"},0)))>0,"Yes",IF(COUNTIF(A77,"*Normal*")=4,"No",IF(COUNTIF(A77,"n/a")+COUNTIF(A77,"n/d")=4,"n/a",IF(AND(COUNTIF(A7:C7,"*Normal*")=3,TRIM(D7)="n/a"),"zzz",""))))

Adapt the return "zzz" to suit

---