+ Reply to Thread
Results 1 to 11 of 11

Creating shaded zones within scatter graphs

  1. #1
    Registered User
    Join Date
    08-06-2020
    Location
    Leeds, England
    MS-Off Ver
    Excel 2016
    Posts
    5

    Creating shaded zones within scatter graphs

    Hi,

    I've recently been struggling in trying to create shaded zones within a scatter graph. I need to have an exact zone to show that value meets the maximum and minimum requirements but I've found it hard to do this with the normal bar chart or area graph method I found online. Do you have any idea of how I could go about creating thises zones?

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Creating shaded zones within scatter graphs

    Hi and welcome.

    Is this what you want? It is a stacked area combo chart. The regions are set up as stacked areas which means you'll need to work out the starting level and add on it it the amount to get to the maximum boundaries (ie they add together so if you want the lower boundary 1 and the upper boundary 5, your data will be 1 and 4 to add to 5). Set these up as a combo chart with the regions as stacked area and your data sitting over the top as line or scatter chart. Easier to look at the attached example and play with the numbers to see what it is doing.
    Attached Files Attached Files
    Happy with my advice? Click on the * reputation button below

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

    Re: Creating shaded zones within scatter graphs

    Crooza has demonstrated the technique in Excel. Are you required to use Excel? I note the Gnumeric has a built in feature in its XY scatter charts to fill underneath an XY data series. I could open your file in Gnumeric, add the upper range data series for Zone 2, then format the Zone 1 and Zone 2 data series to fill beneath without needing to create the stacked area combo chart.

    If that is an option...
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-06-2020
    Location
    Leeds, England
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Creating shaded zones within scatter graphs

    Quote Originally Posted by Crooza View Post
    Hi and welcome.

    Is this what you want? It is a stacked area combo chart. The regions are set up as stacked areas which means you'll need to work out the starting level and add on it it the amount to get to the maximum boundaries (ie they add together so if you want the lower boundary 1 and the upper boundary 5, your data will be 1 and 4 to add to 5). Set these up as a combo chart with the regions as stacked area and your data sitting over the top as line or scatter chart. Easier to look at the attached example and play with the numbers to see what it is doing.
    Thanks for the reply, sorry I probably wasn't clear enough with my initial explanation. I've tried using the stacked area and
    stacked bar chart before, but unfortunately they didn't work as I'd hoped. I also need to limit the zone in the x-direction and
    when I try to do this it always seems to create slopes when using the area chart. Would there be a way to make this slope
    vertical?

    Thanks in advance
    Attached Files Attached Files
    Last edited by CLin123; 08-07-2020 at 11:33 AM.

  5. #5
    Registered User
    Join Date
    08-06-2020
    Location
    Leeds, England
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Creating shaded zones within scatter graphs

    Quote Originally Posted by MrShorty View Post
    Crooza has demonstrated the technique in Excel. Are you required to use Excel? I note the Gnumeric has a built in feature in its XY scatter charts to fill underneath an XY data series. I could open your file in Gnumeric, add the upper range data series for Zone 2, then format the Zone 1 and Zone 2 data series to fill beneath without needing to create the stacked area combo chart.

    If that is an option...
    I'm not required to use excel as I only need the graphs in the end so that would be a great help. Would I be able to easily reproduce this method as I have a lot of other graphs similar to this one I need to make?

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

    Re: Creating shaded zones within scatter graphs

    Would I be able to easily reproduce this method as I have a lot of other graphs similar to this one I need to make?
    Gnumeric's chart engine and user interface is quite different from Excel's, so there would be a learning curve up front learning how to create and format the chart. As with most things spreadsheet related, though, once you've invested the time to learn the user interface and build a good template, creating the chart multiple times is then as simple (if tedious) as doing a bunch of copying/pasting.

    Do you want to pursue this angle?

  7. #7
    Registered User
    Join Date
    08-06-2020
    Location
    Leeds, England
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Creating shaded zones within scatter graphs

    Quote Originally Posted by MrShorty View Post
    Gnumeric's chart engine and user interface is quite different from Excel's, so there would be a learning curve up front learning how to create and format the chart. As with most things spreadsheet related, though, once you've invested the time to learn the user interface and build a good template, creating the chart multiple times is then as simple (if tedious) as doing a bunch of copying/pasting.

    Do you want to pursue this angle?
    Yes please, that would be great.

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

    Re: Creating shaded zones within scatter graphs

    I don't have Gnumeric in front of me here, so I will need to remember my steps from yesterday.

    1) Open the file in post #1 in Gnumeric.
    2) Select the chart and bring up the chart properties dialog.
    3) From the list of available chart elements, find the "zone 1" data series in the "plot xy 1" group.
    4) Select this data series, find the dialog with a "fill type" drop down. There should be a drop down for the fill style (patterned, gradation, picture, none...) and "drop down to" with different options (y axis, x axis, next data series,...). Select the desired fill style and tell it to drop down to the x axis (or y origin -- I'm not remembering exactly how they worded it).

    You should have a filled block that extends from the current zone 1 series down to the horizontal axis.

    Reading between the lines, I assume that the intention for the zone 2 fill is to have it fill between 16000 and 1250 (the max and min given in the spreadsheet). You've only added the minimum value to the chart. If you want the fill to be between this minimum and the x axis, then repeat the same steps. However, if I've guessed correctly and you want to fill between the min and max, then:
    5) Add the maximum data series to the chart. From the same chart properties dialog, select the plot xy1 group and add a data series to plot xy1. Have this series use I5:I6 for the x values and J5:J6 for the y values. Move this data series "up" so it is listed just before the zone 2 minimum data series.
    6) Still in the chart properties dialog, select the new data series and format it to "fill" just like you did for the zone 1 data series. The only difference is that you will "fill to next data series" rather than to the axis.

    Your current example shows zone 1 filling to the axis, but, reading between the lines again, it might be possible that zone 1 may also sometimes have a non-zero minimum. If that is possible, then you will want to do zone 1 the same as steps 5 and 6 for zone 2 where you fill between data series rather fill to the axis.

    Assuming that you are able to navigate Gnumerics user interface to find these dialogs, that is how you can get these filled "blocks". Let us know how you get along.

  9. #9
    Registered User
    Join Date
    08-06-2020
    Location
    Leeds, England
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Creating shaded zones within scatter graphs

    Wow, thanks a lot!! That looks great and exactly what I need.

    I've been trying to download and install Gnumeric but had some trouble as it seems hard to download it onto windows10? Is there a windows friendly version you know about or perhaps a easy way to install it otherwise?

    Thanks again for all the help!!

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

    Re: Creating shaded zones within scatter graphs

    If I understand correctly, the developers of Gnumeric stopped compiling a Windows version several versions ago, so that it is only readily available for Linux systems. I understand that someone who knows their way around such things can take the Gnumeric source code (it is open source, after all), and compile their own version for Windows, but I have no idea what that process looks like. Unfortunately, I only know about running Gnumeric on Linux, so cannot help with Gnumeric on Windows.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating shaded zones within scatter graphs

    I have used the stacked area approach. The key to getting the vertical lines, rather than slopes is to use secondary horizontal axis with dates rather than categorys.

    I included a Factor and Offset because I'm sure old version of excel could not handle the negative/small values.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

+ 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. Creating graph with two axes, plus shaded region
    By rocky20 in forum Excel General
    Replies: 1
    Last Post: 04-02-2016, 05:56 PM
  2. Scatter Graphs
    By air_musicpromo in forum Excel General
    Replies: 2
    Last Post: 04-30-2011, 10:36 AM
  3. Scatter graphs
    By Cartref in forum Excel General
    Replies: 0
    Last Post: 04-12-2010, 04:14 PM
  4. Correlation and scatter graphs
    By Greg_Norman in forum Excel General
    Replies: 1
    Last Post: 10-21-2009, 01:08 PM
  5. Scatter Graphs and Labels
    By FastOneBaz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-04-2007, 08:46 AM
  6. XYZ scatter graphs
    By ojbarca in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-18-2007, 08:59 AM
  7. Scatter Graphs
    By dcinc74 in forum Excel General
    Replies: 1
    Last Post: 07-25-2006, 09:35 AM

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