Please see the attached spreadsheet, I would like to create a dynamic pie chart shows the monthly strategy breakdown (one month at a time), but not showing the zero value and also a drop down box to be able to choose which month's strategy breakdown to display on the chart.
View 9 RepliesI use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.
Sub SumBlock()
Dim First_Row As Long
Dim Last_Row As Long
Dim iTotalRows As Long
Dim iCount As Long
iTotalRows = Range("A65536").End(xlUp).Row
First_Row = 2
Do While Last_Row < iTotalRows - 1
Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row
iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")
ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"
First_Row = Last_Row + 2
Loop
End Sub
I'm working on creating a pie chart that draws data from a fair number of source cells. However, a good number of these cells = 0%. How can adjust the chart data range to ignore zeros.
Here is my chart data range:
='Jan-10'!$G$3:$G$18,'Jan-10'!$K$3:$K$18
I'm working on a report for work in which I need to produce sevearl charts. For each of these charts I need to omit any values that are 0 or null. Since this report is going to be run several times a month and with different values it would be pointless for me to do it by hand.
View 9 Replies View RelatedI have a PivotTable linked to a Bar Chart. (see attached JPG for example)
I do not want the 0% values to show in the chart at all but I do want to see all of the other percentages. I have been able to hide these values in the PivotTable itself by Conditional Formatting or custom number formats but they still show up in the chart!? how I can get the 0% values to be hidden or not show on the charts at all? I don't want to have to do this manually for every 0% that shows because the data changes daily dynamically. A VBA or Macro solution would be preferred.
Instead of treating cells with a blank or a text value as zero in a line graph, how can I create a gap in the line?
View 6 Replies View RelatedHere is a question about the chart in excel. I have a sort of data where only part of the data is in numbers, and when drawing the chart, the whole range of data were selected. Then on the chart, there is a line drop to the x- array, anyone knows how to get rid of it? It is a line chart. Here's an example if i did not clearly describe the problem
Here's data
X Y
4354.28
5354.75
6352
7343.82
8aa
9aa
10aa
11aa
12aa
and between 7 and 8 on x, the line will drop from 343.82 to 0, and i don't want this part, i want the chart to show to 7 and leave the 8 to 12 blank..
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.
So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.
This solution also worked perfectly, but now I've been told to create the graph without macros.
Does anyone have any suggestions? The requirements/details are as follows:
1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels
Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
I'd like a bar chart that will only display bars for positive values. So, if a cell has 0, it won't display anything. I.E. - if i have 10 fields and only 5 of them have values greater than 0, my chart will only display 5 bars instead of 5 bars and 5 empty fileds. kind of like piezeros, but for a bar chart.
View 9 Replies View RelatedI am trying to create a bar graph from a table which is pasted below. Two important things here are :
1. The range of the data for the graph are not adjacent. For the chart, the range will be columns D, F,H and J.
2. The range of the chart will not be static. As per the table below, it is uptill August but next month another row will be inserted and the range will increase by 1 row.
I am creating a chart where the number of data series used on the chart will change every time the code runs depending on what information people are looking for. That part works fine. I need a legend that shows which data series were added to the chart. Is there a way to get a dynamic legend on the chart that matches the data series, without manually adding it?
View 6 Replies View Relatedmake it working, tried from online thread but unable to get it done.Dynamic.xlsx
View 5 Replies View Related[URL]
Alas I need to put more dynamics and fill this data set from other sheets, so I'm putting formula into column A, and this force chart react and insert another category.
In other words I want to have this fill only if data present on different tab(worksheet).
That what I have now:
[Code] ........
COUNTA
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
(I am using different data IRL which is company sensitive so I have designed a simple example spreadsheet.)
Ok, So I have a list of words A2:A4 and I have created a Control Box which allows me to select which word I want from the list.
On a separate tab I have lists of data which relates to individual words from the A2:A4 list.
What I what to achieve is when the word is selected from the drop down menu it will create a Pie chart showing me the break down of percentages and attributing 'companies'.
1. I'm using Excel 2007
2. I'm trying to create a dynamic chart - following instructions from the numerous other threads on the topic
3. When I try to use a dynamic named range in the "Select Data Source" box, it creates a fatal error and the program shuts down.
I have a stock chart that I want to update dynamically if the data record length changes. From the source data menu, I tried to use the following data ranges:
=OFFSET(Candles!$AB$8,1,0,COUNTA($AB:$AB)) ---> Date, x values
=OFFSET(Candles!$AD$8,1,0,COUNTA($AD:$AD)) ---> High
=OFFSET(Candles!$AE$8,1,0,COUNTA($AE:$AE)) ---> Low
=OFFSET(Candles!$AF$8,1,0,COUNTA($AF:$AF)) ---> Close
When I place any of these in the data range box under 'x axis labels or values', I receive the message "This function is not valid".
The MS webpage at http://office.microsoft.com/en-us/ex...098011033.aspx describes using these formulas but they don't appear to work.
I am trying to develop a dynamic stacked bar chart, I have a consistent number of columns defining the x axis and a variable number of rows defining the y axis (the stacked data). Is it possible to generate a dynamic stacked chart. I have tried using dynamic named ranges but I can't get it to work for a stacked chart.
View 7 Replies View RelatedI have a template I used where, over the course of a month, I add an additional row of information (the 31 rows are there, just the information is inserted with macros every day). The charts, though, are daily, meaning that every day I need the charts to show information from the last row entered. How do I instruct a chart to take the last row of information and take the data from there (and automatically create the chart) instead of having to do it manually?
View 2 Replies View RelatedI have data in a matrix captured for the different machines we have. I need to make a pareto chart for the PPM's in each machine. This is updated everyday so i recorded a very primitive macro just to avoid making the 15 graphs i need as i have to filter the zero cells, then sort them descending and then making the graph....
The only way of making this in a more lean way is using a vba code?? do you know an already made code i can use for it?? i've searched the site with no luck. Here's a brief example of what i have:
Machine 1 Machine 2 Machine 3
Total PPM Total PPM Total PPM
Defect 1 30 4.5 0 0 0 0
Defect 2 0 0 0 0 0 0
Defect 3 0 0 45 20.4 98 298
Defect 4 22 12 0 0 36 123
I need the pareto chart for every machine's PPMs, sorted descending and as u can see some machines share defects.....
I have graphed a range of data that was generated using VB, but the data is a series of 6 models plotted against each other over a period of 6 years and the data is daily and as such would be useful to reduce the range to look at certain periods , is it possible to do this using the mouse to select a region or would something like embeded text boxes for the ranges of dates the user could enter be better, or would it be better to move away from excel for this part of it be better. Also is it possible to plot say range A1:A5 and A20:A25 against the the relevent dates to examine two separate periods side on side.
View 2 Replies View RelatedI have a chart that display data from a dynamic named range. Problem is the chart only displays the correct data when I first input the named range into the data source on the first attempt. When the dynamics of the named range change, the range the chart displays remains the same. I have read another post on here where someone had the solution, but I couldnt get it to work for me.
I am inputting the named range into Data Source-> Data Range. The other thread said to put the named range into Data Source-> Series. I am unable to enter my named range into Series. I have also tried putting the .xls filename followed by !(named range) into the series boxes but excel will not allow me to do this either.
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
i have a dynamic pivot chart. It works well, except the chart area size, it remains the same. Is there any way how the chart area can be automatically adjusted/resized (see the example attached)? Maybe with VBA?
View 8 Replies View RelatedI'm working with a pie chart that has a dynamic range as its data source and a dynamic range as its legend. The chart shows ratios of bought items depending on customer type. So, when you switch from a "Hair" customer to a "Bath & Body" customer, the ratios shift and the legend changes as well (i.e. "Hair - 25%" to "Hair - 10%").
Is there a way to fix the colors permanently so that as the ranges change, the colors remains the same for each category?
I've seen some VBA for it, but it was for data in long columns, whereas my data is in one range in the middle of the worksheet.
I can attach the file if it makes things easier.
Trying to create a dynamic chart herewith control in combo box.
Chart 2.xlsx
[URL]...... I need to put more dynamics and fill this data set from other sheets, so I'm putting formula into column A, and this force chart react and insert another category. In other words I want to have this fill only if data present on different tab(worksheet).
That what I have now:
[Code] ........
is there any other COUNTB,C,D???
COUNTA
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?
View 7 Replies View Relatedc5 shows today() and I'd like c5.value to be the headline of a chart. Hence, in sample file the headline be changed to 05/03/2014 (and tomorrow to 06/03/2014..).
View 2 Replies View RelatedI'm having trouble drawing an area chart that ends sharply at a specifided value rather than drawing a diagonal line down to zero. Let me explain
I watched an Excellsfun youtube tutorial that used the IF statement to chart a subset of normal curve data as the second series in an area chart.
It looks like an easy way to add some interactivity. I enter a single value, and the IF statement checks to see if the data in the first sereis is less than or equal o that number. Once the first series data exceeds the number I entered, the IF statement populates the second series with "", which is to say a blank entery.
Here's my spreadsheet data and the chart it produces:
Normal
ABCDEFGHIJ1Series AHeight AHeight B:5.5210.04070850.0407085d2.87228320.06610770.0661077x5430.09509940.0950994540.12118840.1211884Height A: =NORM.DIST(A2:A11,$F$1,$F$2,FALSE)650.13680530.1368053Height B:=IF(A2
I am looking for a quick way to create a number charts using data from multiple sheets.
I have 12 sheets Apr - Mar that contain data in identical format. I also have sheets 12 sheets Apr Charts - Mar Charts, I need to create a number of charts on each sheet for the relevant month. To avoid having to create a ridiculous number of charts, I thought I would try to dynamically change the chart data series based on a value in say cell A1.
For example if cell A1 of the Apr Charts sheet = Apr, the chart will show the data for Apr.
The idea is that I can copy the Apr Charts sheet the required number of times and replace the value in A1, and my charts will update dynamically. I need to have seperate sheets for each of the monthly charts.