Using CONCATENATE function with multiple ifs

**WondersAlo** · 12-03-2007, 08:05 AM

Hey,
I got a problem with the formula that I am trying to produce. The end result is supposed to look something like this:

xxxx is starting soon on channel yyyy (ex. Family Guy is starting soon on channel 48)
or
xxxx is now playing on channel yyyy (ex. Family Guy is now playing on channel 48)
or
""

What I already have is different columns for which TV show, the channel, if it is playing soon, and another for if it is playing or not. What I need to do is put that all together and come out with either of those 3 statements.

Thanks,
WondersAlo
...if you do not understand let me know what you do not understand I will rephrase

**daddylonglegs** · 12-03-2007, 09:02 AM

If A1 contains show B1 contains either "not playing", "now playing" or "starting soon" and C1 contains channel number try

=IF(B1="Not playing","",A1&" is "&B1&" on channel "&C1)

**WondersAlo** · 12-04-2007, 10:27 PM

that works, but it does not apply all the rules. I must make a formula that lets the viewer know if the show is playing soon, or is now playing, and if neither use "".
What I got so far is two IF's but cannot put them together.
=IF(J8="yes",CONCATENATE(A8, " is playing now on channel ", B8),"")
and
=IF(K8="yes",CONCATENATE(A8, " is starting soon on channel ", B8),"")

where
J8 = the playing now or not column (i.e yes or no)
K8 = if the show is playing soon or not column (i.e yes or no)
A8 = the tv show that is playing (i.e Family Guy)
B8 = the channel the tv show is playing on (i.e 48)

**daddylonglegs** · 12-05-2007, 06:32 AM

So presumably only 1 of J8 or K8 can show "Yes" but if neither of them do you want the formula to return a blank?

Try

=IF(AND(J8<>"Yes",K8<>"Yes"),"",A8&IF(J8="Yes"," is playing now"," is starting soon")&" on channel "&B8)

**WondersAlo** · 12-05-2007, 07:58 PM

thanks a bunch, works perfectly...

Using CONCATENATE function with multiple ifs

LinkBack

Thread Tools

Rate This Thread

Display

Using CONCATENATE function with multiple ifs

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions