+ Reply to Thread
Results 1 to 6 of 6

Graphical representation through line graphs

  1. #1
    Registered User
    Join Date
    05-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Graphical representation through line graphs

    Hi All,

    I work in the electricity industry and wish to graph the following comparisons in the most efficient graphs as possible. We do job for power stations in maintaining their equipments and this work is made up of four parts:
    1. Work pack review ( where the plan is organised before the work delivery)
    2. Switching ( This includes switching out and in the circuit before and after the work is delivered.
    3. Planned Maintenance ( The actual maintenance work on the equipment)
    4. OMGS (Other material, goods and services i.e miscellaneous overheads supplied by third parties).

    Prior to the work being delivered we provide quotations to the customer that this work will going to cost you this much. Recently our estimates and final costs have had large variances and therefore I am required to work on presenting startegies on producing accurate quotes and cost cutting techniques. In the regard I have the following data which needs graphical representation, ideally line graphs. I need to compare 2012 work against 2013 work where we had cosniderable improvements in costing and estimations. Ideally three graphs one for 2012, one for 2013 and comparing both 2012 and 2013. Can you please advise the process for the chart as well just in case i need to alter any figures.

    The data is attached in the form of excel file. Please feel free to ask any questions.

    Many Thanks
    Franklin
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Graphical representation through line graphs

    There are 3 options in the attached file. If one of them is a solution for you, say which one and I'll write the steps.

    Are you sure you want a line chart? Two of these options are line charts and they don't really make any sense. They show differences but the trends they reflect are - well, they're not trends. The third chart is an arrow chart. It's made from stacked columns and may be more appropriate for your story. It's set up to show profit/loss for each type of business compared to the estimates. Maybe that's not the story you want to tell. We can change the series to reflect the story you want.

    Line charts, UK.xlsx

  3. #3
    Registered User
    Join Date
    05-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Graphical representation through line graphs

    Thank you Mr. Johnston,

    I really appreciate your valuable time & effort.

    You are absolutely right, arrow charts are definitely much better and easier for the audience to understand. Can I please ask you to include a dual y axis if possible for the hours or may be a separate arrow chart for the comparison of the hours element of the job.

    Yes you may please include the steps .

    Warm Regards
    Franklin

  4. #4
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Graphical representation through line graphs

    Here are the steps so you can modify the arrow chart for your needs:
    1. Create 3 series as shown in columns B-D of the "Arrow Chart" worksheet.
    2. Select A1:D9 and create a Stacked Column chart (Insert-Charts-Column-2-D-Stacked column)
    3. NOT SHOWN Given the short length of some of the arrows, you might want to reduce the Gap Width to 50% to make their dimensions more apparent. Format data series-Series options.
    4. Right-click on the series at the bottom of the column - Format data series-Fill-No fill. (You might have to do the same with the borders.)
    5. Right-click on the series at the top of the column - Change series chart type-XY scatter.
    6. Deactivate the chart. Click on Insert-Shapes. Click on the arrow you want to use in the chart and copy it to the worksheet (not to the chart).
    7. Duplicate the arrow and invert the duplicated arrow 180-degrees. (Click on the arrow you want to invert. Go to Drawing tools-Format-Arrange-Rotate-Flip vertical.
    8. Copy (Ctrl-C) the upward pointing arrow. Click on the Profit series in your chart. Paste the arrow (Ctrl-V), and your profit series will become upward facing arrows. Do likewise with the downward facing arrow and paste it to the Loss series in your chart.

    I'll add a secondary vertical axis and write out the steps in the next 24-hours.

  5. #5
    Registered User
    Join Date
    03-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Graphical representation through line graphs

    Chart with secondary axis in attachment.

    To get the secondary axis into your chart, enter your data in the worksheet as shown in the example. You must offset the rows so that the time and the currency will not be aggregated into the same stacked column; also offset the columns of the worksheet so that your hours can be selected as a separate data series.

    Build your chart per the instructions in my earlier post.

    Now right-click on the 3 components of the hours arrows - Blank2, Hours underestimated and Hours overestimated - Format data series, secondary axis. Because each hours arrow is built from 3 series, you're going to have complete this action - once for each data series.

    Choose your colors, labels, titles as you want and I hope you're good to go.

    Good luck with your presentation, Franklin. If you have questions, please post them.

    Line charts, UK.xlsx

  6. #6
    Registered User
    Join Date
    05-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    18

    Re: Graphical representation through line graphs

    Goodevening Sir,

    Thank you so much.

    I am grateful for your esteemed help and support.

    Keep up the good spirits. I am sure I will be able to impress my audience. I dont require any more response, I'm happy.

    God Bless
    Much Appreciated.
    Franklin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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