I have a large column of data that’s about 500 rows deep. I enter a few points of data a day. I want a simple bar graph to chart that data in order. Simple enough.
I set up the chart and everything. But after 2 days the chart is all scrunched up on the left side. And the remainder of the chart is considered 0. I want the chart to grow as I enter data without changing the data range every day
I got a set of data which is only 2 colums collecting data problems. The output i get is a time stamp and a fault. Im looking to set a chart up with the time been on the X - axis and number of problems per hour on the Y - axis.
View 7 Replies View RelatedI am keepting track of all the calls i get so the range changes daily. In A
is the date and column J is who took the call.
Is there any way to take this formula and have it refer to one place for the
range end as my table grows?
=SUMPRODUCT((MONTH(Data!$A$5:$A$670)=MONTH($A25))*(YEAR(Data!$A$5:$A$670)
=YEAR($A25))*(Data!$J$5:$J$670=C$18))
I've been making a spreadsheet with lots of optionbuttons, and I'm slowly starting to understand how to link them to values, execute VBA script when their value changes, etc.
There is a problem that completely puzzles me. When I click the button (not in Design Mode), or any of the other buttons in the same group, they keep on growing! Please see the attached picture for an idea of what it looks like after a few clicks. I want the text and button size to be stable. I haven't changed the zoom, column sizes, etc. so I can't be that. They grow by simply clicking buttons in the same group.
The spreadsheet calculates the Up and Down Capture ratio with input for the fund and benchmark's monthly return, and results are displayed in cells F96 & I96. Because I don't know how to write a macro in VB, How to turn this into macro so that I can have the formula, =UpCapture(B:B, C:C)
because the input monthly return will change in length with different funds. the definition and formula to calculate up capture and down capture ratio is as Follows: (also the method I used in the spreadsheet)
http://support.pertrac2000.com/statbody.asp#upcap
I'm getting adding values from a worksheet using sumproduct. The formula is as follows:
=SUMAPRODUCTO((Datos!$F$2:F459)*(Datos!$A$2:A459=Tendencias!E3)*(Datos!$C$2:C459=Tendencias!$A$5))
But when i replace the range from row 459 to row 55000 i get the NA.... i need the range to be as big as possible as i keep adding data on a daily basis.
what do i need to add to the formula?
I have a workbook that grows in size on every use. I import a text file, manipulate the data, manually export it and delete the data from the workbook.
So why does it continue to grow in size? By manipulating I mean, add rows and text to the new rows. Attached is a copy of the workbook in native form (have not ran the macro) and a sample file to import, the excel file is 59kb in size and the test file is 9kb in size. I have seen my original file grow to over 500kb from repeating the same macro on the same file many times or running many different files for testing.
There is a CommandButton on Sheet1, it will open a userform. Click in the largest textbox and it will open a file dialog box in "C:". Place the supplied text file in "C:" or some other folder of your choice as you can navigate to any folder or drive from the dialog box. The macros that add lines and or text are "JumpToLabelName" (this runs first) and "InsertRows" which runs second. These two macros do all the work.
Take note:
I store information in the Registry, "GeoMeasure" under "VB and VBA Program Settings" (Normal windows key entry). You can search for "GeoMeasure" and delete it if you decide to test run the file.
I have a workbook that I am working on that seems to be having file size growth issues.
All it is is 2 worksheets.
Worksheet 1- Columns A-S & 63 rows. That worksheet alone is 7mb.
no macros..No VBA..No formulas. Just straight text data with a handfull of auto-filters. I took the auto filters off and saved to see if that was the issue and it did not make a difference.
Worksheet 2- Columns A-S & 35 rows. This worksheet is almost 3mb.
Now I can make a change (when my computer catches up) to 2 cells and sometimes it will jump in size by 1mb or more.
I have been reading up about shared workbooks within here which quite frankly is annoying me! I have rebuilt a spreadsheet from yesterday and shared it with:
Dont Keep change history
Update changes when file is saved
The changes being saved win
At start of day the file is 660kb, now by lunchtime I have it as 15mb. Does anyone know why this occurs? Becuase the file is getting bigger this is affecting performance
find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.
Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.
Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.
I need to use excel on a daily basis at my new job and want to become more efficient.
7-Juntf
7-Junnon
8-Juntf
8-Junnon
8-Juntf
9-Junnon
9-Junnon
9-Junnon
9-Juntf
10-Junnon
11-Juntf
12-Junnon
This is a very simplified version of what I need to graph but has the variables I need. The spreadsheet is set up just like this, just extrapolated over the last year. There are over 500 entries in the spreadsheet. What I need to do is to be able to calculate and chart the number of occurrences of TF and Non over time, by day, month, and year. If the data is ordered by date, how can I get excel to calculate text occurrences and graph them over the dates?
Currently I am trying to do a gantt like chart time summation. I'll try to explain this as clear as possible... Right now I have a program that finds the name of a part in a giant list's column and then copies a few of the other columns (priority and hours to complete) from the part names row and pastes them into a new sheet. I then sort all the found data by their priority number. The job that is gets sorted to the top is the next job to be worked on for the machine. So here is what I need to be able to do...
Based on the following example I need to be able to sum for the object bolt on Machine #1 the tenative date that the "bolt" will be completed by. It will take a minimum of 35 hours for the bolt to get off Machine #1 based on its current priority. However, the part: "bolt" needs to go to Machine #2 before being completed. The part will have to wait a hour for "hammer" before being started on machine 2. (Its data for the time it will take complete "bolt" on machine 2 is also stored in the spreadsheet.) Since the bolts priority is higher than the "nail"... the "bolt" will be the next job to be completed. My question is this: Is there a way to tenatively sum the future hours that it will take to complete the bolt and allow it to be open to changes, in case a higher priority part comes into the system?
Machine #1
PRIORITY HOURSNAME123nut212bolt334washer422kazoo
MACHINE #2
PRIORITY
HOURSNAME122screw22hammer31nail
Let's suppose that I have a serie of activities. All activities are done in an interval of time and there is not overlapping.
View 14 Replies View RelatedHi guys, I would like to be able to automatically gantt chart the following data table, my problem is that i am unaware of how to create the gantt chart when there are several entries for the one location - the table is a booking sheet and the gantt chart is just a graphical display of the activity occuring in each bay - if any could help me achieve the following gantt chart from the the following table it would be much appreciated: ...
View 9 Replies View RelatedI am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together?
The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
I have an Access database, with a table of jobs processed.
One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated.
I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?
I have over 5000 cells of data. I want to take 4 data points at a time, average them then plot them on a dynamic chart. I know how to do the dynamic chart but how can I take the average of the the first 4 numbers store it in a different cell, then the next 4 and so on...
View 4 Replies View RelatedI am trying to create a chart which updates every time a new date (and its data) is added to my list below:
Date
Clicks
Impressions
Click Through Rate
01/06/2012
3
93
3.23
02/06/2012
4
100
4
03/06/2012
2
102
1.96
The chart plots the number of impressions and the Click through rate (on a secondary axis) against the date.
I think I need to use the offset property but am not totally sure how to do this
I am trying to chart data from a single cell that is changing over time. The cell is linked to another software program that provides its value. I would like to be able to chart the changes in its value either every time it changes or on some time interval (i.e. every 3 minutes).
View 9 Replies View RelatedI have a function that exports charts as PNG images, that is suddenly broken. The function is very basic: ActiveChart.Export fileName:=chartFileName, FilterName:="PNG"
Now I can guarantee that chartFileName contains a complete path, with filename and extension, so this is not the problem. I am getting the following error: Run-time error '1004': Method 'Export' of object '_Chart' failed. The only thing I can think of is that I had Office 2007 Beta installed alongside Office 2003, as mentioned by Andy in the following thread: Exporting Charts as GIF - Error. I have uninstalled Office 2007 Beta, and run the repair function of the Pffice 2003 install, but still I have this problem.
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
View 2 Replies View RelatedI'm having trouble figuring out how to put a particular type of constraint on a column. The data entered in each successive cell in the column (going downwards) must be superior to (or superior or equal to) the previous cell, i.e., the one immediately above it.
I've searched on the Net and don't even know which key words to include. Can I use the solver to do this? Must I write a short script (I've never programmed anything in an Excel document). How do you make sure that this constraint is always applied even in the 1,000,000th line of data? A basic version assuming no blank lines would be great. Another version where blank lines are permitted would be cool too.
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
How do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies View RelatedI have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View RelatedI need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I know this has been discussed a number of times, but here is my problem
I have three charts in my workbook. I want to attach a macro so that when the chart is clicked it returns to Sheet - Home. I have using the following:
worksheets("Home").activate. But after I protect each chart and the workbook, and save and exit. When I reload the Workbook it has forgotten the assigned macros and nothing happens.
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
I 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.
I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies View Related