+ Reply to Thread
Results 1 to 30 of 30

Placing A Border Around Specific Data in a Scatter Chart

  1. #1
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Placing A Border Around Specific Data in a Scatter Chart

    Hi guys, I have a scatter chart and I am trying to put a border around the bottom 15th percentile of the data. Do any of you know how to add borders? I don't mean regular borders like around the entire chart. Thank you for your time and help if able.
    Attached Files Attached Files
    Last edited by Dexter2; 02-09-2021 at 05:10 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    Re: Borders

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Borders

    Hello there, what is wrong with this title?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    Re: Borders

    Itís one word - it tells us nothing about the issue you wish to solve.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,514

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Thank you. Thatís what we expect for all threads, please.

  6. #6
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    No problem! Thank you for the clarity.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    It's not clear to me what you are trying to do. As a guess, it sounds like you want to draw a horizontal line on the chart at the 15th percentile. If I guess correctly, I would:

    1) Charts don't do much analysis, so, if I want to something with the 15th percentile, I need the spreadsheet to calculate the 15th percentile. Assuming it is correct for your application =PERCENTILE(F2:F7,0.15) somewhere convenient in the spreadsheet.
    2) If I want to "draw" something on a scatter chart, simply calculate/enter the "corner" points in the sheet and add the data as a new data series. Assuming a simple horizontal line, enter 0 and 35 into a convenient pair of cells. In the adjacent column, enter the PERCENTILE() function (or a reference to the cell containing the PERCENTILE() function. Then add these cells as a new data series (x values in the first column and the y values in the second column). Format the new series to have a line of the desired color and no marker.

    Is that close to what you are trying to do?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Yes I believe that is correct. I am attempting your step 2 to see if it worksout.

  9. #9
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    I added the X cell and Y cell but won't let me submit when I select "Ok". Am I supposed use the PERCENTILE formula as the series name?

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    I'm not understanding what error(s) you are getting. Were able to add the PERCENTILE() function to a spreadsheet cell and get the correct result?

    Once you have entered the desired X and Y values in the spreadsheet, how are you adding those to the chart as a new data series?

    You could use percentile (or any other valid name) as a named range, if you find it easier to remember the range name, but naming the range is not necessary to adding it to the chart.

  11. #11
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Nevermind, I got it. I have 0 in H2, 35 in I2, and =PERCENTILE(F2:F7,0.15) in J2. I added the data series using H2 for X and I2 for Y. Now that series have been added. So far, is this correct? I did the same thing for column D to be on the safe side.

  12. #12
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Or, do I do H2 for X and J2 for Y and then added another data series using H3 and J3?

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    What I had in mind was something like:

    1) Enter 0 in H2 and 35 in H3.
    2) Enter =PERCENTILE($F$2:$F$7,0.15) in I2. Enter =$I$2 in I3.
    3) Add a data series to the chart that consists of H2:H3 for the X values and I2:I3 for the Y values. Format as line without marker to draw a horizontal line between X=0 and X=35 at the Y level of the 15th percentile.

    Add a 3rd point (35 in H4 and 0 in I4) and extend the data series to draw a rectangle (using the axes as two sides) around the 15th percentile.

    Is that closer to what you are trying to do?

  14. #14
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    How do you extend the series?

  15. #15
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Ah, I got it. The new line (to form the rectangle) is not as thick as the other line. When I increase the pt. the original line gets thicker instead. Also, I think it may be opposite, the border should be the bottom half.

    This is what I am trying to create.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Dexter2; 02-10-2021 at 01:27 PM.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    I'm not sure why Excel is only applying the formatting to one portion of the data series. When you format the data series, make sure that you select the entire data series (click once on the data series) and not a single data point (click once on the data series and click again on one point in the data series). The surest way (if it is still available) is to find the drop down list of available chart elements (in my older version it is in the Chart tools -> Format ribbon) and select the appropriate data series from that list.

  17. #17
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    I'm sorry we posted at the same time, on my end. Please see the above attached.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    The "crosshair" effect is probably easiest using a data series with a single data point. It's not clear to me from your picture how you are calculating the "crosshair" point. You have labeled both vertical and horizontal lines as "15th percentile" (which might actually be the 85th percentile depending on exactly how you are understanding percentile -- Excel's PERCENTILE() function is counting percentiles from low to high and your chart, I notice, has the y axis in reverse order where the smaller numbers -- the 0th percentile -- are at the top and the larger numbers -- the 100th percentile -- are at the bottom).

    If I assume you really mean 15th percentile as labeled, then I would:

    1) Compute horizontal position of the crosshair as =PERCENTILE(E2:E7,0.15) in H2
    2) Compute vertical position of the crosshair as =PERCETNILE(F2:F7,0.15) in I2.
    3) Add H2:I2 as a new XY data series where H2 is the X value and I2 is the Y value.
    4) Add error bars to this new data series and format the error bars to have the correct length (might need to use a "value from cells" option where you have the desired lengths calculated in different cells) and the desired line and end cap style (and whatever other formatting you want).

    The shaded area is more difficult, because Excel does not have a build in way to add a shaded area to a chart. Typically, one will add another data series as a bar, column, or area chart. Then tinker with values/axes/etc to position and size the shaded area correctly. The technique is outlined here (https://peltiertech.com/fill-under-b...n-excel-chart/ ) as a scatter+area combination chart (area chart will provide the greatest flexibility for size and position of shaded area).

  19. #19
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Thank you, I believe technically you are right it is 85 percentile, but the affected data to be in question is 15th percentile which is why its labeled as such.

    Would you mind explaining step 4, a little bit more in depth?

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

    Re: Placing A Border Around Specific Data in a Scatter Chart


  21. #21
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Thank you for this. How do you calculate desired length? I'm not sure if its Standard Deviation or Standard Error, I only have the picture to go off of.
    Last edited by Dexter2; 02-10-2021 at 03:32 PM.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    I wouldn't expect to use either standard deviation or standard error. For your purposes, the length is how long you want the error bar to be -- in your picture long enough to extend to the "edges" of the plot area. So, in your picture, the crosshair is about at (18.5, 7) [(percentile_x,percentile_y)]. Your picture shows the left (negative x error bar) extending 18.5-10=8.5 units to the left of the data point, the right (positive x error bar) extending 35-18.5=16.5 units to the right, the upper (negative y -- because the y axis is reversed) error bar extending 7-2=5 units up and the lower (positive y) error bar extending 8-7=1 unit down. Basically, choose an end value for each error bar, and the distance for that error bar is end_value-percentile_value.

    The one thing that will make this tricky in Excel is if you have your axis maxes and mins set to automatic, Excel will "extend" the axes according to its algorithm for making sure the error bars "fit" inside the plot area (which means they won't extend to the edge of the plot area). You will need to decide if you want to manually set the axis maxes and mins to fixed values so you can extend the error bars right to the edge, or if you want to let Excel automatically adjust the axes limits and have the error bars fit inside the plot area.

  23. #23
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Hopefully this is a step in the right direction.
    Attached Files Attached Files

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Sorry for the delay in responding. The chart looks pretty good to me, and looks substantially similar to the picture in post #15. Do you have any questions about the chart at this point?

  25. #25
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Hey, no problem at all, thank you for your help. It appears the error lines use both the 15th percentile and the 85th percentile, why I don't know. Especially when the chart says 15th in both places. Do you know if those labels are just added text or is there a way that I don't see to add the error bar text?

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    To my knowledge, Excel does not have a built in "error bar label text" feature. I would expect that whoever added those "15th percentile" text blocks to the chart used either text boxes (that would need to be manually positioned) or data labels attached to a dummy series. In either case, the programmer was the one who entered "15th percentile" into the text/data label box and not Excel.

    If you are unfamiliar with adding data labels to a data series: https://support.microsoft.com/en-us/...2-f467c9f4eb2d
    If you are unfamiliar with adding text boxes: https://support.microsoft.com/en-us/...2-250967c7828c

  27. #27
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Thank you for all of your help. I honestly just did trial and error with the series count. Is there an actual way to count or know the actual location of the x and y axis when implementing a line series? Similar to how you were able to tell how far the lines should go.
    Last edited by Dexter2; 02-16-2021 at 11:58 AM.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Not sure I'm understanding. The position of the x and y axes are determined by the properties of each axis. In your sample, for example, the "horizontal axis crosses at" property of the vertical axis is set to "at maximum" and the maximum value of the axis is set to a fixed value of 8 and in reverse order so the maximum value is at the bottom of the axis. Based on how you have manually formatted the axis, you could enter 8 in a cell and use that cell wherever your spreadsheet formulas need to know the position of the horizontal axis. Repeat for the other axis properties that you have manually fixed and need the spreadsheet to know.

    If you are looking for some built in way for the spreadsheet to directly axis these properties of the chart, Excel does not have this feature built in. You can use VBA to make axis properties (and other chart element properties) readable (and writable) by the spreadsheet, but that might be more involved that you want or need, since I'm not really sure how you are trying to use these data and chart together. If you decide to look into this, I would start with this tutorial https://peltiertech.com/link-excel-c...lues-in-cells/ and this https://peltiertech.com/chart-udf-control-axis-scale/ to see how VBA can allow the spreadsheet and the chart to share these properties between them.

  29. #29
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    191

    Re: Placing A Border Around Specific Data in a Scatter Chart

    My skillset is definitely not into VBA yet, so I am definitely saving that thank you for sharing. I think what I was trying to describe I was trying to use the percentile formulas, instead of eyeballing the chart and simply plugging in the digits to match the visual. Even with this chart, I don't understand how the digits (1 and 4) used in the line placement are why they are.

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

    Re: Placing A Border Around Specific Data in a Scatter Chart

    Sheet1 is doing the calculations for "translating" the axis information for the XY scatter chart axes (primary axes that are shown) to the axis information for the area chart axes (secondary axes that are not shown). I'm not entirely sure why you are using 0 to 4, either. Here's what I would have expected:

    1) For the horizontal axis translation, it appears that you want to translate from the current axis limits (10 and 35) to the 0 to 1000 axis limits that Peltier recommends. I would enter 10 in sheet1 C3 and 35 in sheet1 C5. Then sheet1 C4 can be =percentile_x (or reference to Data!H2). Remember that C2:C5 are the data for the XY primary X axis.
    2) Similarly, the values in sheet1 D3:D5 are going to help translate the 8 to 3 values (8 at bottom and 3 at top) for the XY primary Y axis onto the area chart's secondary Y axis (which appears to be 3 to 8 with 3 at the bottom and 8 at the top). I would expect to enter 3 in sheet1 D3, 8 in sheet1 D5, and a formula to translate the percentile_y value onto this inverted axis in D4. Something like =D5-Data!I2+D3.

    The idea is to remember that the data and formulas in Sheet1 are "translating" the XY data onto the area chart axes so that they make sense. The key to making these values make sense is to make sure you are using the actual values from the XY chart axes and not arbitrary values.

    I didn't do so this time, but many times, I find this easier to understand and follow if I have the chart display the secondary horizontal and vertical axes while I am building the chart. Then, when the chart is completed, I will hide (not delete) the secondary axes by formatting as invisible (no tick marks no line no labels ...). That way I can easily visualize the translation from one axis system to the other while building and understanding the formulas in sheet1. After making the changes above, you might go ahead and display the default secondary axes (you may need to format the secondary vertical axis to have the correct axis limits 3 and 8) to better visualize what the formulas on sheet1 are doing.

+ 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. Conditional Formatting row borders, eliminate Inside Borders within row
    By lizziemac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2018, 11:06 PM
  2. Replies: 2
    Last Post: 06-27-2017, 12:55 AM
  3. [SOLVED] Auto change borders to thick borders
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2013, 11:17 PM
  4. VBA help with borders.
    By Kingviper74 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2013, 01:42 AM
  5. Inserted Borders will not print
    By CarolineManifold in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-15-2012, 10:44 AM
  6. Replies: 3
    Last Post: 02-20-2012, 05:09 PM
  7. borders
    By Brush Prairie in forum Excel General
    Replies: 0
    Last Post: 08-22-2005, 10: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