Dynamic Area Chart Using IF Statement?
Nov 19, 2011
I'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
View 3 Replies
ADVERTISEMENT
Nov 1, 2006
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.
View 3 Replies
View Related
Feb 5, 2009
I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup.
The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
View 3 Replies
View Related
Oct 31, 2009
My worksheet (excel 2003) contains lots of details and requires different page setup for printing e.g.
(a) Cells B3 to R3 downwards: Data details, which user may add more rows of records
(b) Cell S1-Z20: Data summary (formula) linked with (a)
(c) Cell AA1-AK15: Pivot Table linked with (b)
Therefore, I set buttons, linked with 3 macros to do page setup for the different ranges. However, I had some problems i.e.
1. For print area (a), how to define print area parameter in a macro to check the last row of data. i.e. check for last "blank cell" of column "C-E" (others columns contain formula before input the data)
2. In the future, if I or user insert new columns in area (a), is it possible to setup macro that have print area for (a) - (c) shifted without changing the parameter in the macro?
View 3 Replies
View Related
Dec 17, 2006
I have a sheet with a top portion like a fax cover letter.
Starting at row 17 I merged from B thru L.
This is where the user will type in a message.
The message can extend several pages long, so I merged about 100 rows same as 17.
My sheet only will extend to column N.
There may be instances where the user may leave several rows blank in between what they type.
I am looking for a way to set the print area from A1 to the last row of data and not the last row with formatting.
I have a command button the user is supposed to click when they want to print the sheet.
This is acceptable, but if there was a way around that it would be better.
Note:
I do have code in the sheet that will automatically adjust the row height in the merged cells if the data gets too wide. I do not want to upset that.
I have just recently started to learn VBA, so my knowledge of the various commands and syntax is limited.
This is what I have right now that doesn't work.
Private Sub CommandButton2_Click()
Dim LastCell, DataCells As Range
Dim LastDataRow As Integer
Set LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Set DataCells = ActiveSheet.Cells.SpecialCells(xlconstants)
LastDataRow = DataCells.Cells(DataCells.Cells.Count).Row
ActiveSheet.PageSetup.PrintArea = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastDataRow, LastCell.Column)).Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
View 9 Replies
View Related
Dec 18, 2011
I am trying to create an operational dashboard and I have some List Boxes for which I am preparing the data. The Dashboard is separare worksheet and the calculations are done in several additional sheets. The results, based on the chosen values, are updated in the Dashboard sheet in an area starting with column AO and can be like $AO$4:$BR$37, then $CA$4:$DD$37, and so on: total 6 or seven areas.
I would like to paste one area at the time (with all formats, including font, values, borders, merged cells, etc.) into the “physical” dashboard – the visual area on the screen. I do not want to make this operation manually; shall be automatically.
View 4 Replies
View Related
Oct 28, 2013
Code:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlAreaStacked
I created a simple stacked area type in vba. Is it possible and how to have this stacked area to get values from combobox and textbox?
View 3 Replies
View Related
May 5, 2009
Currently I'm working with an amount of data that corresponds with one data point every day in one year. Not every day is measured thus nothing is entered, some days can also be 0. Therefore I've made an if statement in the column next to the raw data column with an if statement returning #N/A if a cell contains nothing or 0.
This gives me normally no problems when I want to make a Line chart, excel just skips the #N/A cells and draws the line to the next data point (that's the whole reason for the if statement). However this is impossible with area charts. I can understand why, but I still want it as nothing or 0 in a cell not necessarily mean that nothing happened.
View 9 Replies
View Related
Jan 29, 2007
Once a user of my workbook has input various price levels on the Input Page, the data is sorted on the output page. As the data on the Output Page is quite vast and not all of it is relevant on a particular day, the user is interested in just printing a certain range of data that are in 5 columns. I am trying to figure out how to set up a macro that will allow the user to enter into an Input Box the range to print (ie. Cells C25:G60).
I found one example on this site listed below that allows one to do that but I would like to make it more user friendly. I do not want to make the user have to write out Excel cooridinates. Specifically by allowing the user to input in the Input Box a High price level in Column C and a Low Price level in Column C ; The macro will look in Column C for the highest instance of this High Price and begin the range down to the last instance of the Low Price (there can be several instances of the exact price level.) The macro would then create the print preview of the cells Cxx:Gyy.
Sub SelectPrintArea()
Dim PrintThis As Range
ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
(Prompt:="Select the Print Range", Title:="Select", Type:=8)
PrintThis.Select
Selection.Name = "NewPrint"
ActiveSheet.PageSetup.PrintArea = "NewPrint"
ActiveSheet.PrintPreview
End Sub
View 9 Replies
View Related
Dec 29, 2008
I'm trying to create an area chart to show the change in the number of staff working in my store over time. Unfortunately, Excel likes to draw diagonal lines. That is if I have the following data:
9:00AM - 1 staff
10:00AM - 2 staff
11:00AM - 1 staff
Excel will draw a diagonal line between each data point, rather than creating a box. This gives the illusion that there are somehow half people working at 9:30 and 10:30. Is there a workaround?
View 3 Replies
View Related
Nov 30, 2011
I have been measuring variation against some key performance indicators and trying to plot them on a chart i.e., line chart.
Is there any way in Excel that i can highlight in different colours the area between the variable and benchmarks? I want to fill the area say as green when the variable performed better than benchmark (area between the variable line and benchmark line) and as red if variable under performed than benchmark (area between benchmark and variable.
View 1 Replies
View Related
Aug 4, 2008
trying to find a way to insert a table into an active chart area.
unlike inserting charts as objects inside of a spreadsheet, i am trying to insert a table of the spreadsheet into the chart window.
the purpose is so that i may have a numerical reference to my data.
usually i post questions to which i have begun work on - but i have had no luck even brainstorming how this would be done!
the ONLY solution i have surmised is to insert a table into a chart area as an Object(picture)
but how would one write a macro for making dynamic range screen shotpicture files?
View 4 Replies
View Related
May 16, 2014
I have a series of X-Y Data (0-10 in both axis) that I want to overlay on an area chart to show outliers outside of an accepted condition. I have been able to create the area chart and the scatter chart separately but when I try to combine them the axis gets messed up. I tried using a secondary axis but I am not that skilled
I have attached the data and my feeble attempts at creating this chart.
View 2 Replies
View Related
Jan 23, 2012
I need to have a footer that displays the file name in the chart area so that it will copy with the chart when I paste into a word document.
View 1 Replies
View Related
Oct 26, 2006
I have plenty of experience with VBA outside of charts so that's where I need assistance. I need to get SeriesCollection(4), the PTAI series, to appear on the area chart second from the bottom instead of on the top.
The order for the non-line series should be, from top to bottom:
SeriesCollection(3) - Non-Mission AC ( Training, T&E, AMarc series)
SeriesCollection(2) - Total Pipeline series
SeriesCollection(4) - PTAI series
SeriesCollection(1) - Available PMAI series
Here's the
Sub MakeSandChart()
Dim ChartSource As Range
Dim ChartName As String
Dim ChartSheet As String
Dim yCount As Integer
Dim PAA_Value As Integer
Application. ScreenUpdating = False
passtype = Mid(ActiveSheet.Name, 5, 1)
Set ws = Worksheets(ActiveSheet.Name)
ChartName = Left(ActiveSheet.Name, 5)
ChartSheet = ChartName & " Chart"
ChartName = "F18" & passtype
Set ChartSource = Union(Range("SummRowDate" & passtype), _
Range("SummRowTAI" & passtype), _
Range("SummRowNM" & passtype), _
Range("SummRowTotPipe" & passtype), _
Range("SummRowPTAI" & passtype), _
Range("SummRowPMAI" & passtype), _
Range("SummRowPlanPAA" & passtype), _
Range("SummRowWorkPAA" & passtype), _
Range("SummRowDrillPAA" & passtype), _
Range("SummRowBaseline" & passtype))
Worksheets(ChartSheet).Activate
View 9 Replies
View Related
May 4, 2007
I have charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use chartobjects(i).plotarea (top, left, width, height) for that.
Unfortunately when I set these values they still change and get antoher value, there is some scaling going on in Excel and I do not know where that comes from. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned.
De data for resizing the charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
With ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
End With
View 9 Replies
View Related
Sep 11, 2007
I have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006
View 4 Replies
View Related
Jun 6, 2008
I'm trying to produce step charts like Andy Pope does on his web site using the area chart method. http://www.andypope.info/charts/stepchart.htm. This involves duplicating each of the values in each data series, which is tedious if there are a lot of points. Plus the fact that the area series is offset by 1 from the axis series. Can anyone think of a formula to do this? There are plenty of posts here about deleting duplicates, but I couldn't find much about creating duplicates. I can duplicate row numbers using =CEILING(ROW(),2)/2
View 4 Replies
View Related
Jan 6, 2010
I have a stacked area chart located on its own individual worksheet. I would like to use VBA to apply colours to individual series based on the name of that series. I have applied the following code which I found on the following help forum, changing it slightly to fit my needs: http://www.ozgrid.com/forum/showthread.php?t=91381
View 3 Replies
View Related
Jan 29, 2010
how to resize the plotarea of a chart so that it wont overlap with the title. I can't seem to find the appropriate property to let me determine the height or the bottom edge of the chart title.
View 2 Replies
View Related
Oct 2, 2006
I am creating a line chart with markers using 5 series of data. I want to colour in/shade the area under the lowest series.
View 6 Replies
View Related
Feb 17, 2014
I am using an area chart to display the price spread between different types of wheat.
Now the problem I am having is that, at the "end" of the chart, ie. the most recent observation date, the line drops to zero while the value is actually well above zero. How can I avoid this ?
I am attaching a screenshot : Wheat Spread.PNG
View 3 Replies
View Related
May 16, 2008
This seems so simple but i looked everywhere and i cannot find the vba script to add a 3 point solid black line border around the chart and plot area of my graphs. The record macro option does not record this.
View 3 Replies
View Related
Dec 19, 2008
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?
View 9 Replies
View Related
May 23, 2007
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 Related
Aug 20, 2007
I 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.
View 7 Replies
View Related
Jun 28, 2005
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 Related
Jan 30, 2014
make it working, tried from online thread but unable to get it done.Dynamic.xlsx
View 5 Replies
View Related
Feb 5, 2014
[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.
View 2 Replies
View Related
Feb 6, 2014
(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'.
View 6 Replies
View Related
