Making Chart/graph From Text By Occurrence
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.
View Complete Thread with Replies
Related Forum Messages:
Create Chart/Graph From Text Strings
Can I build/present a "Pie" chart DIRECTLY from a list of "ABC" letters WITHOUT counting the occurrence of each one of them and using the results [nor VBA / nor Pivot-Table] - which is the trivial way of building charts !?
An example of such a list will be:
Chart Making- Chart With 3 Data Series
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
Chart: Sorting Values In Accordance To Occurrence
I have a huge set of data values given per 0,5 hour. Now I want to make a chart which shows how many hours each value has taken place.
Here is what it looks like with the arranged data as y-values. Instead of the x-axis showing the number of values, I would like it to show the above mentioned.
Making A Simple Chart
Im trying to create a bar chart that contains required hours per week with a line of hours worked going across the bars, im struggling to make this work and have basically no experience of making any sort of charts or graphs.
I need to create this using vba but to begin with I cant even make the chart manually! Where its all going wrong is the column line for required hours is so thin it looks rubbish. Its a bar chart then I formatted the series of total hours to be a line chart.
Making A Special Chart
I want to make a chart in Excel similar to a Stacked Horizontal Bar Chart, except I want to be able to have spaces in between the bars of each row. I want to be able to have the separate bars represent spans of time, and I don't want them all to have to necessarily connect to one another. I tried the Floating Bar Chart under the Custom Types tab, but that doesn't let me have more than one bar per line (as far as I can tell).
Making A Chart From Data On A UserForm
I'm trying to make a chart (which will also be on the UserForm) from data that a user will enter onto a UserForm. The problem is that I am trying to select a data range as one of the charts 'series' but I can't quite find the right code. At the moment it looks like this:
ActiveChart.SeriesCollection(1).Values = Range(BMIM20Ttl, BMIM2025Ttl, BMIM2530Ttl, BMIM3040Ttl, BMIM40Ttl)
ActiveChart.SeriesCollection(1).Name = " Total"
ActiveChart.SeriesCollection(2).Values = Range(BMIM2075, BMIM202575, ...
The problem is with the Range function.
Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart
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.
How Do I Not Graph A 0 Value On A Line Chart
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?
Chart Or Graph Percentages
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....
Graph-Chart In Cell
I 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?
Making Chart Plot Data Cells & Not Null Values
I have a chart that shows up to a list of 28 people and the number of sales for that day. I'm try to make the chart only show the names and number of salesperson that are not = to null. This is what the chart has for values right now "=' Nest Average'!$C$6:$C$33". How can I make it so that it only shows those cells if not = to null.
Multiple Series Chart/Graph
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.
Automatically Update Graph/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:
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
Rolling X Month Graph Or Chart
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
Auto-Extending Graph/Chart Range
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 ......
Drop Down Chart To Run A Graph Macro
I 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).
Dynamic Chart/Graph For Changing Ranges
I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table.
I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook.
I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range.
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.
Chart A Graph Of Jobs Over A Period Of Time
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?
Create Stacked Bar Chart/Graph
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.
Determine Breaking-Point In Graph/Chart
finding a function/formula for determining the breaking point (Yvalue) in a graph, that doesn't necessarely equals the maximum value of that curve!
So the curve in the sample is a stress strain curve (tensile test) of a fibre and at the end it breaks and the Y value returns to 0. I would like to determine that Y value before it is 0, so the last Y value before it returns to zero.
I thought of a formula like:
display last y value before it drops with 90%...
but I don't have a clue how to write it in maths...
Plot On Chart/Graph As Zigzag Pattern
I have the following list of values which I want to plot as a zigzag. But when i create a line chart from them, the lines connect the zeros and the non-zero points - while I want the lines to connect only the non-zero points - so that there is a zigzag-like pattern shown.
Combined Bar And Line Graph/Chart
creating 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%
Prevent Graph/Chart From Updating/Refreshing
I am dealing with plotting large amounts of data (millions of cells) across multiple series. Everytime you click, add a new series or do anything it redraws the graph taking a while. This Is there are way to prevent Excel 2003 from plotting a graph until you are done setting it up?
Pie Chart To Graph Percentage Of Total Expenses
when you open up the file please ignore everything above the blue bar. That was the old data which was arranged wrong. I have actually figured a lot of this out but i am stuck. The graph is set up so when you click on the drop down menu (F26) it changes the graph to the relevant data. Now the bottom graph is currently graphing the Months Expense1 as a total of all Expense1. So January Expense1 was $100 so its 26% of all Expense1. and when you click on the drop down button you can changed the data to Expense2 and the graph changes. YAY ok thats cool. However, thats not what i want. I want the pie chart to be graphing the expenses as a percentage of total Expenses. So the drop down menu would be of the months instead of the expenses.
I have changed that with relative ease, but I cant get the data to graph how i want it to.
I have been using OFFSET() formula and the define name manager to set up the previous graph. You can easily look at the formulas i have used instead of me trying to explain everything. Please help. This is the test bed for a budgeting spread sheet I am working on.
if you can help me out that would be great.. Please try not to use Macros because i dont understand them all that well and I need to take what is done in this spreadsheet and learn from it and change it so it works when are thre 10 expenses.
Graph & Chart: Data Source Changes Monthly
I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"
I am getting Runtime error 91 - object variable or with block variable not set.
Source Location/Address Of Series In Graph/Chart
Looking for code that returns the cell address of a series in a pre-existing graph. I have a graph that already has Source Data manually assigned to it. Is there a way to find just the cell values or location of a single Series in a SeriesCollection object? I know that in order to assign a series of data to a Series object, you do something like this:
ActiveChart.SeriesCollection(1).Values = Worksheets("My Worksheet").Range("A1:F1")
The issue I am tackling with is a graph that already has preexisting data in the Series object, and I just need a quick and easy way to find the address of this data set.
Macro: Plot Graph/Chart By Series Of Rows
I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.
Basically the name of series is down column A
but the range is from E1 to Y1
and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably
Sub adddatatopsd(Name As Range, XData As Range, YData As Range)
.ChartType = xlColumnClustered
.XValues = XData
.Values = YData
.Name = Name
.ChartType = xlXYScatterSmooth
Test Selection Values & Graph/Chart
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23
21-40 = 1
C4 = 30
21-40 = 2
D4 = 45
41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
Chart/Graph For Individual & Group Totals
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.
Interval Distribution Plot Chart/Graph From Selected Row
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly.
Finding The Maximum Occurrence Of Text Over Non-consecutive Cells
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Set Chart/Graph Minimum/Maximum Scale To Nth Smallest & Largest Value
I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:
.MinimumScale = Range("Min").Value
.MaximumScale = Range("Max").Value
Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area. Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis. I cannot add a constant in the code given above since I that does not suite my requirements.
Controlling Word And Making Text Bold
I'm using excel to open a new word document, stick some text on different lines and then save and close. The trouble is, I want to bold the titles but when I do I either bold or unbold the entire document. The codes looks like this:
.Bold = True
.Content.InsertAfter [Word_WordCount].Offset(0, 1).Value
Making Text To Columns Permanent In A File
I have some text that I am having to analyze which comes out of our computer looking like this:
I use the text to columns function and headers above it to translate what each field is.
As I'm doing my testing if I do this once, I can keep the file open and any time I paste a similar entry in it takes care of the formatting.
But, is there way to make the file do that permanently? I want to let the other testers use it without making them have to go through the steps of clicking on the delimiter, etc.
Making A List Of Rows Into One Line Of Text
I have one column with a list of 2000 rows like this
I want to do is put all those lines of text / rows into one cell with the word "or" between each phrase. In other words in one cell all the above should be made into "apple or banana or carrot or dog or elephant or fish or goat or hat" . There should be no breaks.
Make A Graph That Will Graph Against Time
How can i make a graph that will graph against time? lets say i have a bank balance like this:
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
Passing Cell References From A Cell's Text To LINEST Function And A Graph
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
Line Graph: (line Graph To Display An Amount Over Time)
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.
A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):
Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
Show Text Box On Chart
I am trying to add the date my charts were created. I have the date the charts were created in a cell then I added a text box to each chart referencing the cell with the date. On some of the charts the date is showing. But on other charts on same sheet, the date only shows when selecting the chart area of the chart but disappears when selecting other charts or cells on the same sheet.