Free Microsoft Excel 2013 Quick Reference

Charts variable axis y Results

In Excel can I plot two lines with different Y-axis values?

Hello everyone,

Here is a problem that has been puzzling me. I am looking to create a column or bar chart that moves in two directions.

Essentially, I want to include 16 variables on my chart, 1 bar for each variable, and the four categories that make up each variable stack on each other. I am looking to have the variable name up the left side of the graph (ordinarily the Y axis), and the % axis on the bottom of the graph (x-axis.) For simplicity sake, lets call the categories of each variable C1-C4. Using 0% as the origin, I want the categories 1 and 2 to be to the left of the line (stacked on each other) and categories 3 and 4 to be to the right of the line, stacked on each other as well. The sum of all of the absolute %'s will equal 100 for the variable.

There are a few things I am hoping that you can help me with. First, I do not want to show the axis as ranging from -100% to +100%. Id prefer it to look like 100% counting down to 0% and then back up to 100%. Second, I want the label of each variable to be outside of the graph, or at least not on the "zero axis", rather I want to position each label so it can be clearly read.

Are there any special things I can do to trick excel into making the graph the way I want it to look?

I'm trying to plot a few series of data against depth. Hence it would look
much better to have depth on the y-axis and the variables on the x-axis.

However I still need different scales for the variables to make this really
work.

Help!

I'm looking to prodice a graph as above - basically a 3d column chart
with a variety of different values (dependent on x and y axis),
together with a plane / surface intersecting (some of) the columns. Is
basically an extension of the column - line chart, but with an extra
variable.

All the columns come from 0 to between 50 and 150 or so, but I'd like a
plane / surface intersecting the graph at 100 or so. I've tried
changing the vertical (z?) axis settings to move the intersection to
100 but then the columns come up and down from 100, instead of
remaining to come up from 0 which is what I want.

There doesn't seem to be a standard formatting function which can do
this however I know a bit about visual basic (not much!) so if anyone
can let me know some code it would be very much appreciated!

many thanks

Jonny

I need to produce a line chart with the lines running north to south rather
than east to west. Anyone done anything like this?

Hello!

I have about 20 different graphs with 4 different series each. Each one of these series have a unique data range (Letters etc "A", "B"). This gives me a total of 80 different letters. The X-axis are numbers (etc 1->700).

I have a loop that goes through all 20 graphs to change their length. Etc imagine that all graphs starts at 50 -> 100 and I want to change it to 75->150. How do I do this?

Inside the loop it looks something like this:
Loop~
next graph~

Startz and Endz reprsent the length (etc startz = 50 and endz = 100).
How can I use this script without having to create 80 different variables? Is there someway I can just leave the variable as it is and just change the length?

Please ask away if I made anything unclear and I appriciate any help!
File is to big to post and posting the entire code will just lead to more confussion as it does a lot more.

Thank you!

Hi,
Just started using VBA and I need a bit of advice how to create a specific type of chart.

I have two arrays 'doserate' and 'bendingcurrent' - the maximum number of elements in these arrrays is 41 and I have another variable 'pos' which indicates the last non zero element in both the arrays.
I want to plot a chart with the x axis showing the 'bendingcurrent' values between the values of bendingcurrent(1) to bendingcurren('pos') and y axis showing 'doserate' between the values of doserate(1) to doserate('pos').

I want an x-y scatter chart with smoothed lines and I would like the x-axis scaling only between the values of bendingcurrent(1) and bendingcurren('pos')

Help!

Hi everyone,
I've been working on a macro that manipulates some data, and have been having trouble with one part of it. Specifically, I have three columns of data - one with percentages, one with simple integers, and one with names. I want to write a macro that:

1. creates a new sheet (i think i can do this)
2. creates a new graph that contains 2 series. The data for these series comes from a different sheet (not the activesheet, since that will only contain the graph)A. A bar graph with the names (column D) on the x-axis and numbers (column C) on the y-axisB. A line graph with the same names (column D) on the x-axis, and the percentages (column A) on a secondary y-axis, with the range from 0 to 1. When I do this manually, the wizard tells me that the chart data range is
 I already have a variable that tracks the last row of data, so the "73" in the range above would be my lastrow
variable.

