+ Reply to Thread
Results 1 to 3 of 3

multiple data sets in a single chart - need a single legend entry/color

  1. #1
    Registered User
    Join Date
    04-13-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    multiple data sets in a single chart - need a single legend entry/color

    Hi everyone,

    Thanks very much for taking the time to help. My issue is as follows:

    a) Once a week I collect data on the price of apples, bananas and carrots at my local grocery store. So for example, on 14 April 2013, I will have the prices for all three of these products.

    b) I then make a prediction on how much these prices will change over the course of next week. This will be my other set of values - predictions, also attached to the date I made them, i.e. 14 April 2013.

    c) Next week (21 April 2013) when I do go to check the prices I will know whether or not my predictions were accurate, so I will have the actual changes in prices, which will be attached again to the same date 14 April 2013, since this is the week during which the change had taken place.

    d) I will also store the new prices which will be available on 21 April 2013, make my predictions for the following week (i.e. April 28), and then go and check those prices in a week's time.

    I will repeat this extremely interesting process every week, so that every date will have the following information attached to it -

    - prices
    - predictions in price change
    - actual price changes

    To make things more interesting I will also add a couple more values which are calculated based on the observed data:

    - my prediction as % of prices when the prediction was made
    - actual price change as % of prices when the prediction was made

    Once I have all this data I want to plot it in a single graph (please see attached).

    The problem is, I want all the data associated with a particular date to be associated with a single color, so that I don't need multiple legend labels. The other issue is, I may have up to 20 products to worry about, in which case the bars start shrinking on the bar chart and become less visible. If you can solve this conundrum and/or have ideas on how I can present this type of data in a more visually appealing manner, please do let me know.

    Many thanks

    Jim
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: multiple data sets in a single chart - need a single legend entry/color

    Here's how I would start off...
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-13-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: multiple data sets in a single chart - need a single legend entry/color

    Thank you very much Ben, looking at your solution right now. Incorporating some VBA code in the chart as well - will revert asap!

    Jim

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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