|
Complicated Formula help please
Hi All I have a brain teaser. I'm trying to make a formula which says : If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Can someone help with this? I'm grateful for any advice. I tried this : =IF(COUNTIF(E2,"C*")0,J2,IF(J2=2,6,IF(OR(J2=1,J2= 11),5,J2))) and this =IF(LEFT(E2)="C",J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5, J2))) but neither give correct results :( Best Wishes |
Complicated Formula help please
"Colin Hayes" wrote:
If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Try: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) ----- original message ----- "Colin Hayes" wrote: Hi All I have a brain teaser. I'm trying to make a formula which says : If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Can someone help with this? I'm grateful for any advice. I tried this : =IF(COUNTIF(E2,"C*")0,J2,IF(J2=2,6,IF(OR(J2=1,J2= 11),5,J2))) and this =IF(LEFT(E2)="C",J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5, J2))) but neither give correct results :( Best Wishes . |
Complicated Formula help please
In article , Joe
User <joeu2004@?.? writes "Colin Hayes" wrote: If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Try: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) Hi Joe User OK thanks for that - perfect first time. I don't know how you do it - I'm in awe... BTW I'd love if you have time a quick verbal run-through of how this is working. For my own interest , and also because I know it's changing one of the values in certain cells to a 6 , but see no sixes in the formula...! Thanks again ----- original message ----- "Colin Hayes" wrote: Hi All I have a brain teaser. I'm trying to make a formula which says : If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Can someone help with this? I'm grateful for any advice. I tried this : =IF(COUNTIF(E2,"C*")0,J2,IF(J2=2,6,IF(OR(J2=1,J2= 11),5,J2))) and this =IF(LEFT(E2)="C",J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5, J2))) but neither give correct results :( Best Wishes . |
Complicated Formula help please
"Colin Hayes" wrote:
User <joeu2004@?.? writes =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) [....] BTW I'd love if you have time a quick verbal run-through of how this is working. No problem. I was concerned that it might be a little mysterious. I started with a more "natural" approach, albeit longer with more nested IF() expressions. But the one above is more concise, and it does not seem too arcane -- a reasonable compromise, I think. My understanding is: you want 5 or 6 when LEFT(E2)="c" and OR(J2=1,J2=2,J2=11) are both true; otherwise, you want J2. OR(J2={1,2,11}) is shorthand for OR(J2=1,J2=2,J2=11). The expression 5+(J2=2) is shorthand for IF(J2=2,6,5). The expression (J2=2) results in TRUE or FALSE. When those boolean values are used in an arithmetic expression, they are translated into 1 and 0 respectively. So when J2=2, the expression becomes 5+1, which is 6. When J2<2, the expression becomes 5+0, which is 5. Caveat: That trick does make it difficult to change the formula if you want something than 5 or 6. Arguably, it would be flexible to write: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), IF(J2=2,6,5), J2) IMHO, either approach is better than the following expression, which follows your English description more closely: =IF(LEFT(E2)="c", IF(OR(J2=1,J2=11), 5, IF(J2=2, 6, J2)), J2) ----- original message ----- "Colin Hayes" wrote in message ... In article , Joe User <joeu2004@?.? writes "Colin Hayes" wrote: If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Try: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) Hi Joe User OK thanks for that - perfect first time. I don't know how you do it - I'm in awe... BTW I'd love if you have time a quick verbal run-through of how this is working. For my own interest , and also because I know it's changing one of the values in certain cells to a 6 , but see no sixes in the formula...! Thanks again ----- original message ----- "Colin Hayes" wrote: Hi All I have a brain teaser. I'm trying to make a formula which says : If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Can someone help with this? I'm grateful for any advice. I tried this : =IF(COUNTIF(E2,"C*")0,J2,IF(J2=2,6,IF(OR(J2=1,J2= 11),5,J2))) and this =IF(LEFT(E2)="C",J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5, J2))) but neither give correct results :( Best Wishes . |
Complicated Formula help please
Hi Joe User OK thanks for getting back. Fascinating to see the logic so clearly outlined. I can see how it works now. I wasn't aware previously of the way the (J2=2) function could be used , or that it was placed arithmetically. Very clever. Interesting also to see other ways of achieving the same goal. My own attempt seems clumsy in comparison , and too verbatim in layout , but I'm more aware now of a different type of narrative. Thanks again for your expertise. Best Wishes In article , Joe User <joeu2004@?.? writes "Colin Hayes" wrote: User <joeu2004@?.? writes =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) [....] BTW I'd love if you have time a quick verbal run-through of how this is working. No problem. I was concerned that it might be a little mysterious. I started with a more "natural" approach, albeit longer with more nested IF() expressions. But the one above is more concise, and it does not seem too arcane -- a reasonable compromise, I think. My understanding is: you want 5 or 6 when LEFT(E2)="c" and OR(J2=1,J2=2,J2=11) are both true; otherwise, you want J2. OR(J2={1,2,11}) is shorthand for OR(J2=1,J2=2,J2=11). The expression 5+(J2=2) is shorthand for IF(J2=2,6,5). The expression (J2=2) results in TRUE or FALSE. When those boolean values are used in an arithmetic expression, they are translated into 1 and 0 respectively. So when J2=2, the expression becomes 5+1, which is 6. When J2<2, the expression becomes 5+0, which is 5. Caveat: That trick does make it difficult to change the formula if you want something than 5 or 6. Arguably, it would be flexible to write: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), IF(J2=2,6,5), J2) IMHO, either approach is better than the following expression, which follows your English description more closely: =IF(LEFT(E2)="c", IF(OR(J2=1,J2=11), 5, IF(J2=2, 6, J2)), J2) ----- original message ----- "Colin Hayes" wrote in message ... In article , Joe User <joeu2004@?.? writes "Colin Hayes" wrote: If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Try: =IF(AND(LEFT(E2)="c",OR(J2={1,2,11})), 5+(J2=2), J2) Hi Joe User OK thanks for that - perfect first time. I don't know how you do it - I'm in awe... BTW I'd love if you have time a quick verbal run-through of how this is working. For my own interest , and also because I know it's changing one of the values in certain cells to a 6 , but see no sixes in the formula...! Thanks again ----- original message ----- "Colin Hayes" wrote: Hi All I have a brain teaser. I'm trying to make a formula which says : If E2 starts with "C" and J2 equals 1 or 11 , then put 5 , otherwise put J2 If E2 starts with "C" and J2 equals 2 , then put 6 , otherwise put J2 Can someone help with this? I'm grateful for any advice. I tried this : =IF(COUNTIF(E2,"C*")0,J2,IF(J2=2,6,IF(OR(J2=1,J2= 11),5,J2))) and this =IF(LEFT(E2)="C",J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5, J2))) but neither give correct results :( Best Wishes . |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com