Insert and Automate Brackets within Chart

[wizzle89]

Hi there!

I'm wondering how these blue brackets are inserted into the chart and if their position can be automated based on the number in the "payback" period cell. For example, if the payback period is 4 years, the "investment recovered" bracket spans 4 years and the "profit" bracket spans 6.
If someone has a better way of automatically distinguishing between investment recovered and profit data based on "payback" period, I'm all ears.

brackets.jpg

Any insight into this would be great! Thanks!

[Andy Pope]

Depending on the construction of you current chart you might be able to use xy-scatter series to draw the lines and add data labels, but it's difficult to tell from pictures.

[wizzle89]

Sorry, I'm new to this but slowly finding my around. Is there anything I can show you that will help determining how my chart has been constructed?

[Andy Pope]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[wizzle89]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thanks very much Andy,

I have attached a sample workbook. The data in there is what I currently have and I want the chart to have the same brackets and wording as the one shown in my original post. It needs to automatically adjust based on the payback period cell.

workbook-for-forums2.xlsx

[Andy Pope]

This should give you the general idea. Basically you are drawing the bracket with a xy-scatter series on the secondary axis.
Another series is positioned in the middle of the bracket so it can be labelled.

You need to set the secondary horizontal axis so it has a maximum of (Number of Categories + 0.5) Then the center of the columns will be 0.5, 1.5, 2.5 etc

[wizzle89]

This should give you the general idea. Basically you are drawing the bracket with a xy-scatter series on the secondary axis.
Another series is positioned in the middle of the bracket so it can be labelled.

You need to set the secondary horizontal axis so it has a maximum of (Number of Categories + 0.5) Then the center of the columns will be 0.5, 1.5, 2.5 etc

Thank you very much Andy, I will play with what you've done here and let you know if I have any further questions.

You've been a tremendous help!

[wizzle89]

Hi Andy,

I've been playing with the supplied chart. I understand how the data point affect the brackets, but can you please walk me though how you added the XY scatter to the secondary axis on my existing chart? I can't seem to recreate that process. I know this is might be relatively elementary, but I'd much appreciate a brief "step-by-step" instruction on how you accomplished that.

[Andy Pope]

Use the Select data dialog to add a new series
Select the new series and via the format dialog change it to the secondary axis
Change chart type of that series to xy-scatter
Use select data dialog to set the x and y ranges
format series as required.

add another series for the label of the bracket. this time it should automatically add xy-scatter series to secondary axis so just set the range reference and format