Complicated Formula help please

[Colin Hayes]

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

 

[Joe User]

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]

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

 

[Joe User]

=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]

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

=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 -----