Centering markers over respective clustered columns
Centering markers over respective clustered columns
the columns themselves should represent Value, LT Momentum, Quality and Dividend Yield. And the markers should represent "Current Portfolio" and "Reporting Benchmark." When more than one column is used, the markers center on the grouping instead of within each respective column. How do I center the marker within each respective column?
Well the short answer is, put this in F33:
=F31/2
and pull across.
A deeper answer though is that the markers are just coming off whatever the values in F33:Q33 are, and the table you're working from has the same value in each one, so it looks more like the chart is doing exactly what it's supposed to with the table, but maybe the table is not pulling data as you expect.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
Ok, understand. But the x axis here is simply a range of proposed portfolios in which to invest and the overall goal is to compare the factor exposures of each of those to the exposure of those factors within the Current Portfolio and Reporting Benchmark. As such, the Current Portfolio row includes the same value intentionally (because that is a single datapoint that I'm trying to force within the structure of the chart). Same idea with the Reporting Benchmark; the data will be the same across the columns. Since I can't change the values in the table (they represent actual exposures), is there a way around this?
Not sure I understand, and your sample chart does not seem to match what you describe (or is not complete, yet). My first guess at dealing with the problem you are describing is this tutorial: https://peltiertech.com/Excel/Charts...olAndLine.html Peltier offers two possible solutions for this -- use a combination clustered column + XY scatter (instead of line) or use a combination line + clustered column with a "sparse" data range. Look it over and see if that describes the problem you are having and if one of the proposed solutions will work for you.
Originally Posted by shg
Even when I switch to Scatter plots, the markers/points don't line up over their respective columns. I've attached another example of what I'm trying to convey. In the example, the black markers should be over the blue columns. the light blue and green lines should be centered over the yellow columns (so if I remove the line from those series' I would see two markers lined up vertically in each yellow column).
Your latest sample file does not include any XY scatter series, so I cannot tell what steps you left out (is it possible you thought that changing the chart type was the only step to this, because that is the only step you mentioned?). If you did not read through Peltier's tutorial or only skimmed it quickly, please spend some more time with it so you can understand how it works.Even when I switch to Scatter plots, the markers/points don't line up over their respective columns.
Here's what I did (only treating the "reporting benchmark value" series since that should be enough to illustrate what is needed).
1) I know I will need a row of numbers for "x values" for each XY scatter series, because the XY scatter series cannot use the text based values in row 30. I enter a row of numbers 0.5, 1.5, 2.5,... in F29:Q29. I will come back to these numbers later when I have a better idea what values I want here.
2) Select the "reporting benchmark value" series in the chart, shift the X values for this series up to row 29, and change chart type to XY scatter (markers no lines, in this case). In my version, Excel automatically added the secondary vertical and horizontal axes to the chart. If your version does not automatically add those elements, then add them manually.
3) The secondary axes are added with automatic limits, which don't help much at all, so I edit the axes to give me more reasonable limits.
3a) Select secondary vertical y axis and set max and min so they match the limits of the primary vertical axis.
3b) Select the secondary horizontal x axis and set limits from 0 to number of categories in primary axis (12 in this case).
4) Now the markers are back where they were. I select F29 and adjust the value until the first marker is where I want it (0.33 seems right), then adjust the other values in row 29 to place the markers where I want them.
4a) I believe Peltier applied some algebra/geometry to this step of the problem so that you can enter a simple formula here, if you want, to compute the best horizontal position for each marker.
5) Repeat for the other series.
6) When you have all of the markers placed where you want them, and you decide that you are offended by the presence of either or both secondary axes, hide those axes (set line, label, tick mark, etc. for these axes to invisible), so they won't offend.
Unless I am misunderstanding, that should work. Which steps did you follow? Which did you neglect?
There's the full response, finally through the firewall (I'm not sure why "( value )" looks like an SQL injection, but that must have been it).
Last edited by MrShorty; 02-09-2018 at 03:45 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks