+ Reply to Thread
Results 1 to 7 of 7

Clustered Column with Centered Markers

  1. #1
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    Clustered Column with Centered Markers

    Centering markers over respective clustered columns
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    Re: Clustered Column with Centered Markers

    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?

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Clustered Column with Centered Markers

    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.

  4. #4
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    Re: Clustered Column with Centered Markers

    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?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Clustered Column with Centered Markers

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Contributor
    Join Date
    06-30-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2013
    Posts
    251

    Re: Clustered Column with Centered Markers

    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).
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Clustered Column with Centered Markers

    Even when I switch to Scatter plots, the markers/points don't line up over their respective columns.
    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.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 10-29-2015, 10:58 AM
  2. [SOLVED] Format markers based on an on/off column
    By Charles Murphy in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-25-2014, 07:25 AM
  3. markers for error bars in clustered column graph
    By Andreea C. in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 11-20-2013, 10:18 AM
  4. Excel top row column markers
    By julio torres in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-31-2011, 12:11 AM
  5. Clustered Column with a Z axis?
    By geff in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-12-2008, 08:37 PM
  6. Replies: 2
    Last Post: 08-02-2006, 11:10 AM
  7. [SOLVED] HOW DO I REMOVE MARKERS IN A COMPLETE COLUMN?
    By MEANLEANDEANE in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-30-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1