# Excel 2010 :: Creating Charts With Average Line?

Apr 5, 2012How I could create a chart (standard column) with an average line in Excel 2010?

View 4 RepliesHow I could create a chart (standard column) with an average line in Excel 2010?

View 4 RepliesADVERTISEMENT

I'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:

Seperation First target Second target

0 59.00% 0.00%

2 64.28% 40.62%

4 62.68% 48.99%

6 60.81% 65.83%

8 61.82% 70.21%

I have two line charts in Excel 2007.

One chart has the values going down from upper left to lower right

The other chart has the values going up from lower left to upper right

Is there anyway to merge these charts to see if they intersect at all?

I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.

Sample Data:

Code:

State Characteristic Value07 Value08 Value09

Alaska A 11 23 45

Alaska B 13 22 98

Alaska C 99 91 21

Alabama A 23 14 11

Alabama B 44 62 76

Alabama C 75 47 21[code]....

This code doesn't run at all.

I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code

View 2 Replies View RelatedI have a simple problem. I have many worksheets which all contain data in the first four columns. I want to be able to create a chart (a scatter chart) in the first worksheet which plots the data on that worksheet and then format it how I want it to look. This I can do.

Next, I want to copy and paste that graph into all the other worksheets, but have the pasted graphs display the data in that worksheet.

As it stands, in Excel 2010, all the pasted graphs keep showing the data in the original worksheet irrespective of which worksheet I now have them in. This seems to be different to older versions of Excel and is very annoying and has to be changed by hand which is very time consuming.

How can I make charts that don't have the worksheet name in the cell reference, or how can I paste a chart so that is updates the references to the same row and column but in the new worksheet?

I know I could copy and paste the entire worksheet and then manually copy the new data into that copy of the worksheet - but that is not a real solution to this problem.

My chart is coming in blank when I set up a line chart. This is my data that I selected and then pressed chart. 1997

63.9%

1998

63.2%

[Code].....

I am looking for a way to manipulate xy data graphically by moving points in an excel scatter plot. This functionality was present in older versions of excel, but I can't seem to do it in the MS office 2010 version.

View 1 Replies View RelatedI'm working on a macro that does the following:

1. Pull data from the internet

2. Perform calculations on the data

3. Graph the calculated values

- As I run the macro, Excel dies in step 3, where I graph the calculated values.

- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts

- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area

- It works perfectly fine when I step through using F8

- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait

- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:

I'm running Windows 7, Office 2010

Code:

'Chart variables

Sheets("Summary").Select

Dim ChartRange As Range

[Code]....

I am using Excel 2010 to create a simple chart. When I enter the information, highlight it and select a chart, the chart area appears blank. It doesn't matter what kind of chart I choose- it still appears blank. I've tried going into the Advanced options and indicating for All objects to be shown and that didn't work.

View 1 Replies View RelatedI'm trying to dynamically populate ranges to facilitate dynamic charts being generated.

I use excel 2010 at work, and 2011 for mac at home.

Dynamic chart ranges populated from named ranges as selected in nested indirectly sourced validation lists

I want any selection made in a dependent validation list which contains a list of named ranges to trigger a worksheet_change event which copies the range the selection points to and pastes it into a dynamic range in another column, beginning as a specified cell.I've tried using this, put together from some code examples from similar, but different issues.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B2")) Is Nothing Then

Range(Range("B2").Value).Copy

Range("P2").Paste

End If

End Sub

Trouble is, I don't really understand this code. It doesn't appear to do anything when I make a worksheet change in "B2", but I don't know exactly what it is. I suspect that perhaps the fact that "B2" is validated from an indirect source might be difficult?

I am using Excel 2010

I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.

I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.

How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.

Here is a visual demonstration of how things currently are and how i'd like to get them to be:

As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.

Pic1

How this looks when i try to print, it's only on the left side. right is all blank

Pic2

How i want it to look like upon printing

Pic3

As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.

[URL]

I would like to plot the 1st dynamic line chart as shown in [URL] ..... but i never write any excel macro 2010 before, any sample of this?

View 4 Replies View Relatedi have 5000+ addresses with no punctuation in them, just seperated by line breaks. i need to be able to add a delimiter (semi-colon, colon, full stop, any will do) to the end of every line of the address, not the cell; in order to extract the info using 'Text To Columns' in Excel 2010

View 9 Replies View RelatedHow can I get this code to loop through every line on sheet?

Code:

Selection.Copy

Windows("SheetA.xlsm").Activate

Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas _

, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate

[Code] ..........

I want to a horizontal line in a chart. There are only two figures with me. One I want to display as a column chart. The other as a horizontal line chart. (I can use both excel 2003 or 2010, whichever is suitable)

View 2 Replies View RelatedI have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.

View 1 Replies View RelatedI would like to be able to add a line (a trend line) to a clustered column chart. The attached jpg (picture copied from Excel Charts - Easy Excel Tutorial) shows the general structure, with the red (poorly) hand drawn lines representing what I want to add to the chart.

If using Excel's Trend line functionality is not an option, I have already calculated the trend points adequately, so have the data to add as an additional series or combine with an existing series. (using Excel 2010)

I am using Excel 2010 and I am currently trying to find the Avg Min / Avg Max costs for 4 different groups.. Players, Accompanying, Coaches, Medical.

I need it to get the minimum values from 3 different sections ( I am using custom names for them such as PlayerReg for Player Registration costs ) once it gathers the minimum / maximum values from the 3 different sections it needs to add them together to display the Avg Minimum / Maximum for each group.

At the moment I have =MIN(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Minimum /w Frequent Flier although the minimum value it should detect is $948.75 it displays $1115.

For maximum I have

=MAX(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Maximum /w Frequent Flier although once again this value should be $1174.50

Although for some reason it displays $978.75 - which is the minimum cost. The Maximum figures for the other groups (Accompanying, Coaches and Medical) don't even display values Instead it just has #VALUE! in which I'm guessing I have something wrong with it.

I want to create a bell curve for my team's annual appraisal. There are 12 people in my team. The scale is 1-5, where normally rating range from 2.9 to 4.0.

View 1 Replies View RelatedIm trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.

My Data Looks like this:

List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%

c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%

d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%

Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.

What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.

So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.

The code I came up with so far looks like this:

Function AK_Overlap_Go(x, y)

Dim Temp(1 To 2, 2 To 1)

Dim x As Integer

[Code]...

One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.

I would like to create a sound alert if the answer to an "if" statement is false. I am using Excel 10. Can it be done, and if so, how?

View 5 Replies View RelatedI have been given a task to create a line graph who has 10 categories and each category has 5 different values. I need to plot a line graph, so that those 10 categories should appear on the Y-axis instead of X-axis.

I am using Excel 2010.

Basic Excel-2010 functions.

I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.

1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error

2) when I use =AVERAGEA(A2:A7), it yield ans=0

3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;

Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

2/9/2014

[code].....

I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week

I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns

I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34

=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,

X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)

I could use SUM, COUNT COUNTIF etc but its gonna get messy

Using EXCEL 2010

I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).

Sub average()

FinalRow = Cells(Rows.Count, 3).End(xlUp).Row

For I = 3 To FinalRow

If Cells(I,3).Value 0 Then

'I use the above to identify the first nonzero cell

ActiveCell.FormulaR1C1 = "average(........)

This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.

Warrantee.xlsx

I attached the sheet that I am trying to get up and running, there is two things I need to do, but cant get it working as I intend to.

Sheet 1 - When the invoice number is fulled in in cell F8 a message box must pop up requesting the hyperlink to the invoice saved on a share folder, but for now on the desktop, when the file is selected it (the hyperlink) must be returned to cell G8, then once the last cell (I8) has been completed, the line 8 must be locked so that it can't be editted again without a default password and a new line must be added below line 8 for processing, I tried recording the macro, but it only works on one line, and on the message box request, I truly am not up to speed yet, this I can't get up and running.

Excel 2010

I'm using Excel 2010

I'm working on a workbook for tracking debt. It has 15 sheets. Instructions, Charts (for showing debt change over time), "Invisible" (this is where my chart data is, I plan to hide it), and 12 month sheets. On the Invisible sheet, for example, I have my data set up. I'm pulling the data from other sheets. In the screenshot, I'm using =Month12!P48 as the formula to pull my data from the other sheets. But, the user hasn't entered data yet for month 12, so the line chart drops to zero there. I want it to just stop and do nothing, until there is data in that cell. (I would prefer to use a line chart vs a scatter chart for this, but I have the same problem in both)

data.jpg

chart.jpg

Windows 8, Excel 2010

I have XYZ Coordinates for a continuous 3-D line that has numerous segments. I want to input a distance along that line, and have it create the XYZ coordinates at that point. See Image for reference.

Row 2 is my start point - I input the initial coordinates here - this point is the origin of the 3-D line

Column B is where I want the calculated Y value to go for each point

Column C is where I want the calculated X value to go

Column D is where I want the calculated Z Value to go

Column E is the how far along the 3-D line that the (to be calculated) point should be at. (MD1)

Columns G, H, & I are given to me, and I use this data to generate the coordinates in Columns J, K, & L

Column O is the cumulative length of the line at that coordinate. (MD2)

Basically, I had planned on writing a formula to:

Find the coordinates of the point who's MD2 (column O) is before the desired point's MD1 (column E)Find the coordinates of the point who's MD2 (column O) is after the desired point's MD1 (column E)Subtract MD's (column O) to get the length of the segmentFind the distance along that segment that MD1 (column E) fallsUse that distance to traverse along that line to the desired point.

Copyrights 2005-15 www.BigResource.com, All rights reserved