+ Reply to Thread
Results 1 to 10 of 10

How to align bar chart characteristics with data table characteristics

  1. #1
    Registered User
    Join Date
    02-28-2019
    Location
    Paris, France
    MS-Off Ver
    2011 for Mac
    Posts
    5

    How to align bar chart characteristics with data table characteristics

    I have a data table that tracks the probabilistic forecasts of the Federal Reserve interest rate changes. Each day there is a probability that the rate go lower by a certain data...that it will stay the same...or that it will go higher.

    So I have three rows:
    Higher
    Same
    Lower
    Every day I add a column for that day's probabilities, and enter three probabilities. And I created a bar chart that shows the relative percent of each probability.

    The problem is that the bar chart shows the "Lower" portion at the top of each bar, and the "Higher" portion at the bottom of each bar. Here is an image.

    Interest Rate Forecasts.png

    As you can see, the blue "Higher" is at the bottom of each bar, and the green "Lower" at the top.

    I have tried everything that I can think of, to no avail. I am using Excel 2011 for Mac.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to align bar chart characteristics with data table characteristics

    I don't have 2011 here, but can you not reorder the series in the Select Data dialog, or by editing the SERIES formula when selected in the chart (changing the final 1 to 3 and vice versa)?
    Rory

  3. #3
    Registered User
    Join Date
    02-28-2019
    Location
    Paris, France
    MS-Off Ver
    2011 for Mac
    Posts
    5

    Re: How to align bar chart characteristics with data table characteristics

    I have tried both of those things. When I look at the formula, it looks just fine. But the bar chart keeps putting "Higher" at the bottom of the column, and "Lower" at the top of the column. Take a look...

    Select Data Source.png

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to align bar chart characteristics with data table characteristics

    Doesn't appear to be a move up/down option there, so just edit the Name and Y Values for the first series to point to row 5, and the third series to point to row 3.

  5. #5
    Registered User
    Join Date
    02-28-2019
    Location
    Paris, France
    MS-Off Ver
    2011 for Mac
    Posts
    5

    Re: How to align bar chart characteristics with data table characteristics

    OK, I did that. Now the chart looks good, but I have a table, the row name of which does not reflect the data in the row.

    I am stunned -- just really stunned -- that something this basic and this intuitive should be so incredibly convoluted.

    In any case, thanks for helping me work through this.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to align bar chart characteristics with data table characteristics

    I don't follow. The table shouldn't have been changed at all.

  7. #7
    Registered User
    Join Date
    02-28-2019
    Location
    Paris, France
    MS-Off Ver
    2011 for Mac
    Posts
    5

    Re: How to align bar chart characteristics with data table characteristics

    The table didn't change, the row names did.

    So now the row that says "Higher" actually contains the data from "Lower," and vice versa. But the bar chart shows it exactly as I want it: with higher probabilities (labeled "Higher" in the legend) at the top of each bar, and lower probabilities (labeled "Lower" in the legend) at the bottom of each bar.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to align bar chart characteristics with data table characteristics

    I didn't suggest changing anything at all on the worksheet. I suggested changing the chart so that it plotted the Lower data first.

  9. #9
    Registered User
    Join Date
    02-28-2019
    Location
    Paris, France
    MS-Off Ver
    2011 for Mac
    Posts
    5

    Re: How to align bar chart characteristics with data table characteristics

    I fiddled around with the parameters on the chart in every conceivable way. No matter what I did, "Higher" ended up at the bottom of each bar, and "Lower" ended up at the top. So finally I got a little out of the box and ended up with what I originally wanted.

    But meanwhile -- having spent hours trying to make this work with Excel -- I just went to Google sheets and in about 15 minutes had exactly what I wanted.

    One of these days I'm going to learn...

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: How to align bar chart characteristics with data table characteristics

    Now that I have access to 2011, there are a couple of ways to do it:
    1. If you right-click a series in the chart, and choose Format Data Series, there is an 'Order' option in the left hand pane where you can move the series up or down in the chart.
    2. If you select one of the series, you will see a SERIES formula in the formula bar, like this:

    =SERIES(Sheet1!$A$2,Sheet1!$B$1:$D$1,Sheet1!$B$2:$D$2,3)

    the highlighted number at the end controls the plot order so you can just change it for any series directly.

+ 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. [SOLVED] Filter data from table that have unique characteristics
    By L.LEE in forum Excel General
    Replies: 7
    Last Post: 09-16-2017, 06:56 AM
  2. Replies: 8
    Last Post: 06-26-2014, 02:37 PM
  3. Replies: 3
    Last Post: 03-21-2012, 02:08 PM
  4. Sum formula with multiple characteristics
    By rhudgins in forum Excel General
    Replies: 3
    Last Post: 12-20-2010, 05:01 PM
  5. Separating data with similar characteristics
    By oakman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2007, 02:25 PM
  6. Re-formatting data to use DATE-characteristics?
    By dune2 in forum Excel General
    Replies: 6
    Last Post: 08-08-2006, 09:34 AM

Tags for this Thread

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