Can someone please help me with this? I've spent 4 or 5 hours trying to figure it out by Googling and trying to record the macro, with no success.

Hi.

I've got a set of data I am having difficulty charting, and if anyone could offer their expertise I'd be most appreciative!

Basically I have data from a set of monitors which are recording pressure in a hydraulic system.

I have an upper and lower limit of allowable pressures.

What I would like to do is plot a chart from the data (it has been attached to try and help illustrate the difficulty I am experiencing) which shows the pressures on the y-axis and the times each day (08:00 and 17:00 concurrently) along the x-axis (so the x-axis would read 08:00 Day 1, 17:00 Day 1, 08:00 Day 2, 17:00 Day 2 and so on).

A separate chart can be produced for each hydraulic jack.

I would also like, if possible, to display the upper and lower limits of allowable pressures as a line parallel to the x-axis.

I'm trying to create a line graph that uses a formula for the Y-axis, and where the X-axis is based on time. The variable for which I want the formula to graph along is time, a value that I enter

I can't figure out how to get the graph to show the formula as it changes over time.

For example, I have a single cell (A) that gives my output data. It changes depending on a signle input I give it from another cell (B). How do I get a graph to show what cell A outputs, as B changes?

Hi, I need help creating a specific line graph. What I'm trying to do is graph results from an experiment (measuring the volume vs mass of an object in different amounts). So how I'm trying to graph it is this: Put the volume on the y-axis and the mass on the x-axis, then put in the results accordingly. So for one point say the object had a mass of 20 and a volume of 40, it would be on 20 on the x-axis and 40 on the x-axis. The problem is, when I try to graph this I cannot change the x-axis to a dependent variable (it always comes up as values like 1, 2, 3, 4, 5). Can anyone help me?

Hi,

i found this forum searching for help with a task i got today in the morning. Maybe one of you can help me out...

I have to create a waterfall-chart with a variable column width.

Means: Thy Y-Axis represents total costs (high) for each categoy, the x-Axis represents the cost per order (width).

[name] [total cost = height(stacked as waterfall)] [cost per order = width]
A 35 2
B 20 5
C 15 3
D 10 5

Means for example B has the width 5 and the upper bound of 55 and begins at 35...

I found some tutorials for matrix-charts with variable column widht but none of them included waterfall function...

Can anybody help me?
Thank you very much.

Best greetings
Haubner

Hi, I have a situation where I have a spreadsheet with 2 columns, A and B.
Column A is the date and Column B is the # of bids we have submitted on that
day.

I create a graph based on those columns, that shows the date on the X-axis
and the # on the Y-axis.

But I would like the graph to be updated automatically everytime I add a new
row to the spreadsheet. So that tomorrow, when I add the row for May 10, the
chart will reflect May 10 without requiring me to recreate the chart.

Is this possible? I've tried everything I can think of and it hasn't worked.

Thanks!
Ben
merc1286@hotmail.com

Hi folks,
I'm baffled- I'm writing a macro to pull a data table into an excel sheet & create a bargraph of those results. The number of records in the data table are variable (say between 2 and 50).

I'm trying to define a dynamic bar graph using VBA. The X values are in col C and the text Y vlaues are in col A. (both start in row 3) The values in column B are necessary, but have nothing to do with the graph. I'm having trouble using variables to define the source data as two, non adjacent ranges.

I've had problems just including the 50 cells in the data table as it includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cell C3. It just numbers them on the Y axis, not by the corresponding text values....

Any ideas?
Thanks
pim

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A"))
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Graph.Chart.SetSourceData Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered
ActiveSheet.ChartObjects(1).Activate

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = True
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
.TickLabelPosition = xlHigh
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With

I am trying to plot three parameters on a single XY line chart. Is it
possible io have three different scales charted to cover all the three
parameters which are quite variable numerically? Using Excel 2000

Hi,
I've created a Scatter Chart in OWC11 in C# and the X axis must display
dates instead of numbers, I do it using the code below:

....
string x= "10/2/2004 12:00:00 AMt10/2/2004 12:00:00 AMt10/5/2004 12:00:00
AMt10/5/2004 12:00:00 AMt10/6/2004 12:00:00 AMt10/7/2004 12:00:00
AMt10/8/2004 12:00:00 AMt";
string y = 1t2t76t67t....";

objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, "Scatter Charts Series
1");
objChart.SeriesCollection[0].SetData
(OWC11.ChartDimensionsEnum.chDimXValues,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, x);
objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimYValues,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, y);
....

whe I do this, somehow the X axis doesn't display the current dates in the
variable x and so the points position is wrong... so what I did is to change
the dates in the x variable into numbers. That way did work out but it didn't
display the dates in the x axis... (Example below)

....
x= "5t7t78t23t23t85t....";
y = 1t2t76t67t....";

objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, "Scatter Charts Series
1");
objChart.SeriesCollection[0].SetData
(OWC11.ChartDimensionsEnum.chDimXValues,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, x);
objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimYValues,
(int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, y);
....

I have no clue about using a Scatter Chart with a date format axis... can
you please tell me any way to do that please?

pavr1

Hello:

Using an older copy of Excel,"Excel 97"

However, it's been working just fine, and pretty much does everything I
want.

A bit stumped on this, however:

I would like to generate a graph/chart that plots 2 variables along the
normal Y axis, but in addiition
I would like the right hand Y axis to be used for a third variable.

The Scatter or Line type(s) would be fine.

Can't quite come up with how to do it.

Is it possible ?

Thanks,
Bob

Hi there,

This is my first post on this forum, so bare with me.
I am trying to chart the performance of telesales personnel at my workplace.
I would like to use a line chart to 'chart' their performance over a period of time (so Y-axis = Date).

The Variables are:

Total Call Time
Total Talk Time

I would like the option to expand the chart as time progresses, so I can continue to update the chart with staffs performance, and get a clear picture of their avg. performance over time.

Please find attached the data sheet.

Any help on this would be greatly appreciated.

Many thanks
Alex

Hi, I am a novice at VBA and have a question about formatting marker colors in a chart based on the values in a column of my spreadsheet.

I have results of analysis of soil samples in a worksheet with 27 columns and 360 rows. The column labels are for year, plot, and then 25 soil analysis variables. Each row is for one sample from one year--there are 8 years of data. There are 45 samples for each year--one sample for each of 45 plots, labeled "1" through "45" (in column D). What I have done is to use VBA to produce a X-Y chart for each soil variable: "TC" for Total Carbon, for example. The x-axis is categorical: "1" to "45" for each plot. The y-axis is the level of carbon in the soil sample for that plot. The chart therefore shows stacked markers above each plot number; one marker for each of the 8 years. I have stacked 25 charts--one for each variable--using VBA, by making the code for one variable's chart and then just copying down the VBA window 24 times, changing the refernce columns and names for each chart, and the chart's location on the sheet.

This works fine, but what I'd like to do is to have different marker colors for different years. I am thinking that the .FormatConditions object is the way to go (I have Excel 2007), but I can't figure out how to use it. Can anyone help me out with this?

I have posted below my VBA script for one of the charts. It's probable that this script shows how little VBA I know--I used the Macro Recorder a bit and copied from forums a bit.

Thank you!

______________________
Dim ChtTC As ChartObject
Set ChtTC = ActiveSheet.ChartObjects.Add(5, 2970, 600, 150)
With ChtTC.Chart
.ChartType = xlXYScatter
.SetSourceData Source:=Sheets("data").Range("ac5:ac320,e5:e320")
.SeriesCollection(1).Values = "='data'!$ac$6:$ac$320"
.SeriesCollection(1).XValues = "='data'!$d$6:$d$320"
.SeriesCollection(1).Name = "TC"
.SeriesCollection(1).MarkerSize = 4

With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 45
.CrossesAt = 0
End With

With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 60
' .CrossesAt = 0
End With

.HasLegend = False
.HasTitle = True
.ChartTitle.Text = "TC"
.ChartTitle.Select
With Selection
.Font.Size = 8
.Top = 0
.Left = 0
End With
End With

Hi,

First time user,

I'm trying to graph changes in pH in a water column with depth. Obviously it makes more sense if the depth is on the y axis i.e up and down...

Excel automatically makes the x axis the independent variable, is there a way to change this? I can make the graph work my manually entering the series', but with the amount of graphs i need this would take about a lifetime.

Thanks in advance.


No luck finding an answer? You could always try Google.