I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.
For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel ......
I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?
I imported my data and I have a macro that updates the data. Nothing new until new items were added, creating new rows at the bottom and now the Table Format does not include these rows. There are so many formats available that I don't want to guess which one I used when I created it I just want to extend the data range.
I do not care if this is done through the macro or right on the sheet as this does not happen that often.
I'm having a problem with a dynamic range. I've re-checked my formula at least 10X and its syntax is correct. The problem is the dynamic range stops short 3 rows up from the last entry.
View 9 Replies View RelatedI have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
VB recorder in Excel. The macro is designed to process a spreadsheet that will have the same number of columns and same type of data in each column, process this data with a formula (hat tip: AlKey+XOR LX) and then clean up the columns ready for use in another program.
I have got it working but when I alter the row length the macro only works to the final row in the recording spreadsheet. I realize this is because when recorded the macro I told VB to set the range to a given row.
I remember there is a way of setting the Range function that tells VB to always go to the bottom of the rows filled with data but I can't find it for the life of me.
see the attached file the training spreadsheet.
[Code] ......
DdSample_training_data.xlsx
process.docx
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
I have created some charts for my work and one of them is a line chart that spreads across one year with values for each month. Well they want the graph to always show one month ahead of where we are out. So there is actuals in for Sep. but then the line graph drops all the way to 0 for Oct. I don't want to have the graph bottom out when there is a zero. Does anyone know how to fix this?
View 9 Replies View RelatedI was tracking down simple ways to generate ASCII character based bar charts and came across this stunningIncellBarChartExample where the author suggested this can be done using VBA code.
I am very new to Excel and EXCEL VBA. Can someone help me sketch how the coding for the above example (include mixed colors) might be done? Is there a special font that need to be used?
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
View 3 Replies View RelatedHow can I create a box chart showing graphically a median, 1st and 3rd quartile and a minimum and a maximum?
View 5 Replies View Relatedis it possible to rotate a graph. i need to turn my stacked cone graph upside-down so that the point is facing down and not up
View 2 Replies View RelatedIs it possible to create a chart using spherical coordinates (radius, theta, phi) in Excel? I want to create something similar to this. I would even be willing to write add-in code to create the graph from scratch, but I don't know if that's possible. Is it possible to create a new chart type without using the other chart types as a base?
View 5 Replies View RelatedI would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
View 5 Replies View RelatedI am trying to figure out how to activate a macro from each item in a drop down list. I also need the macro to recognize which item is chosen and to use that items data (say cells C3:C11) to create its graph. I know how to create an individual macro that will create a graph for the item but not a general macro that can be used to graph all items just discerning the difference in where the data is (which item to graph). Is this possible????if so please include code (amatuer programmer).
View 9 Replies View RelatedI'm trying to graph a bell curve chart using the following data
Hotels per state
Total
AK
4
NH
5
[Code] ......
As you can see I have states and the number of hotels per state what I need not for statistical purposes but more for "pretyness" is to sort this data in a way that what you get is a somewhat of a bell shape curve graph.
I've been working on this for a couple hours and I am close to getting it how I want. Basically I want to have a rolling 15 month graph that will change according to the month you choose in the drop down tab. Example: I choose july 2007, the graph will show data from july 2007 to September 2008. I have the graph functioning, however my formulas are off (collecting the wrong data). I've included a spread sheet
View 2 Replies View Relatedone of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
View 2 Replies View RelatedI 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................
I have a scatter graph that has over 100 curves that im need on one graph that at the moment i putting each x and y and name in by hand in series. I have been doing it for hours and have looked round the site to see if anything similar answer on ozgrid would help but im baffled
i was just wondering if there is an easier way of doing this
i havent really used macros but iv been reading up on it can i used it to do this?
I have added the table and graph and this is how i would like all the columns to look like
its the table called graph that makes the curves that i would like to be able to add in quickly
the rest im not too fussed about.
I need to plot a stacked chart. I have attached a sample of it. I need to plot, category As X and subcategory and and its percentage as Y (stacked chart). In the attached sample I plotted a chart. The problem is, the series name showing is not correct. It takes the series name from the top row.
View 5 Replies View Relatedcreating a graph / chart using Ms Excel? I've been trying to do it myself but to no luck as I'm just a novice Ms Excel user. I'm trying to create a bar graph and combine with line graph based on these data. kindly refers to my attachment image for an example of desire graph. For line graph, it should display individually based on the given data. These are my daily data and I want to have an analysis graph so that I could monitor my computer's usage and its efficiency.
Computers Data Usage
--------------------------
Computer01 10 50.00%
Computer02 691 68.89%
Computer03 107 81.03%
Computer04 190 81.25%
Computer05 2099 82.89%
Computer06 1247 50.88%
Computer07 2786 10.00%
Computer08 69 22.00%
Computer09 300 18.00%
Computer10 1829 87.07%
Computer11 746 27.43%
Computer12 437 91.33%
Computer13 301 62.50%
Computer14 24 40.00%
Computer15 93 83.33%
Computer16 192 92.45%
i have a set of data range from B1 to K10 (10 cells all together).
and i created a graph by selecting from B1 - K10.
what i want is, if i key in range from 1-10 in A1, the graph will change based on the number i keyed in (from 1-10).
if i keyed in 5 in A1, the graph will auto select B1 to F1 and show data from B1 toF1
then if i select 6 in A1, the graph will auto select B1 to G1 and show data from B1 to G1
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph.
How do I get the graph to display the new data I entered ?
I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
after spending hours searching the forum, I begin to seriously doubt my ability to even formulate a suitable search question. My problem SHOULD be a known one, but I am unable to find any clues. Here we go:
I have made a pretty stright-forward forecasting model. It is used to calculate economics of a coal fuelled power plant (even though that shouldn't matter). The outcome is, among other things, a single number for Profit / MWh in Euros. There are several input data, such as fuel price, price for CO2 emission rights, operational expenditure, capex, etc.
My idea, then, is to lock all in-data parameters except ONE, then let this variable vary incrementally and then produce a new small table with a) the variable data (example: price for CO2 rights ranging from 10 to 50 Euro in 2 euro steps), and b) the resulting net profit in each case. Ideally, I wish to be able to chose which indata to lock and which to vary, i.e. solution need to have certain flexibility.
NB: The calculations are not that simple, i.e. I cannot use a simple formula - I need to use the exising output cell(s) to feed data.
How do I do this?
The outcome should be basically two rows with a suitable number of columns (or the other way around, two columns in X number of rows). The table will then be used to produce illustrative graphs.
I guess I could hard code a table by simply enter data, but heck, that's not the way to use Excel!
I am making a spreadsheet that will be used to track employee time usage. I would like to be able to create a graph of the time throughout the day that shows only whether or not the employee has entered an activity during each particular time slot.
EX: The graph shows the time from 8-10am in 30 minute increments. The employee has entered activities for the time slots from 8-9 and 9:30-10. When the graph is created, I would like to see times 8-9 and 9:30-10 filled (or, charted, or, however I should phrase it) and time 9-9:30 left blank (to represent that no work was done).
I have a countif statement based on certain criteria in a table which is reading off of a drop down list on a separate sheet.
Apple (a1) =COUNTIF(Table2[Fruits],+'Drop Downs'!A1) ...and the results are 3
Pear (a2) = =COUNTIF(Table2[Fruits],+'Drop Downs'!A2) ..and the results 0
Banana (a3) = =COUNTIF(Table2[Fruits],+'Drop Downs'!A3) ...and the results are 1
I have also set up a bar graph (this may change to a pie chart at some point) which is feeding from this. My question is, if I do not want to any results with 0 value to show up on my chart (both on the X&Y axis), how do i amend the formula to do this? I read in a couple of places about the NA() function or about just hiding the rows, but if this chart is updated "live"on a weekly or monthly basis, I would like to chart to move with the data simultaneously...so the numbers will change, sometimes they will be 0, others they may not.
I track inventory/sales for a very small sole proprietorship. I'm looking to track the number of items that I sell based on the text that I enter in a certain column.
Specifically, I want to have a visual chart/graph to show the percentage of each size/color of an item that I sell, as I sell it. When I enter the words "3T pink" I want the chart/graph/whatever to show another quantity of this category. As I sell a new item, I want the chart to reflect it.
The problem I'm having, is that the graphs/charts require two data sets, but I want the graph to calculate it for me! All I want to do is enter the text for specific items, and when the text occurs subsequent times, I want this reflected in the graph/chart.
It seems so much more simple in my head....basically I want a dynamic chart, not based on a static data set.
I would like to chart (pie or graph) sales based on the hour they occured.
In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.
I am working on the evaluation of a survey. I had 212 respondents which could be divided into three distinct groups. I would like to present the results of a question in columns that reflect the total frequency count in percentages. At the same time I would like those columns to be stacked in order to illustrate the contribution each group has made to that particular answer. I have attached an xls file with the data and a demonstration of the outcome I am trying to achieve.
I haven't been able to create the graph so that the columns display the total count in relation to all respondents on the one hand and at the same time are broken up into the individual groups. Among others, I have read the thread: http://www.ozgrid.com/Excel/stacked-column.htm but that doesn't answer my question as the Total there is actually the cumulative total of the other values.