Complex Chart need. Suggestions Urgently Required

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268
Hello,

I am going to do my best at explaining my needs here.

I have 8 percentage values, all of which represent the percentage of times a value occurs within a certain range on a number line. I want to plot both the number line AND the percentage columns on the same chart. I suspect I may need to plot the number line (single axis line chart) as a transparent 2nd chart on top of the percentage columns chart.

Are there any other suggestions anyone has? Ideally I would prefer to avoid the transparent 2nd chart approach as things do nto line up properly. Ideally I would have everything on one chart so that the percentage bars/columns would be Underneath/Over the part of the number line that they pertain to.

e.g. In a simple case if I have number line of 1-25 and I have 2 column/bar, one of which is product A and is 12% (that is 12% of the time this product falls between 1 and 7) and another column/bar which is Product B and is 66% (that is 66% of the time this product falls between 20-23) then I would want the column/bar for product A underneath the numberline between 1 and 7 and correspondingly the product b column underneath the numberline between 20-23

I don't know if that makes sense. I hope it does. Please help!!!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Hi

Just a quick thought and that is to explore using multiple 'Y axis with a common 'X axis. You have one set of data as a line and the other as columns (or whatever you choose).

To be of more help a sample of your data would be required; though others may have a better understanding!

Regards
 

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268

ADVERTISEMENT

PJMorris-This is the road I was going down, but I can't get it to work.

if you think about it there are two x-axis and two y-values here. The first x-axis would be the groupings, so (%Sells in Range, %Buys in Range etc, %Returns in Range) and the first y-axis would be the %'s 0-100. This with the values would build a column/bar chart series. Then I want to add the number line onto it, and thought I could join it to the columns/bar via having the y-axis of the second series (Number Line) as a constant (say 50 Percent). But my problem is that the x-axis is going to be the groupings, and not a number that I can build the number line from (number line needs to be horizontal over the % columns of the first series.

I essentially need 2 X-axis and one y-axis ?

I have confused myself now.

The Data is simple

% Buys in LHS
% Sells in LHS
% Returns in LHS
% Stock in LHS

% Buys in RHS
% Sells in RHS
% Returns in RHS
% Stock in RHS

Number line is values 1-75
Where LHS is 1-7.5 and RHS is 68.25-75
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Hi Nat,

Apologies for inconvenience, but though the data may be simple for you - I don't even know what you mean by LHS (I would say LHS means Left Hand Side - I suspect that that is wrong!), can you post some sample data as that might help a lot!

Regards
 

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268

ADVERTISEMENT

Hi Nat,

Apologies for inconvenience, but though the data may be simple for you - I don't even know what you mean by LHS (I would say LHS means Left Hand Side - I suspect that that is wrong!), can you post some sample data as that might help a lot!

Regards

Just an FYI I was not being patronising with that comment, however after re-reading it find that it may indeed sound so.

LHS/RHS indeed means left hand side and right hand side. There isn't really any data to post as they are just percentages yielded from formulas pointing at my data set

For Example

% Buys in LHS12
%Sells In LHS25
%Returns in LHS66
%Stock in LHS61
%Buys in RHS35
%Sells in RHS44
%Returns in RHS86
%Stock in LHS24

<TBODY>
</TBODY>

The Number line should be from 1-200 (Spectrum means number line)
Minimum Spectrum Value1
Maximum Spectrum Value200
LHS 10% Spectrum Boundary20
RHS 90% Spectrum Boundary180
Spectrum1-200
LHS Spectrum Range1-20
RHS Spectrum Range180-200

<TBODY>
</TBODY>


Does that make sense ? I want a normal column chart for the percentages, with the number line on the same chart, with the columns of the percentages placed under/above the spectrum ranges in which they reside. I.e. 12,25,55,61 should all be columns under/over the number line between 1-20, with 35,44,86,24 percentages all under the RHS range of 180-200 of the number line.

Thanks
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
I think this is possible, but may not be what you want:

1. In cells A1 to A41: 0, 5, 10 etc
2. In cells B1 to B4: % Buys in LHS, %Sells in LHS... etc
3. In cells C1 to C4: the corresponding percentages.
4. In cells B38 to B41: % Buys in RHS, %Sells in RHS... etc
5. In cells C38 to C41: the corresponding percentages.

I then created a graph, used the column B as the x axis and added the line from cesll A1 - A41, the percentages from C1:C41 (note the blanks simply appear as nothing!). You'll need to play with formating etc and ensure you get the right data assigned to the secondary Y axis.

Not sure there's another solution. The trick is having physically spread the spectrum data over sufficient space to allow 4 elements in LHS and RHS.

Hope this helps.

Regards
 

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268
I think this is possible, but may not be what you want:

1. In cells A1 to A41: 0, 5, 10 etc
2. In cells B1 to B4: % Buys in LHS, %Sells in LHS... etc
3. In cells C1 to C4: the corresponding percentages.
4. In cells B38 to B41: % Buys in RHS, %Sells in RHS... etc
5. In cells C38 to C41: the corresponding percentages.

I then created a graph, used the column B as the x axis and added the line from cesll A1 - A41, the percentages from C1:C41 (note the blanks simply appear as nothing!). You'll need to play with formating etc and ensure you get the right data assigned to the secondary Y axis.

Not sure there's another solution. The trick is having physically spread the spectrum data over sufficient space to allow 4 elements in LHS and RHS.

Hope this helps.

Regards

Hello Peter,

Yet again thank you very much for your assistance. I have tried implementing your solution but cannot seem to get to the scenario you described.
I have as you said
1.In cells A1 to A200: 0,1,2,3....200
2.In cells B1 to B4 %Buys in Lhs, %Sells in Lhs etc
3.In cells B1 to B4 the corresponding percentages
4.In cells B38 to B41: %Buys in RHS, %Sells in RHS..etc
5.In cells C38 to C41 the corresponding percentages

I see where you are going with this... and I like it. I think it is in line with what I'm after, and could write some VBA to populate this when I (knock on wood) actually get the chart right.

But when I insert the chart... what should the series be ?
When I select data, Chart Data Range:'Sheet1'!$A$1:$C$200 with the following composition

Legend Entries (Series)
Series 1 : Series Values =Sheet1!$A$1:$A$200
Series 2 : Series Values =Sheet1!$B$1:$B$200
Series 3 : Series Values =Sheet1!$C$1:$C$200

Horizontal X-Axis Label range ==Sheet1!$B$1:$B$200

But when I then put Series 1 on a secondary axis.... it looks very strange. It is a diagonal line (increasing linear line with chart type as line) going from 0-200 from left to right i.e. 45 degree line...? How do I get it to be a horizontal line instead? Other than that the %'s look good as do their locations across the x-axis
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
Nat,

The strategy I used had A1 = 0, A41 - 200 with steps of 5. This is sufficient to creat the line you seek and provide room for the two sets of 4 data elements to align with the bands 0-20 and 180-200 that you require.

Why would you want a horizontal line? it wouldn't tell you anything and at what level do you want it set? A possible suggestion would be cells B1 to B4 set to 20, cells B5 to B38 set from 5 to 175 in steps of 5 (should give a slope) and then B39 to B41 set to 180.

Also, you will only need two data series: A1:A41 and C1:C41; B1:B41 would be the X axis dataset.

This process should help:

1. Highlight cells B1:C41, insert a column chart.
2. Select the horizontal axis and set the alignment to vertical.
3. Right click on the chart and click 'select data'. In the dialogue that appears, click on Add in the left hand area. Then replace the ={1} with =Sheet1!$F$1:$F$41 and name the series appropriately. You will now have a load of unhelpful bars!
4. Click OK to come out of the select data dialogue. Right Click on the data series and select Format Data Series, choose 'Secondary Axis' in the 'Plot Series On' box. Click Close.
5. Right Click on the data series and select 'Change Series Chart Type' and choose a suitable line type.

Hope this helps.
 

Forum statistics

Threads
1,136,353
Messages
5,675,288
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top