Developer Forum

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.


--
Greetings from New Zealand

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

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 -----

asically, 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" < XXXX@XXXXX.COM > wrote in message
news:eEfJ2Dp$ XXXX@XXXXX.COM ...

********Meg" <alumni(removethis)@swedishinstitute.edu> wrote:

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" <alumni(removethis)@swedishinstitute.edu> wrote in message
news:uFI%23Uax$ XXXX@XXXXX.COM ...