Express 'Joined up' IF's Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

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
 
=IF(B1+B2+B3,"Dear "&IF(B1,A1,"")&IF(B1*(B2+B3)," /
","")&IF(B2,A2,"")&IF(B2*B3," / ","")&IF(B3,A3,""),"")
 
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.
 
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?
 
=IF(SUM(B1:B3),"Dear ","")&IF(B1,"Jim"&IF(SUM(B1:B3)>1," /
",""),"")&IF(B2,"Bob"&IF(SUM(B2:B3)>1," / ",""),"")&IF(B3,"Mike","")
 
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
 
Thanks David, I had a #value returned, but spotted where I went wrong

Thanks for the other posts too
 
Back
Top