Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Chart With Multiple Variables

  1. #1
    Join Date
    14th June 2006
    Posts
    21

    Chart With Multiple Variables

    Hi Everybody,

    I am trying to create a column chart in which i want to incorporate 2 variables. However I don't want to use the line for the second variable. What I would like to do is to add the information of the number of deals a company has done on top of the column. So you see a column which gives you the average size of a deal, and the you should see a number at that column to see the number of deals done with that company.

    To make this more clear I have attached an example. There are 2 tables of which the information I would like to see in one chart.

    Hope someone can help me on this.

    Kind regards,

    Sirus
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Creating Chart With Multiple Variable

    2 ways of doing this.

    To have the labels on top of each column you will need to add a duplicate set of data and move it to the secondary axis. Apply data labels and link then to the number of deals cells.

    To have the labels at the bottom of each column add a set of deals data and move to secondary axis. Set alignment position for data labels to be inside Base.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


  3. #3
    Join Date
    14th June 2006
    Posts
    21

    Re: Chart With Multiple Variables

    Andy this great, exactly what I needed. Thank you very much.

    The only thing is that I don't really understand how you got this done. If possible could you talk me through the steps?

    Thanks!

    rgds, Sirus

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Chart With Multiple Variables

    Labels on top chart -

    to get the extra data series into the chart select the range A9:J15 and drag and drop it on to the chart. At the paste special dialog Add cells as New Series. Values in Columns. Tick both Series Names in 1st Column and Categories in 1st row.

    Select the new Jan column series and use the format dialog to move it to the secondary axis. Now repeat for all other new series. You can use the F4 button to repeat changes.

    You should now have a duplicate set of columns infront of the original ones.
    You need to apply data labels to these series and then link the data labels to the ranges with the deal numbers in.
    Here is a link to a free addin that will make that process easy.

    Actually thinking about it you don't really need the 2nd set of repeated data. Simply use the existing columns data labels to link to the cells.

    For the chart with labels at the bottom you can either do the same as above but just change the position of the labels.
    Or add the range A1:J7 to the chart as described above.
    Move to the secondary axis.
    Display data labels for these new series and position at bottom.
    Format columns to have no border or no fill colour.

    Cheers
    Andy


  5. #5
    Join Date
    14th June 2006
    Posts
    21

    Re: Chart With Multiple Variables

    thats amazing!

    Thanks a lot andy!

    rgds, Sirus

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th June 2006
    Posts
    21

    Re: Chart With Multiple Variables

    Hi Andy,

    How do I add the data label? I am having some trouble with that.

    Thanks!

    rgds, Sirus

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Chart With Multiple Variables

    Have you downloaded and installed the addin I suggested?

    If so just select the chart and use Tools > XY Chart Labels > Add Chart Labels.
    Select required series and cell range.

    Cheers
    Andy


  8. #8
    Join Date
    14th June 2006
    Posts
    21

    Re: Chart With Multiple Variables

    Ah have not done that as the firewall at work does not allow me to do that. Is there another way to do this? otherwise I will try to do it at home tonight.

    Cheers!

    rgds, Sirus

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Chart With Multiple Variables

    The addin takes the effort out of doing it manually.

    To do it manually see here

    Cheers
    Andy


  10. #10
    Join Date
    14th June 2006
    Posts
    21

    Re: Chart With Multiple Variables

    Hi Andy,

    I actualy just saw your piece on linking charts to a cell in the book Excel Hacks as well. However, I am having trouble getting this part done manualy. For example I am trying to do this for January, I click chart options and then data labels, but from there I have no idea how to do the link.

    Thanks again mate!

    rgds, Sarju

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Set Chart Source To Non Contiguous Range Variables
    By ryuryubu in forum EXCEL HELP
    Replies: 1
    Last Post: May 17th, 2008, 04:11
  2. Variables Colors In Bubble Chart
    By ddickerson in forum EXCEL HELP
    Replies: 2
    Last Post: November 17th, 2007, 06:04
  3. If Function With Multiple Variables
    By sloaner in forum EXCEL HELP
    Replies: 4
    Last Post: July 24th, 2007, 03:37
  4. Get max value of multiple variables
    By darb44 in forum EXCEL HELP
    Replies: 2
    Last Post: February 7th, 2006, 06:52
  5. Looking up multiple variables
    By iwrk4dedpr in forum EXCEL HELP
    Replies: 2
    Last Post: November 4th, 2004, 13:29

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
  •  
porno