• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Concatenate Text for a Chart Title

M

m1cajah

New Member
Greetings all!


Having just implemented the awesomeness found in the article "How to cook a delicious dynamic chart that will have your boss drool" on this site (sorry - I would provide a link but am not sure how in the forum) I thought I'd like to add one additional bit of awesomeness...a dynamic title for the chart.


This requires - I think - some conditional concatenation. However when I try it I don't "quite" get what I want.


Let's use the series headings from the article and put them in column A.


Column A

A1=Sales

A2=Profits

A3=Customer


In cell B1 I would like to concatenate these, but with a twist.


If A1 is blank, b1=concatenate(A2, " x ",A3)

If A2 is blank, b1=concatenate(A1, " x ", A3)

If A3 is blank, b1=concatenate(A1," x " A2)

If A1 and A2 are blank, b1=A3

If A1 and A3 are blank, b1=A2

If A2 and A3 are blank, b1=A1


Now, one more twist - I am not allowed to use macros in the workbooks, so no VBA. ;)


After searching all over the web (including here) I've tried various codes/nested "if" things and have gotten close to what I want, but I am having a mental block on executing what is needed to get to my goal. Here's the code I currently have.


Code: 
IF(A1<>"",A1&" x ","")&IF(A2<>"",A2&" x ","")&IF(A3<>"",A3,"")


It FEELS like I am close...but can't quite figure out the nesting properly or something?


Thanks in advance. I'm sure one of the brilliant minds on this site will just laugh at my predicament, but I'll suffer the slings and arrows to learn how to do this. :)


Mike
 
I think if we make the "x" bit separate, we can make things a little simpler.

=IF(A1="",A2,A1)&IF(COUNTIF(A1:A3,"")<2," x ","")&IF(A3="",IF(A1="","",A2),A3)
 
OK! Now that's interesting (especially the countif bit) and it gets me close as well. However - feeling pretty sheepish - I left out one condition.


If A1, A2, and A3 are all "not blank" then it should concatenate the three together as concatenate(A1," x ",A2," x ",A3). Not sure how I managed to leave that one out.


I blame the heat (I'm in West Virginia and we still don't have power...).


Thanks Luke M!!!


Mike
 
No worries. We can just tack on one more IF statement.

=IF(A1="",A2,A1)&IF(COUNTIF(A1:A3,"")<2," x ","")&IF(A3="",IF(A1="","",A2),A3)&IF(COUNTIF(A1:A3,"")=0," x "&A3,"")


Hope your power comes back soon. I'm up in Indiana and it's still close to 100 degrees here. =(
 
Thanks for the well wishes AND the formula update (it performs flawlessly)! Perhaps sometime you could explain how that's working exactly? I can read it in plain English, but not quite sure HOW you arrived at the logic for it. I think it's those "countifs" that're throwing me. :)


Fortunately it's only 92 at the moment outside my window. Tad cloudy so it's helping keep the temps down.


We're supposed to get our power back (in my neighborhood) around Tuesday evening...but compared to the rest of the state our area got off easy. There are still areas that are 100% grid down. I'm able to go to my wife's office to charge my laptop and phone. My phone is a Windows phone with the wifi hotspot capability - so I can still manage to hit the 'net and get a little work done.
 
I'd be glad to explain. We know that we'll always start with A1 or A2. We'd need A1 if there's text there, and if not, A2. The first trick I realized is that if A1 and A2 are blank, it doesn't really matter, and so we could return either one (as it's a null value).

So, we've now got either a value or null.


Next, if's there's 2 blanks within the group, then we don't need an " x " in our wording. If not, put the " x " in there.


If A3 is blank, then we just need to know if the first IF function grabbed A1 or A2. If A1 is blank, then it grabbed A2, and we do nothing. If the first IF function has grabbed A1, grab A2. It doesn't matter if A2 is blank, because if it is, then we already don't have the " x " and we're just tacking on a null value.

If A3 isn't blank, add it to our text string.


Finally, if there were no blanks at all in our group (checked via COUNTIF) then add on another " x " and A3 (since this must be the last value.
 
You must log in or register to reply here.
Back
Top