Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    XY Scatter with a Vertical Band

    Hi All,

    I am in the process of checking the existing salaries of my employees how they are scattered within a range.

    I am looking at a graphe i.e. a XY scatter to plot the salaries of my employees and a vertical band for the existing salary range.

    I have attached an excel shee that has the data and the picture of the vertical band that i need for the XY scatter graph that i will create.

    The file is self explanatory, do let me know if you dont understand do let me know.

    Looking forward to a great solution.

    Regards
    Baiju
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How are points min and points max supposed to relate to the other data? The bands are pretty easy using stacked columns.
    Attached Files Attached Files
    Last edited by rory; 2011-02-03 at 08:41.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is this what you are after? It has variable box widths relating to the points and the box heights related to the salary range.

    I added a lable of the "grade" to the chart as well and included a picture of the table for reference.

    Steve
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Ah, now I see. You can ignore mine then.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    FYI,
    If you want to fill in the boxes it becomes a little more complicated. It requires a secondary X axis and having the 2nd axis using a stacked area...

    Steve
    PS The filling of the XY areas is done using an adaptation of the technique found at http://peltiertech.com/Excel/Charts/XYAreaChart2.html
    Attached Files Attached Files
    Last edited by sdckapr; 2011-02-03 at 09:59. Reason: Added a PS and link to an description of the filling technique

  6. #6
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Steve,

    This is exactly what i was looking for. Now that I have got the Vertical Band, I need to plot the data that i have in a seperate sheet on to this graph using the XY Scatter graph. How do i do this?

    Regards
    Baiju

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    right click the chart I prepared select location and select the new sheet option...


    Steve

    PS if it is different data into the chart, just copy and paste the new data into the example table you created...

    If the datatable needs to be expanded the intermediate chart data will need expanding as well. The segments are listed by rows and I think are pretty self-explanatory, but if not you will need to ask specific questions....
    Last edited by sdckapr; 2011-02-04 at 07:43. Reason: Added a PS

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve,

    I am revisiting this post again. You had provided me the vertical band based on min and max salary range and the points min and max.

    Now I have the actual salary of the employee within these grades and the mid point of the company and the survey. I would like to plot them the same graph. Can you let me know how to do the same.

    I have included an additional sheet "Salary data", this has the actual salaries against the grade and the mid points of the salary of the company and survey. I have also included a picture of the final graph i am looking for.

    Is this possible.

    Regards
    Baiju
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this?

    Steve
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    HI Steve,

    Thanks for this. However there is this red line that is showing up as line graph apart from the box. Need to remove that. How do I do it. Im not sure if it is the problem im opening the fie. I have attached the screenshot on the sheet and mentioned the line that needs to be removed.

    I just need the box in red. the Salary, Company Midpoint and Survey midpoint.

    Regards
    Baiju
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You should be able to select the line and then press But I don't see that line on the chart sheet you provided: Only the picture, so I can't tell where it comes from. "Select Data" and look at the ranges and which data looks like that, or post an example with the line in it if you want more help.Steve

  12. #12
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Not sure if it is a problem with excel on my system. I can acutally see those line on the chart sheet of the attachment in the post. I have taken the screenshot of the same and pasted on the For Graph sheet so that you dont scroll between sheets.

    I have reattached the file with a new screeshot that has the range highlighted on the graph.

    Hope that will identify the reason.

    Regards
    Baiju
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    HI Steve,

    I was able to figure out why the line was appearing. It was a problem with the range. I has been fixed now and is working perfectly.

    I wanted to know if it is possible to have the "Salary", "Company Midpoint" and "Survey midpoint" parallely displayed rather they in one single line so that i could do a comparison without an overlap.

    Thanks
    Baiju

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I have the SALARY spread out through the range in the attached by adding some calcs. For each grade I determine the XMin the XMax, the number of salaries in the grade and the item number for that salary within the grade (based on the appearance in the list). The XCalc for that is then:
    XMin + Item/(Total+1)*(xMax-xMin).

    That spreads them out and keeps them from all being at the midpoint.

    I kept the Survey at the Xmidpoint. I made the Company a line going from the xMin to xMax of each grade if desired

    Steve
    Attached Files Attached Files

  15. #15
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve,

    Thanks for the solution, I would prefer it the ways you have done it for me. I just wanted to check if the Company Midpoint and Survey Midpoint could be parallel so that visual comparison is easier. In the current state the Company Midpoint, Survey Midpoint and actual salary point are on the same line.

    I will manage with the solution you have provided. Thanks steve for creating this graph for me. Really helpful

    Regards
    Baiju

Page 1 of 2 12 LastLast

Posting Permissions

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