IFs ANDs and ORs

  • Thread starter Thread starter ********Meg
  • Start date Start date
M

********Meg

I have 6 columns that I would like to reference.

If any of the first 3 and the last are basically greater then 0, then I
would like to put a code in an additional column.
If any of the first 3 are greater then 0 and there is nothing in any of the
last 3 columns, then I would like a different code
If any of the last 3 are greater then 0 and there is nothing in the first 3
columns then I would like a different code in the additional column.

Is there a way to go about doing this?

We're a school. We have start dates, grad dates and licensing dates for two
different programs. Based on the answers to these columns, the record would
be coded.

This is windows XP
Excel 2003

Thanks
 
one way

=IF(AND(OR(A1>0,B1>0,C1>0),F1>0),"first",IF(AND(OR(A1>0,B1>0,C1>0),AND(D1="",E1="",F1="")),"different",IF(AND(OR(D1>0,E1>0,F1>0),AND(A1="",B1="",C1="")),"differentagain","")))

enter this in G1

assumption........... your first condition
If any of the first 3 and the last
I understand that to be if the last and any of the first three..............

If not correct you may now be able to change this to suit.
 
I tested this previous suggestion and it seemed to fail for the 2nd condition
if 2 is in A1 and 1 is in D1.

Try this array formula:

=IF(AND(AND(D1:F1=""),OR(A1:C1>0)),"Code2",IF(AND(OR(D1:F1>0),AND(A1:C1="")),"Code3",IF(OR(A1:C1>0,F1>0),"Code","")))

array - you must press Shift+Ctrl+Enter to enter it.
 
********Meg said:
I have 6 columns that I would like to reference.
If any of the first 3 and the last are basically
greater then 0 [....]
Click to expand...

It is unclear whether you want to evaluate this on a row-by-row basis, or
whether you want to evaluate the conditions against entire sets of columns.

On a row-by-row basis:

=if(countif(A1:C1,">0")>0,
if(countif(D1:F1,">0")>0,"code1","code2"),
if(countif(D1:F1,">0")>0,"code3","code4"))

Based on entire sets of columns:

=if(countif(A1:C100,">0")>0,
if(countif(D1:F100,">0")>0,"code1","code2"),
if(countif(D1:F100,">0")>0,"code3","code4"))

Some notes:

1. The second formula (entire sets of columns) assumes that either all of
the columns have the same number of rows of data or at least that shorter
columns of data are blank in the remaining rows up to the longest column.


2. The COUNTIF usage does not distinguish between cells with nothing and
cells with zero or negative values (instead of dates). Some combination of
COUNTA and COUNTIF could make that distinction. But it seems unnecessary
since you do not specify any codes to cover it.


3. My "code1", "code2" and "code3" are intended to correspond to the order
of the conditions that you specified, namely:

a. "any of the first 3 and the last are basically greater then 0"
(code1);

b. "any of the first 3 are greater then 0 and there is nothing in any of
the last 3 columns" (code2);

c. "any of the last 3 are greater then 0 and there is nothing in the
first 3 columns" (code3).


4. I added "code4" to cover a condition that you do not specify, namely:
nothing in the first __and__ last set of 3 columns.

If you are confident that the condition cannot arise, you could simply
remove ``,"code4"`` or replace it with ``,"ERROR"``.


----- original message -----
 
Basically, if any of the first 3 columns..(B, C or D) have anything in them,
then i want Column E to have the following code CEM
If they don't have anything then it can be just CE
If columns F, G, or H have anyting in them then it should add an A to either
CE or CEM

dpending on what is any of these columns it could be CE, CEA CEM or CEMA


JoeU2004 said:
********Meg said:
I have 6 columns that I would like to reference.
If any of the first 3 and the last are basically
greater then 0 [....]
Click to expand...

It is unclear whether you want to evaluate this on a row-by-row basis, or
whether you want to evaluate the conditions against entire sets of
columns.

On a row-by-row basis:

=if(countif(A1:C1,">0")>0,
if(countif(D1:F1,">0")>0,"code1","code2"),
if(countif(D1:F1,">0")>0,"code3","code4"))

Based on entire sets of columns:

=if(countif(A1:C100,">0")>0,
if(countif(D1:F100,">0")>0,"code1","code2"),
if(countif(D1:F100,">0")>0,"code3","code4"))

Some notes:

1. The second formula (entire sets of columns) assumes that either all of
the columns have the same number of rows of data or at least that shorter
columns of data are blank in the remaining rows up to the longest column.


2. The COUNTIF usage does not distinguish between cells with nothing and
cells with zero or negative values (instead of dates). Some combination
of COUNTA and COUNTIF could make that distinction. But it seems
unnecessary since you do not specify any codes to cover it.


