| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF/AND/OR Formula Help?
I have 4 columns:
Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
I don't know if this is what you want, but here is what I came up with based
on your request. =IF(OR(B2="X",AND(B2="",E2="")),0,IF(D2<=E2,D2,IF( D2E2,E2))) "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
Thank you PCLIVE - that worked perfectly!
-- Carol "PCLIVE" wrote: I don't know if this is what you want, but here is what I came up with based on your request. =IF(OR(B2="X",AND(B2="",E2="")),0,IF(D2<=E2,D2,IF( D2E2,E2))) "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
Try this:
=IF(OR(B2="x",AND(B2="",E2="")),0,IF(AND(B2="",D2< =E2),D2,E2)) Biff "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
Thanks Biff. This worked on all cells with the exception of those where the
amount in E2 was smaller than that in D2 - but not a problem, the formula provided by "PCLIVE" worked. Thank you for responding! -- Carol "T. Valko" wrote: Try this: =IF(OR(B2="x",AND(B2="",E2="")),0,IF(AND(B2="",D2< =E2),D2,E2)) Biff "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
This worked on all cells with the exception of those
where the amount in E2 was smaller than that in D2 Hmmm.... You sure about that? It works fine on my end. I compared my results to those using PCLIVE's formula and they are identical. Biff "Carol" wrote in message ... Thanks Biff. This worked on all cells with the exception of those where the amount in E2 was smaller than that in D2 - but not a problem, the formula provided by "PCLIVE" worked. Thank you for responding! -- Carol "T. Valko" wrote: Try this: =IF(OR(B2="x",AND(B2="",E2="")),0,IF(AND(B2="",D2< =E2),D2,E2)) Biff "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
Interesting. I have one row that looks like this:
ColB ColD ColE ColF 2 4 4 It should have returned 2! However, I have another row that has 5 in ColD and 6 in Col E - it returned 5 as it should have. Very odd. I did discover a mistake in my request. I had stated that if B2 & E2 was blank, F2 should be 0. But that was wrong. F2 should have been E2. But thanks to the information ya'll provided I was able to fix it myself. (Feeling very smart now - thank you very much!) -- Carol "T. Valko" wrote: This worked on all cells with the exception of those where the amount in E2 was smaller than that in D2 Hmmm.... You sure about that? It works fine on my end. I compared my results to those using PCLIVE's formula and they are identical. Biff "Carol" wrote in message ... Thanks Biff. This worked on all cells with the exception of those where the amount in E2 was smaller than that in D2 - but not a problem, the formula provided by "PCLIVE" worked. Thank you for responding! -- Carol "T. Valko" wrote: Try this: =IF(OR(B2="x",AND(B2="",E2="")),0,IF(AND(B2="",D2< =E2),D2,E2)) Biff "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF/AND/OR Formula Help?
Glad you got it worked out. Thanks for the feedback!
Biff "Carol" wrote in message ... Interesting. I have one row that looks like this: ColB ColD ColE ColF 2 4 4 It should have returned 2! However, I have another row that has 5 in ColD and 6 in Col E - it returned 5 as it should have. Very odd. I did discover a mistake in my request. I had stated that if B2 & E2 was blank, F2 should be 0. But that was wrong. F2 should have been E2. But thanks to the information ya'll provided I was able to fix it myself. (Feeling very smart now - thank you very much!) -- Carol "T. Valko" wrote: This worked on all cells with the exception of those where the amount in E2 was smaller than that in D2 Hmmm.... You sure about that? It works fine on my end. I compared my results to those using PCLIVE's formula and they are identical. Biff "Carol" wrote in message ... Thanks Biff. This worked on all cells with the exception of those where the amount in E2 was smaller than that in D2 - but not a problem, the formula provided by "PCLIVE" worked. Thank you for responding! -- Carol "T. Valko" wrote: Try this: =IF(OR(B2="x",AND(B2="",E2="")),0,IF(AND(B2="",D2< =E2),D2,E2)) Biff "Carol" wrote in message ... I have 4 columns: Col B indicates if Trained - Yes = X, No = "" (blank) Col D indicates Phase 1, 2, 3, 4, 5, or 6 Col E indicates Back-Up Phase 1, 2, 3, 4, 5, 6, or "" (blank) In Col F I want a formula that will do the following: If B2 contains an X, F2=0 If B2 is blank, and... E2 is blank, F2=0 D2 is Less Than or Equal to E2, F2=D2 D2 is Greater Than E2, F2=E2 I have the following formula that works for everything EXCEPT those times when F2 should equal E2 or 0: =IF(AND(OR(D2<=E2,D2=E2),B2=""),D2,"0") Not sure where to go from here. Can you help? Thank you! Carol I've got an if/and formula that is doing everything -- Carol |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
