The trend Equation on a chart is of the form y= a+x.(b+c.x) I want to use that equation in a spreadsheet to calculate value of y for the value of x. I can do it manually by inserting the a+x.(b+c.x) in a cell but would like it to be automatic as the a, b, c in the Equation changes regularly as more data is acquired.
View 2 RepliesIs there a way to get an equation for a trend line and have excel automatically export the equation to a cell where it can be used. The only way i currently know how to do this is to manually type the equation.
View 8 Replies View RelatedOn top of these graphs, i draw a line (insert a line shape) corresponding to a certain gradient in the data presented. I do not want this line to be exact (i.e. not point to point) as it is best to be drawn by eye so that the general 'gist' of the data can be found. What i would like, using any method available, is to be able to find the gradient of this line i have just drawn which obviosuly must be done taking into account the scale of the graph. The scales of the graph will not be consistent across all applications.
View 4 Replies View RelatedI've created a graph from data and need to add an average/net impact/trend line to the graph. From what I've read this needs to be an XY scatter, however, several efforts later and I can't seem to get it to work for me.
0514_Costs_DRAFT_ExcelF.xlsx
I want to show a stock chart in Open-High-Low-CLose format, then I hope that a trend line can be displayed at the same chart, what can I do? It seems like that stock chart can't be merged with another chart
View 7 Replies View Relatedi have a workbook that contains a series of worksheets. the workbook is a master document list. the first worksheet contains no data. the remaining worksheets are arranged so that A1 has the document number, A2 contains the document title, and A3 contains the review date. Id like to create a search macro that can search for the string entered into textbox1. if this is a number, the macro should search A1:A50 of all but the first worksheets in the workbook, and if its text, the macro should search B1:B50 of all but the first worksheets.
the macro should then select the cell containing what was searched. then id like the macro to prompt me and ask if id like to modify the reviewed date to todays date. d like to be prompted to continue searching for other records if they exist, and after that loop has finished to set the focus back to the search textbox.
I'm in the middle of designing a sheet that (amongst many other things) takes the equation of a graph trendline, pastes it, and calculates the various values along it. I can do it by hand, but when I try to record a macro, it doesn't register that I've highlighted the equation and hit copy. Here's the code I'm working with:
Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Selection.Copy
ActiveWindow.Visible = False
Windows("Copy of HRA Design calculator1.xls").Activate
Sheets("Binder @ max. stability").Select
Range("A1").Select
ActiveSheet.paste
The problem is line 4 - it can't copy because the text isn't selected.
Is it possible to put in Trend Lines when creating a stacked column chart?
I've tried in Excel 2003 and 2010 but it doesn't appear that it is possible.
I am trying to use VBA to find a value on a trendline. I have been unable to call the TREND function from Excel, and I can't see what I'm doing wrong. I'm using the following line of
point25 = WorksheetFunction.TREND( Range("d2:d25"), Range("c2:c25"), 25)
I keep getting "Runtime error 1004 - Unable to get the Trend property of the WorksheetFunction class".
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View RelatedI'm currently writing an automatic report to display information about a store and how it compares to its peers. My company works on a National>Region>Area>Store basis so my report lets a user select a store and the excel file brings back all the data for the relevant national, region and area data for comparison. That works fine.
I now want to develop a set of pages which display data for that store compared to other stores in the same area. For example: [URL]
My report brings back all the stores, ranks them by their performance against target and then puts them in a sorted table using vlookups. I then want to put them into the bar chart shown above, with the store selected highlighted with a different colour. In the example above, the Hunslet store is selected so its bar is highlighted.
Is there any way to make the store highlight automatic? I manually set the colours in the example but I don't want the user to have to do that each time. It would be possible to do using a macro, but I try and avoid them where I can since VBA knowledge is extremely limited in my office.
I have named ranges containing the names and lookup codes for the selected store, area and region .
I can automate chart creation in VBA, but is there any way to also have it such that the x-axis of the chart begins with the minimum x-value and ends with the maximum x-value in the data set?
View 2 Replies View RelatedI am starting within a workbook with a standard invoice/form. During the course of a month this invoice will be copied 20 to 30 times within the workbook and the amounts changed each time. Is there a way that I can:
a) Have amounts from 2 of the cells (say E30 and E31) within each new invoice worksheet automatically included on a summary page? These cells are calculated by formula.
b) (This one would be nice but is not essential) Have each new invoice worksheet that is added (by copying) automatically numbered sequentially in a cell (say A2) and also re-named with this number. The first existing invoice would be manually numbered and each additional invoice would follow from there.
I have a workbook, with 2 sheets. first page has graphs, second has data. in my charts I refer to a named range on the second one as follows: Data!Named_Range, where "Data" is the name of the second sheet. However when i save this, it changes the range of the chart to: '101044.xlsm'!Named_Range, where 101044 is the name of the workbook. This is a problem for me as this workbook will often be loaded into a document handling system where the file will change name, throwing out all sorts of errors because the chart path is invalid, and i have to change the range of all the charts manually. why does it have to use the file name when reffering inside the same workbook? and how do i force this to not change?
View 5 Replies View RelatedI currently need to create around 1200 chart images using excel for a report series I am performing for a client. I have been trying all kinds of different things, all failures, to automate this process. My current method is to filter a pivot table on the building I am analyzing, copying the chart, pasting it as a picture in Ms Word, then saving that image as a Picture. Obviously, this process is quite lengthy, especially if I multiply that over the course of all 1200 images.
I am looking for a solution using a macro, vba, or a combination of things to automate this process and maybe even create the jpgs automatically?
I'm currently building a excel workbook with charts that are feeding from Countif formulas, from a incident reporting sheet, now some of these items have "0" values and as a result are piling up making the charts look unsightly.
I know if I hide the rows with that data in them, it won't show up on the chart, the only problem is that this workbook will be going to about 8 countries, so I want this to be automated, is there any way for those 0 values to be excluded.
I have no experience with VBA, and only a little with macros
to write a code that would create a chart automatically in another worksheet when a button is clicked.
I have attached the sample data that I am working on.
Each row in the sheet represent 3 coordinates, which are:
coordinate # 1 = start, 0
coordinate # 2 = centre, level
coordinate # 3 = end, 0
I have 3 charts on 3 seperate worksheets. The data for each is sourced from a list of up to 100 but only the top 20, or all if less than 20 in full list, are displayed on the chart. A seperate worksheet ("Calcs") is used to sort all the data so that just the top 20 are sourced for the charts and they are displayed in descending order from max to min and alphabetically in the case of 2 or more names having the same value.
I have attached one ot the charts.
I need the y axis scale to auto-adjust to suit the max and min series 1 values. I need the y axis maximum to be 5 more than the max series 1 value (unless the value is 5 or less when axis should be 10) and the min y axis to be 3 less than the min series 1 value (unless the value is 3 or less when the axis should be 0)
I have done the formulae to sort out these conditions and put the required y axis values in a cell which the copies across to the chart sheet with the required max in cell M1 and the min in cell L1. These values will change when another macro runs.
I searched this site for the code to auto-adjust the scales and found one which I have tried to edit to suit my needs. This is the code I have ended up with, placed within the worksheet containing the chart....
I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:
Sub Extract()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If................
See attached file. I have managed to work out how to update a chart automatically when a new row of data is added. However is it possible to tweak the formulas I am currently using so that it will only include 12 months previous.
For example if you add Jan in to row 26 the chart will show Feb-Jan data (Row 15-26).
Book1.xls
I have been trying to think of a way to get a hierarchy chart automatically with given data through macros
In the attached excel sheet
Org. Charts (hierarchy) question.xlsm
Would it be possible to get the chart to do by itself if say I were to add items or remove items? Is there any way to do it for data automatically?
I extract the data from primavera as attached and I want to make the chart in excel automatically.
VBA code that would update a chart automatically as you change the inputs which are located in cells in excel. The inputs are located in cells B5:B9 and i want a code so that as you change these numbers the chart would automatically update.
View 1 Replies View RelatedI have created a pseudo Gantt chart in Excel (a horizontal stacked bar chart). The chart is driven off data I have in a Power Pivot model (Auto Refreshed) and the Gantt chart displays data which spans a fixed 12 hour time frame.
The problem I am having is that in order for me to get the chart to display correctly I must manually (daily) get the excel decimal values for my Minimum DateTime and Maximum DateTime and then enter those numbers into the "AXIS Options" Bounds Minimum and Maximum value fields. I would like to automate that process so that the Axis Bounds are either driven from the data cells OR are always set to a specific 12 hour window 6pm->6am daily.
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies View RelatedI am building this waterfall chart. I'd like to put conditional color formatting to change the vertical bar automatically e.g. if it is positive, the bar color is green and if it is negative the bar color is red.
View 3 Replies View RelatedI have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........
I have a chart with a title of "Store 123 Vs Store 789 Comparision". I would like to rename the worksheet name to "Store 123 Vs Store 789"
View 9 Replies View RelatedI downloaded an Excel template from Microsoft and I noticed that it had a dedicated worksheet with a chart in the middle. The area around the chart was gray and didn't contain cells.
I've attached the template to this post.
How can I add this type of dedicated chart worksheet to one of my workbooks/