Multiple condition in Exel2003

July 20, 2011 at 14:05:05
Specs: Windows XP
I have following condition, for exel worksheet2003
If M1 is zero and N1 is blank, the answer in should be A
If M1 is zero and N1 is STAPH, the answer should be B
If M1 is zero and N1 is LFE, the answer should be C
If M1 is zero and N1 is NLFE, the answer should be D
If M1 is 1+ and N1 is blank, the answer should be B
If M1 is 1+ and N1 is STAPH, the answer should be B
If M1 is 1+ and N1 is LFE, the answer should be C
If M1 is 1+ and N1 is NLFE, the answer should be D

I need result in O column from M and N column.

I have total 16 condition for one cell.
Can you get me solution ?

I shall thanks in advance for all co-operation.

Mehul


See More: Multiple condition in Exel2003

Report •


#1
July 22, 2011 at 06:48:57
do you mean the value in m1 is greater than or equal to 1 or is literally equal to "1+"?

Can the value of M1 be anything other than the to options you listed? ie could M1 ever be blank?

Also just looking at your example data, basically only N1 being blank changes the result based on the contents of M1. If this is true, then you are really looking at analysing N1, and if N1 is blank then you need to do a second check of M1 to see whether it is A or B as a final result.

This is one way of doing it without having heard your responce yet:

=IF(M1=0,IF(N1="","A",IF(N1="STAPH","B",IF(N1="LFE","C",IF(N1="NLFE","D","ERROR")))),IF(M1="1+",IF(N1="","B",IF(N1="STAPH","B",IF(N1="LFE","C",IF(N1="NLFE","D","ERROR"))))))


This is how to do it if my observation that only when N1 is blank does the value of M1 matter:

=IF(N1="",IF(M1=0,"A",IF(M1="1+","B","ERROR")),IF(N1="STAPH","B",IF(N1="LFE","C",IF(N1="NLFE","D","ERROR"))))

now if you mean 1+ means greater than or equal to 1, then simply replace ="1+" with >=1 (or >0 if you are using integer values in M1).

To make this all work you need to place the appropriate formula in cell O1


By the way I put the "Error" in as a way of letting you know when they formula is dealing with an unexpected condition. The word Error will show up in O1 when this happens.


also since a blank is treated as a value of 0, is it important to distinguish between M1 being blank and having the number 0 in it?


Report •

#2
July 27, 2011 at 17:21:40
We have results like 1+, 2+, this not mean greater than or equal to 1
You can consider like symbol also.

Thanks

I am waiting for your kind of reply.

Mehul


Report •

#3
July 27, 2011 at 17:41:18
M1 N1 O (Result)
0 (Zero) Empty A
0 (Zero) STAPH B
0 (Zero) LFE C
0 (Zero) NLFE D
1+ Empty B
1+ STAPH B
1+ LFE C
1+ NLFE D
2+ Empty B
2+ STAPH B
2+ LFE D
2+ NLFE D
3+ Empty C
3+ STAPH C
3+ LFE D
3+ NLFE D
TNTC Empty C
TNTC STAPH D
TNTC LFE E
TNTC NLFE E
I have total 16 conditions for one cell.
There is no any greater than or less than in M1 column.
I need results on the basis of value of M1 and N1 column.
Empty : means nothing in that cell
1+, 2+ : means you can consider as symbol, because we have certain range for result exp. If we have count of bacteria between 1 to 10 numbers we write result as 1+, same 11 – 20 we write as 2+.

I hope you can get proper answer from me.

I am waiting for your reply

Thanks

Mehul


Report •

Related Solutions


Ask Question