• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Line chart on a bar chart

emmatm

emmatm

Member
Hi - any help will be much appreciated, if you see my attached file (Master tab) I have a few lines that represent forecasts on my bar chart. I am trying to get the lines to start at the beginning where the firs blue bar is and ends at the last blue bar rather than centre of the chart - any ideas on how t do this?
Also if I try and hide row 4 on he master this then removes my line chart, how can I hide without this happening?
 

Attachments

  • Project Data v7a.xlsm
    815 KB · Views: 6
Hello again emmatm,

Looking at the data for the line charts, one problem is that each of the lines has 5 data points (row 4:8), while your columns only have 4 data points (5:8). This is why you have the extra blank space.

Next, understand that the line only has a single point it's drawing to for each label on the x-axis. IF you only show 1 label on the x-axis, you just have a single point, and there won't be any visible line. Or, you can show all 4 of your labels, and the line will instead connect to the points, which will be centered over the label.

Your last question is the easiest to answer. Right-click on the chart, go to Select data. In bottom left is box for "Hidden and Empty Cells". Click that, and check the box in the dialogue that appears.
upload_2016-1-12_13-30-33.png

On a side note, I would suggest a chart design change. Currently, you've got the lines and columns are next to each other, but it seems more likely you'd want to compare the forecast to the actual, e.g. forecast of Agile Desks to Allocated Agile desks. Changing data layout to this:
upload_2016-1-12_13-34-42.png

Lets you make this chart:
upload_2016-1-12_13-34-50.png

Which IMO is easier to read. I can now see that we're good at forecasting offices, but greatly overestimated on Agile Desks.
 
Thanks Luke,

This document will run both groups of business lines and individuals (individual is on version 7a, group see version 7b where we have more than one office or floor or both)
Can you show me how the above proposed layout will work on version 7b?
 

Attachments

  • Project Data v7b.xlsm
    814.7 KB · Views: 3
Not sure what your last post as asking. Do you need to compare the number of desks in row 5 with the desks in row 6? Chart I did above compares numbers on just on line as they are were within what appeared to be one category.

The other view might be to look at all Office occupancy, but across the different categories?

PS. Note that your terms of "business lines" and "individuals" don't mean as much to us, as not being in your business we're not familiar with lingo. IT would help if you include cell references/sheet names when asking questions so we know where to look.
 
Sorry Luke,

So what I am trying to ask is depending on what client will be pulled into cell B1 the table starting in row 3 column B-L on the master tab will pull different amounts of data. So your chart you recommended above would work well for version 7a where this only has a single line of data in row 5.
If you see my version 7b where I have multiple lines of data from row 5 - 8 on the master tab, how would you recommend the chart to be displayed?

Sorry charts and graphs are all very new to me.

Thank you very much for all your help I am learning a lot!
 
Ah, that starts to make more sense.

Stepping back for a moment, before creating a chart, we'd want to know what story/idea we want to convey. Just because we have a lot of data doesn't mean it should all get charted.

So, things to ask:
Do we need to compare all the items in rows 5:8 at the same time (aka, against each other), or could we use a dropdown to pick which one we want to look at?
Similarly, do we want to see all the items in C3:L3 at the same time? If the idea to compare forecast to utilized? In which case, perhaps we don't need to know about # allocated in this view.

The attached shows a basic chart where user can change which row to look at. Chart data is on Master, cells O44:V45. Play around with it to get an idea of how you can make one chart that has multiple uses. Then, try to think about which stories/ideas you want to convey from the data.
 

Attachments

  • Project Data v7b LM.xlsm
    819.5 KB · Views: 1
Yes I do want to compare all items in rows 4-8 at the same time, so basically exactly what you have given me but including all 4 options on your dropdown.
Unfortunately I do need to know C-L all at the same time, if forecast is greater than allocated this business unit may need to be moved to another area entirely. If usage & forecast is less than allocated then that 1 line of the business unit can be downsized to another area.
Because these business areas are all part of the same group they need to be reflected on the same graph!
 
For the offices (single/double), there is only a single number and the forecast, vs. the desks seem to have Allocated, Utilized, and forecast. Should there both an allocated and utilized number for offices? If not, which one of the two is what's currently being given?
 
The single & double offices will represent allocated, we also have a forecast for the offices but not utilised.
Will this cause a problem?
 
Okay, here's the route I would take then if you need to show everything at same time.
upload_2016-1-13_14-24-28.png

Forecast has been changed to markers, as the line gave wrong impression that each item was connected (like a time line). Now each item is aligned into a single column, where you can compare allocated vs. utilized, and also see how forecast aligns. I went with assumption that comparing desks/offices within a single business was more important, so they are arranged closer to each other. You could have it reversed, where the main categories on bottom at desks/offices and the smaller repeating categories are the businesses. Just depends on what needs to be compared.

Chart is a column chart. Forecast series changed to be a line chart, with markers but no line. Column series set to 100% overlap. Allocated has thicker border, white fill. Utilized has dashed border, gray fill with 50% transparency.
 

Attachments

  • Project Data v7b LM2.xlsm
    821.6 KB · Views: 14
Last edited:
The table that the chart is pulling from what formulas did you use to get this data?
N49:R65

Thank you
 
I'm afraid I just manually built the layout for testing purposes. I'm not fully familiar with the original data or how it's laid out to advise what type of formula to use to extract. If you point to which columns in original data have the numbers, I can try to help?
 
Last edited:
You must log in or register to reply here.
Back
Top