Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |