ExcelBanter - how to use "IF" here

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - how to use "IF" here (https://www.excelbanter.com/excel-discussion-misc-queries/146812-how-use-if-here.html)

omer

how to use "IF" here

Rick Rothstein \(MVP - VB\)

how to use "IF" here

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

Max

how to use "IF" here

One way ..

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

Put in E2:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(A2:D2,{"Severe";"Moderate";"Mild"}, 0)))0,"Yes",IF(COUNTIF(A2:D2,"Normal")=4,"No",IF( COUNTIF(A2:D2,"n/a")+COUNTIF(A2:D2,"n/d")=4,"n/a","")))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Omer" wrote:
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\)

how to use "IF" here

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(A1:D1,UPPER("normal"))=4,"No",IF(COUNT IF(A1:D1,UPPER("n/?"))=0,"Yes","N/A"))

Same conditions as described above still apply.

Rick

omer

how to use "IF" here

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

"Omer" wrote:

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

omer

how to use "IF" here

=IF(SUMPRODUCT(--ISNUMBER(MATCH(A7:D7,{"Severe";"Moderate";"Mild"}, 0)))0,"Yes",IF(COUNTIF(A7:D7,"Normal")=4,"NO",IF( COUNTIF(A7:D7,"n/a")+COUNTIF(A7:D7,"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"

Max

how to use "IF" here

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(A7:D7,"Normal")=4

with:
COUNTIF(A7:D7,"*Normal*")=4

in the formula
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Omer" wrote:
=IF(SUMPRODUCT(--ISNUMBER(MATCH(A7:D7,{"Severe";"Moderate";"Mild"}, 0)))0,"Yes",IF(COUNTIF(A7:D7,"Normal")=4,"NO",IF( COUNTIF(A7:D7,"n/a")+COUNTIF(A7:D7,"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"

Rick Rothstein \(MVP - VB\)

how to use "IF" here

omer

how to use "IF" here

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

how to use "IF" here

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(A7:D7,{"Severe";"Moderate";"Mild"}, 0)))0,"Yes",IF(COUNTIF(A7:D7,"*Normal*")=4,"No",I F(COUNTIF(A7:D7,"n/a")+COUNTIF(A7:D7,"n/d")=4,"n/a",IF(AND(COUNTIF(A7:C7,"*Normal*")=3,TRIM(D7)= "n/a"),"zzz",""))))

Adapt the return "zzz" to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Omer" wrote in message
...
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

All times are GMT +1. The time now is 03:35 PM.