Express 'Joined up' IF's Q
 

How could I express the following as a Text string

I have 3 Names in A1:A3 (Jim; Bob; Mike); in B1:B3 is a value of
either 1 or 0 (1;0;1). I wish to construct an opening statement
depending if the values in B1:B3 are 1, so for example if B1=1;B2=0
and B3=1 my opening statement would be:

Dear Jim / Mike

if B1=0;B2=0 and B3=1 my opening statement would be:

Dear Mike

if B1=1;B2=1 and B3=1 my opening statement would be:

Dear Jim / Bob / Mike

if B1=0;B2=0 and B3=0 my opening statement would be:

<blank

I have 5 names in total so haven't put all the permutations above,
perhaps there is not an easy way to do this

Express 'Joined up' IF's Q
 

=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")
--
David Biddulph

"Sean" wrote in message
...
How could I express the following as a Text string

I have 3 Names in A1:A3 (Jim; Bob; Mike); in B1:B3 is a value of
either 1 or 0 (1;0;1). I wish to construct an opening statement
depending if the values in B1:B3 are 1, so for example if B1=1;B2=0
and B3=1 my opening statement would be:

Dear Jim / Mike

if B1=0;B2=0 and B3=1 my opening statement would be:

Dear Mike

if B1=1;B2=1 and B3=1 my opening statement would be:

Dear Jim / Bob / Mike

if B1=0;B2=0 and B3=0 my opening statement would be:

<blank

I have 5 names in total so haven't put all the permutations above,
perhaps there is not an easy way to do this

Express 'Joined up' IF's Q
 

Thanks David, can't seem to get to work

Is this part complete =IF(B1+B2+B3,"Dear "&.....

Express 'Joined up' IF's Q
 

The formula I gave works for me.
That formula was
=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")
but you may need to be careful if the newsgroup has split the formula at a
line wrap.
If it splits the lines at the " / " stage, there is a space, a slant, and
another space, between the quotes.
--
David Biddulph

"Sean" wrote in message
...
Thanks David, can't seem to get to work

Is this part complete =IF(B1+B2+B3,"Dear "&.....

Express 'Joined up' IF's Q
 

I should, of course, have asked what you mean by "can't seem to get to
work".
What error message did you get? If no error message, what result did you
get for what input, and what result did you expect?
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The formula I gave works for me.
That formula was
=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")
but you may need to be careful if the newsgroup has split the formula at a
line wrap.
If it splits the lines at the " / " stage, there is a space, a slant, and
another space, between the quotes.
--
David Biddulph

"Sean" wrote in message
...
Thanks David, can't seem to get to work

Is this part complete =IF(B1+B2+B3,"Dear "&.....

Express 'Joined up' IF's Q
 

=IF(SUM(B1:B3),"Dear ","")&IF(B1,"Jim"&IF(SUM(B1:B3)1," /
",""),"")&IF(B2,"Bob"&IF(SUM(B2:B3)1," / ",""),"")&IF(B3,"Mike","")


"Sean" wrote:

How could I express the following as a Text string

I have 3 Names in A1:A3 (Jim; Bob; Mike); in B1:B3 is a value of
either 1 or 0 (1;0;1). I wish to construct an opening statement
depending if the values in B1:B3 are 1, so for example if B1=1;B2=0
and B3=1 my opening statement would be:

Dear Jim / Mike

if B1=0;B2=0 and B3=1 my opening statement would be:

Dear Mike

if B1=1;B2=1 and B3=1 my opening statement would be:

Dear Jim / Bob / Mike

if B1=0;B2=0 and B3=0 my opening statement would be:

<blank

I have 5 names in total so haven't put all the permutations above,
perhaps there is not an easy way to do this

Express 'Joined up' IF's Q
 

This works for five names in A1:A5 and 1s or 0s in B1:B5:

=IF(SUM(B1:B5),"Dear ","")&IF(B1,A1&IF(SUM(B2:B5)," /
",""),"")&IF(B2,A2&IF(SUM(B3:B5)," /
",""),"")&IF(B3,A3&IF(SUM(B4:B5)," / ",""),"")&IF(B4,A4&IF(B5," /
",""),"")&IF(B5,A5,"")

Hope this helps.

Pete

On Feb 13, 9:07*pm, Sean wrote:
How could I express the following as a Text string

I have 3 Names in A1:A3 (Jim; Bob; Mike); in B1:B3 is a value of
either 1 or 0 (1;0;1). I wish to construct an opening statement
depending if the values in B1:B3 are 1, so for example if B1=1;B2=0
and B3=1 my opening statement would be:

Dear Jim / Mike

if B1=0;B2=0 and B3=1 my opening statement would be:

Dear Mike

if B1=1;B2=1 and B3=1 my opening statement would be:

Dear Jim / Bob / Mike

if B1=0;B2=0 and B3=0 my opening statement would be:

<blank

I have 5 names in total so haven't put all the permutations above,
perhaps there is not an easy way to do this

Express 'Joined up' IF's Q
 

Thanks David, I had a #value returned, but spotted where I went wrong

Thanks for the other posts too