|
3 stupid, simple IFs
I'm trying to enter a simple (so I thought) 3-If formula, but keep getting
errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Try:
=IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Thanks, ahh, the 'and'...
Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Just a few things out of place, Steve:
=IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Thanks, it works, finally !
One small problem. My original formula produced a blank cell, which I wanted, when nothing is in B, C, or D: B C D result The new formula produces a 0 B C D result 0 Why a zero and not a "" ? Thanks again, Steve "Anne Troy" wrote: Just a few things out of place, Steve: =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Try this:
=IF(SUM(B8:D8)=0,"",IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,"") ))) The zero was as a result of them all equaling each other (if and b8=c8 and c8=d8, you return d8, which is nothing or zero). ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, it works, finally ! One small problem. My original formula produced a blank cell, which I wanted, when nothing is in B, C, or D: B C D result The new formula produces a 0 B C D result 0 Why a zero and not a "" ? Thanks again, Steve "Anne Troy" wrote: Just a few things out of place, Steve: =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Perfect !!!
Thank you very much, Steve "Anne Troy" wrote: Try this: =IF(SUM(B8:D8)=0,"",IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,"") ))) The zero was as a result of them all equaling each other (if and b8=c8 and c8=d8, you return d8, which is nothing or zero). ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, it works, finally ! One small problem. My original formula produced a blank cell, which I wanted, when nothing is in B, C, or D: B C D result The new formula produces a 0 B C D result 0 Why a zero and not a "" ? Thanks again, Steve "Anne Troy" wrote: Just a few things out of place, Steve: =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Steve............
Here's a mod to eliminate errors in case you might get TEXT entrys........... =IF(COUNTA(B8:D8)COUNT(B8:D8),"",IF(SUM(B8:D8)=0, "",IF(B8C8,B8-C8,IF(AND(B 8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))))) All one line.......watchout for wordwrap........... Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... Perfect !!! Thank you very much, Steve "Anne Troy" wrote: Try this: =IF(SUM(B8:D8)=0,"",IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,"") ))) The zero was as a result of them all equaling each other (if and b8=c8 and c8=d8, you return d8, which is nothing or zero). ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, it works, finally ! One small problem. My original formula produced a blank cell, which I wanted, when nothing is in B, C, or D: B C D result The new formula produces a 0 B C D result 0 Why a zero and not a "" ? Thanks again, Steve "Anne Troy" wrote: Just a few things out of place, Steve: =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
Thanks,
I'll check it out. Steve "CLR" wrote: Steve............ Here's a mod to eliminate errors in case you might get TEXT entrys........... =IF(COUNTA(B8:D8)COUNT(B8:D8),"",IF(SUM(B8:D8)=0, "",IF(B8C8,B8-C8,IF(AND(B 8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))))) All one line.......watchout for wordwrap........... Vaya con Dios, Chuck, CABGx3 "Steve" wrote in message ... Perfect !!! Thank you very much, Steve "Anne Troy" wrote: Try this: =IF(SUM(B8:D8)=0,"",IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,"") ))) The zero was as a result of them all equaling each other (if and b8=c8 and c8=d8, you return d8, which is nothing or zero). ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, it works, finally ! One small problem. My original formula produced a blank cell, which I wanted, when nothing is in B, C, or D: B C D result The new formula produces a 0 B C D result 0 Why a zero and not a "" ? Thanks again, Steve "Anne Troy" wrote: Just a few things out of place, Steve: =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8,IF(D8C8,D8-C8,""))) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Steve" wrote in message ... Thanks, ahh, the 'and'... Now I'm getting the #value! error Here's row 8 =IF(B8C8,B8-C8,IF(AND(B8=C8,B8=D8),D8)),IF(D8C8,D8-C8,"") B C D result 1 1 1 #value! Thanks, Steve "JMB" wrote: Try: =IF(B1C1,B1-C1,IF(AND(B1=C1,B1=D1),D1),IF(D1C1,D1-C1),"") "Steve" wrote: I'm trying to enter a simple (so I thought) 3-If formula, but keep getting errors: 2 IFs work: =IF(B1C1,B1-C1,IF(D1C1,D1-C1,"")) This formula works nicely for most of what I need, either the # or nothing. B C D result 1 1 B C D 1 1 B C D 1 First 2 samples above result in 1, the 3rd results in "", giving me the result I need. However, I also need a result of the D cell (2) if B=C=D. Trying to enter that 3rd IF isn't working. My unworking formula: =IF(B1C1,B1-C1,IF(B1=C1=D1,D1),IF(D1C1,D1-C1),"") The #'s will vary, but the below example is the result(2) I'd need in this situation: B C D 2 2 2 2 Any help for this problem would be greatly appreciated. Thanks much, Steve |
| All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com