Need to add more formula
 

I have this formula:
=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

Just as W, J and K must meet a criteria or it gives a "Yes" or "No", I need
cells AD1 and AE1 added to this formula so it also gives a "BER" or
"Canceled" when a date is entered in to cells AD or AE.

W, J, K have the criteria of a Yes or No. AD will have the criteria of BER
and AE is Canceled.

Need to add more formula
 

Hi!

Ok, let's see what we have here.........

Which set of conditions has precedence?

Since each set returns different values one set has to be evaluated first
and your explanation doesn't really give any clue as to which set that is.

The formula could be written 2 ways:

=IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel",IF(COU NT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes","No")," ")))

=IF(COUNT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes", "No"),IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel"," ")))

In the first formula, the dates in cells AD1, AE1 have precedence.
In the second formula numbers in cells W1, J1 and K1 have precedence.

Biff

"roy.okinawa" wrote in message
...
I have this formula:
=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

Just as W, J and K must meet a criteria or it gives a "Yes" or "No", I
need
cells AD1 and AE1 added to this formula so it also gives a "BER" or
"Canceled" when a date is entered in to cells AD or AE.

W, J, K have the criteria of a Yes or No. AD will have the criteria of
BER
and AE is Canceled.

Need to add more formula
 

Thanks. That did it. The 1st formula took precedence.

For my knowledge, what does the =2 mean after IF(AND(COUNT(J1,K1)?

"Biff" wrote:

Hi!

Ok, let's see what we have here.........

Which set of conditions has precedence?

Since each set returns different values one set has to be evaluated first
and your explanation doesn't really give any clue as to which set that is.

The formula could be written 2 ways:

=IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel",IF(COU NT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes","No")," ")))

=IF(COUNT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes", "No"),IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel"," ")))

In the first formula, the dates in cells AD1, AE1 have precedence.
In the second formula numbers in cells W1, J1 and K1 have precedence.

Biff

"roy.okinawa" wrote in message
...
I have this formula:
=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

Just as W, J and K must meet a criteria or it gives a "Yes" or "No", I
need
cells AD1 and AE1 added to this formula so it also gives a "BER" or
"Canceled" when a date is entered in to cells AD or AE.

W, J, K have the criteria of a Yes or No. AD will have the criteria of
BER
and AE is Canceled.

Need to add more formula
 

Hi!

For my knowledge, what does the =2 mean after IF(AND(COUNT(J1,K1)?

In your original formula:

=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

You use the Isnumber function to test a cell to see if it contains a number.
You can achieve the same thing using the Count function and you save a few
keystrokes in the process!

So, instead of using:

ISNUMBER(J1),ISNUMBER(K1)

I used Count. Count does just what its name implies, it counts numeric
values. If both cells contained numbers the count would be 2.

So:

COUNT(J1,K1)=2

Is just another way of expressing:

ISNUMBER(J1),ISNUMBER(K1)

See how much shorter using Count is?

Biff

"roy.okinawa" wrote in message
...
Thanks. That did it. The 1st formula took precedence.

For my knowledge, what does the =2 mean after IF(AND(COUNT(J1,K1)?

"Biff" wrote:

Hi!

Ok, let's see what we have here.........

Which set of conditions has precedence?

Since each set returns different values one set has to be evaluated first
and your explanation doesn't really give any clue as to which set that
is.

The formula could be written 2 ways:

=IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel",IF(COU NT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes","No")," ")))

=IF(COUNT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes", "No"),IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel"," ")))

In the first formula, the dates in cells AD1, AE1 have precedence.
In the second formula numbers in cells W1, J1 and K1 have precedence.

Biff

"roy.okinawa" wrote in message
...
I have this formula:
=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

Just as W, J and K must meet a criteria or it gives a "Yes" or "No", I
need
cells AD1 and AE1 added to this formula so it also gives a "BER" or
"Canceled" when a date is entered in to cells AD or AE.

W, J, K have the criteria of a Yes or No. AD will have the criteria of
BER
and AE is Canceled.

Need to add more formula
 

I have ran into an unanticipated problem. As I stated before the first
formula is working, however as I was reconciling my data I notice that column
K in some instances will be blank. No entry/number is required.

Since no entry/number is required in column K and all the other criteria has
been met, it is giving me a false "No" in column C. Column C should actually
be blank if all criteria in formula is met and column K is also blank.

Does that make sense?



"Biff" wrote:

Hi!

Ok, let's see what we have here.........

Which set of conditions has precedence?

Since each set returns different values one set has to be evaluated first
and your explanation doesn't really give any clue as to which set that is.

The formula could be written 2 ways:

=IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel",IF(COU NT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes","No")," ")))

=IF(COUNT(W1),IF(AND(COUNT(J1,K1)=2,J1<=K1),"Yes", "No"),IF(COUNT(AD1),"Ber",IF(COUNT(AE1),"Cancel"," ")))

In the first formula, the dates in cells AD1, AE1 have precedence.
In the second formula numbers in cells W1, J1 and K1 have precedence.

Biff

"roy.okinawa" wrote in message
...
I have this formula:
=IF(ISNUMBER(W1),IF(AND(ISNUMBER(J1),ISNUMBER(K1), J1<=K1),"Yes","No"),"")

Just as W, J and K must meet a criteria or it gives a "Yes" or "No", I
need
cells AD1 and AE1 added to this formula so it also gives a "BER" or
"Canceled" when a date is entered in to cells AD or AE.

W, J, K have the criteria of a Yes or No. AD will have the criteria of
BER
and AE is Canceled.