On 2/8/2010 10:29 PM, Gene Kohlenberg wrote:
On 2/8/2010 9:27 PM, Walter Hildebrandt wrote:
I am confused. IOn your last reply, what is the difference between IF(OR
and IF(AND.
Why was there just the ;3 at the end of the IF(AND but a ;2;"emt" at
the end
of the IF(OR
On Mon, Feb 8, 2010 at 6:44 PM, Gene Kohlenberg
<[email protected]>wrote:
On 2/8/2010 6:28 PM, Walter Hildebrandt wrote:
The formula worked except 2 appeared where emt should appear
On Mon, Feb 8, 2010 at 3:54 PM, Gene Kohlenberg
<[email protected]>wrote:
On 2/8/2010 5:26 PM, Walter Hildebrandt wrote:
How would the following be done?
Cells A1, B1, D1, E1 have numbers in them that may be greater than 0
(vero)
or less than 0
In cell Z1, if all the four cells have numbers greater than 0, 3
appears.
In cell Z1, if any of the four cells have a number less than 0, 2
appears
However, in cell Z1, if any of the four cells are empty, emt
appears (a
2
or
3 does not appear)
Put
=IF(AND(A4>0;B4>0;D4>0;E4>0)>0;3;IF(OR(A4<0;B4<0;D4<0;E4<0);2;"emp"))
in Z1
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
Ah yes, I didn't test thoroughly. However if I put
=IF(AND(A4>0;B4>0;D4>0;E4>0);3;IF(OR(A4="";B4="";D4="";E4="");"emt";2))
in Z
then I get the following results:
A B D E Z
-1 3 2 2 2
-1 2 6 emt
1 1 1 1 3
5 3 6 -1 2
8 4 3 emt
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
Your specification said that if all four cells are positive, then you
want Z to be 3. Therefore I used the "and" condition,
and(a1>0;b1>0;d1>0;e1>0), which is true only if all four cells are
positive.
You also said that if any cell is empty the result should be "emt". Then
I used the "or" condition, or(a1="";b1="";d1="";e1=""), which is true if
any one of the cells is empty.
The format of an "if" statement is =if(condition is true;set this cell
to this result)
The "if" statement =if(and(a1>0;b1>0;d1>0;e1>0);3) sets the z cell to 3
when the "and" statement is true.
The "if" statement =if(or(a1="";b1="";d1="";e1="");"emt") sets the z
cell to "emt" when the "or" statement is true.
The only other condition possible is that at least one cell is negative.
The full "if" statement format is =if(condition is true;set this cell to
this result; else set this cell to this result). So we can combine the
two "if" statements above into one.
=if(and(a1>0;b1>0;d1>0;e1>0);3;if(or(a1="";b1="";d1="";e1="");"emt");2)
When the "and" condition is true the first "if" statement sets the z
cell to 3. Otherwise the second "if" statement sets the z cell to "emt"
when the "or" condition is true. When the "or" condition is false, the
second "if" statement sets the z cell to 2.
The above combined "if" statements have a misplaced ")". It should be
=IF(AND(A1>0;B1>0;D1>0;E1>0);3;IF(OR(A1="";B1="";D1="";E1="");"emt";2))
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