3. My "code1", "code2" and "code3" are intended to correspond to the order
of the conditions that you specified, namely:

a. "any of the first 3 and the last are basically greater then 0"
(code1);

b. "any of the first 3 are greater then 0 and there is nothing in any of
the last 3 columns" (code2);

c. "any of the last 3 are greater then 0 and there is nothing in the
first 3 columns" (code3).


4. I added "code4" to cover a condition that you do not specify, namely:
nothing in the first __and__ last set of 3 columns.

If you are confident that the condition cannot arise, you could simply
remove ``,"code4"`` or replace it with ``,"ERROR"``.


----- original message -----

********Meg said:
I have 6 columns that I would like to reference.

If any of the first 3 and the last are basically greater then 0, then I
would like to put a code in an additional column.
If any of the first 3 are greater then 0 and there is nothing in any of
the last 3 columns, then I would like a different code
If any of the last 3 are greater then 0 and there is nothing in the first
3 columns then I would like a different code in the additional column.

Is there a way to go about doing this?

We're a school. We have start dates, grad dates and licensing dates for
two different programs. Based on the answers to these columns, the
record would be coded.

This is windows XP
Excel 2003

Thanks
Click to expand...
Click to expand...
 
********Meg said:
Basically, if any of the first 3 columns..(B, C or D)
have anything in them, then i want Column E to have
Click to expand...

I think you mean row-by-row. That is the only way that "column E" makes
sense to me.

I think the following meets your needs. Put the following formula into the
E1 (i.e. the first row of data) and copy down:

="CE" &
if(counta(B1:D1)>0,"M","") &
if(counta(F1:H1)>0,"A","")

Note that I switched from using COUNTIF to COUNTA because now you are saying
"have anything" instead of "greater than zero". If you prefer:

="CE" &
if(countif(B1:D1,">0")>0,"M","") &
if(countif(F1:H1,">0")>0,"A","")


----- original message -----

********Meg said:
Basically, if any of the first 3 columns..(B, C or D) have anything in
them, then i want Column E to have the following code CEM
If they don't have anything then it can be just CE
If columns F, G, or H have anyting in them then it should add an A to
either CE or CEM

dpending on what is any of these columns it could be CE, CEA CEM or CEMA


JoeU2004 said:
********Meg said:
I have 6 columns that I would like to reference.
If any of the first 3 and the last are basically
greater then 0 [....]
Click to expand...

It is unclear whether you want to evaluate this on a row-by-row basis, or
whether you want to evaluate the conditions against entire sets of
columns.

On a row-by-row basis:

=if(countif(A1:C1,">0")>0,
if(countif(D1:F1,">0")>0,"code1","code2"),
if(countif(D1:F1,">0")>0,"code3","code4"))

Based on entire sets of columns:

=if(countif(A1:C100,">0")>0,
if(countif(D1:F100,">0")>0,"code1","code2"),
if(countif(D1:F100,">0")>0,"code3","code4"))

Some notes:

1. The second formula (entire sets of columns) assumes that either all of
the columns have the same number of rows of data or at least that shorter
columns of data are blank in the remaining rows up to the longest column.


2. The COUNTIF usage does not distinguish between cells with nothing and
cells with zero or negative values (instead of dates). Some combination
of COUNTA and COUNTIF could make that distinction. But it seems
unnecessary since you do not specify any codes to cover it.


3. My "code1", "code2" and "code3" are intended to correspond to the
order of the conditions that you specified, namely:

a. "any of the first 3 and the last are basically greater then 0"
(code1);

b. "any of the first 3 are greater then 0 and there is nothing in any
of the last 3 columns" (code2);

c. "any of the last 3 are greater then 0 and there is nothing in the
first 3 columns" (code3).


4. I added "code4" to cover a condition that you do not specify, namely:
nothing in the first __and__ last set of 3 columns.

If you are confident that the condition cannot arise, you could simply
remove ``,"code4"`` or replace it with ``,"ERROR"``.


----- original message -----

********Meg said:
I have 6 columns that I would like to reference.

If any of the first 3 and the last are basically greater then 0, then I
would like to put a code in an additional column.
If any of the first 3 are greater then 0 and there is nothing in any of
the last 3 columns, then I would like a different code
If any of the last 3 are greater then 0 and there is nothing in the
first 3 columns then I would like a different code in the additional
column.

Is there a way to go about doing this?

We're a school. We have start dates, grad dates and licensing dates for
two different programs. Based on the answers to these columns, the
record would be coded.

This is windows XP
Excel 2003

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top