Hi There,

I have a complex model that I need to do a sensitivity analsyis on. I have three variables which I want to vary from 80% to 120% in 10% increments, the three variables will produce one results. Rather than changing each value manually and copying the resulting value to create a table, I would like to be able to create a table that will carry all this out automatically.

I do remember doing this before, but for the life of me I can't remember how. Any ideas?

Thanks

I have a complex model that I need to do a sensitivity analsyis on. I have three variables which I want to vary from 80% to 120% in 10% increments, the three variables will produce one results. Rather than changing each value manually and copying the resulting value to create a table, I would like to be able to create a table that will carry all this out automatically.

I do remember doing this before, but for the life of me I can't remember how. Any ideas?

Thanks

- Tornado/Sensitivity Chart
- Sensitivity Table
- Spreadsheet/VBA Consulting - 50% Off or Even Free
- Spreadsheet/VBA Project Consulting: 50% Discount or Even Free - Limited Time Only
- VBA Code to reformat charts does not run properly if run on all charts
- Gradient Colorize Chart Points Based On Cell Reference
- Charts: Disappearing Gridlines in the Last of 7 Columns
- Export Dynamic Chart To Word With Vba
- Sensitivity analysis
- Unstable Pivot Chart - crashes
- Exporting excel charts with form controls (spinner) to Powerpoint
- Range sensitivity macro
- Help: Dynamic Stock Chart and Range Calculations
- Price elasticity and Sensitivity calculation
- Price Sensitivity calculation
- How Do I Create a fancy Tornado Chart with data labels?
- Pasting Excel charts to PPT when the target slide and location varies
- Increasing chart range incrementally for each series
- Micro chart/graphs
- Offset Match
- Create a Pivot Chart from multiple, separate data tables
- Custom Menu available from chart tab
- Paste text into chart
- Stacked bar of bar chart

I am trying to use a stacked bar chart to create a Sensitivity/Tornado chart.

I need this chart to be able to accept variable data (this is part of a large Excel workbook that users change data in). The data can be positive or negative (or span between).

The users would like to combine two sets of data (one % and one $) into one chart - so a Primary and Secondary axis are needed (however if this cannot be done, then I would just create two seperate ones).

I can create the automated chart, with two axis (using VBA code behind for some settings) in the positive number world. I create a relationship between the two sets of data (axis) and then set my min and max values, axes crossing line, and major gridlines based on this relationship. Reloading data is fine so long as it is positve.

Once I started adding negative numbers I discovered the 3 points of data would not work and was told to I would need 7 data sets, 4 sets are blank and 3 sets have fill.

I attempted to apply this however I am not sure how I could automatically determine which items should have fill and which shouldn't.

Given this data:

Expected amount: 30

Item#____Low____High

Num1____-80____-25

Num2____-50____25

Num3____25____100

I believe I need this layout of the 7 points:

Item#___data1__data2__data3__data4__data5__data6__data7

Num1___ -25___-55_____-80

Num2_________-50_____-50_________________25_____25

Num3________________________25____75___________100

I assume I would have to programmatically fill and not fill the correct bars but I am not sure how to do that.

I was hoping to find the answer in this thread but Steve never posted his solution/example.

Not sure if this helps but here is the code I use to set the chart settings on the two axes chart:

'Set the $ data title ActiveSheet.ChartObjects("Chart1").Activate ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "NPV - Expected Value: " & ActiveSheet.Range("AF10").Text 'Set the % data title ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "IRR - Expected Value : " & ActiveSheet.Range("AK10").Text 'Set the minimum and maximum ranges for the $ data, ' Also have it create the major unit lines automatically and set the Expected Value line for $ data With ActiveChart.Axes(xlValue, xlPrimary) .MinimumScale = Range("RngChrt1MinNPV") .MaximumScale = Range("RngChrt1MaxNPV") .MajorUnitIsAuto = False .MajorUnit = Range("RngChrt1NPVUnit") .Crosses = xlCustom .CrossesAt = Range("RngChartNPVExp") End With 'Set the minimum and maximum ranges for the $ data, ' Also have it create the major unit lines automatically and set the Expected Value line for % data With ActiveChart.Axes(xlValue, xlSecondary) .MinimumScale = Range("RngChrt1MinIRR") .MaximumScale = Range("RngChrt1MaxIRR") .MajorUnitIsAuto = False .MajorUnit = Range("RngChrt1IRRUnit") End With

Here is what I have to do to the attached model. Can someone help me set up this sensitivity table? Need as soon as possible.

In FY2010 assume sales = $3 billion

For the following: 13-15% EBITDA (not EBIT) margin

what EBIT margin is that?

what EBT margin is that?

ALSO -- for interest expense line assume a $200-$250mm reduction in debt...what does that get you to in EPS? To figure change in interest subtract $200 and $250 x 7%

please make me sensitivity chart for each

These are my instructions. The "for each" == i'm not even sure how to set it up so i'm not even sure what it should look like. EPS is what I ultimately need to get to.

The weighted average interest rate at October 31, 2006 was 7.1%.

Tired of spending hours on time-consuming, labor intensive work?

Try UDQ Consulting Services -

Consulting Cost Starting from $25.00 - Your Small Investment Could Save Your

Hours Even Days of Labor-Intensive Repetitive Work!

Most of the time, you can receive solution to your problem within 24-72

hours.

www.geocities.com/UDQServices

Tired of spending hours on time-consuming, labor intensive work?

Try UDQ Consulting Services -

Consulting Cost Starting from $25.00 - Your Small Investment Could Save Your

Hours Even Days of Labor-Intensive Repetitive Work!

Most of the time, you can receive solution to your problem within 24-72

hours.

www.geocities.com/UDQServices UDQServices@GMail.com

UDQ Consulting Services Believes that Every Problem Should Have an Easy &

Simple Solution

Your "Impossible" Task Could Be Someone Else's "Piece of Cake"

You first consulting work could be free if it is not too difficult - limited

time only!

Project Based Consulting Could be Either On-Site or Remote

No matter what problem you have, even you think it is impossible or has

nothing to do with Excel, try UDQ Consulting Services! You will get an

affordable and satisfactory solution.

= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -

UDQ Services has years of experience with MS Excel and VBA (Visual Basic for

Application) and has developed various kinds of utility programs, add-ins,

templates, spreadsheet applications, and database applications.

For MS Excel: File Password Protection, Sheets/Range Password Protection,

Conditional Formatting and Custom Formatting, Data Validation, List, Forms

and Controls, Array Functions, Database Functions, Value Lookup Functions,

Range Names, Customize Menu and Toolbar, Solver, Goal Seek, Scenario

Analysis, Statistic Analysis, Group and Outline, Subtotal, Filter and

Advanced Filter, Sort, Database Query and Web Query, Pivot Table and Pivot

Chart, XML, and Macros.

For Excel VBA: User-Defined Functions, Macros, Work with

Ranges/Charts/Sheets/Workbooks/Files/Directories, Forms and Controls for GUI

(Graphic User Interface), Customize Menu/Toolbars, API, External Database

Access via SQL, Extraction of Data from Internet, Extraction of Data from

External ASCII Files, Interact with Other Offices Applications (like Word,

Outlook), Class Module, etc.

VB6/Access/Others: UDQ Services also has experience with MS Access(Table,

Form, Query, Reports, Stored Procedure, Macro, VBA, Database Application),

VB6(ADO, DAO, Objects, API, ActiveX), Java, PHP, MySQL, Apache, HTML,C/C++,

SPSS, Matlab, etc.

= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -

List of Recent Projects:

P001: Mortgage Transaction Spreadsheet Database

Developed a spreadsheet database application, which includes a user-friendly

loan transaction entry interface, password protected administrative page to

allow add/remove loan officer and loan manager, monthly and annual report

for loan officers / managers, monthly state report, etc.

P002: Customer List Converted from Excel Table to Word

Developed an interface to convert a list of customers from an Excel table

into a new Word document, one customer per page, including user-specified

headers.

P003: Amortization Calculator

Developed an amortization calculator: based on user specified loan amount,

interest rate, and term, calculate the monthly payment, total payment, total

interest payment, payment schedule table, interest rate sensitivity charts,

etc.

P004: Account Data Extraction from Internet

Developed a utility program to extract housing pricing/tax payment

information from a government website based on a list of house address.

P005: Sales Invoice Database

Developed a spreadsheet database for sales invoicing. If user input an

existing customer, then the shipping/billing address, VAT number, Account

Number, etc. for that customer will be automatically filled into the

invoice, otherwise, the new customer entry will be automatically added to

the existing customer list database, automatically fill product information,

calculate the invoice, create monthly and annual report for sales.

P006: Data Extraction from Hundreds of ASCII Files

Developed a spreadsheet application with user-friendly interface for

importing data from hundreds for external ASCII files based on

user-specified keywords.

P007: Mass Email based on Criteria

Developed a mass email program using Spreadsheet. Based on criteria (replied

or not), send email (reminder) to each individual in the list with different

body contents, different subject, and different salutation lines - about

1000 emails in the list.

www.geocities.com/UDQServices UDQServices@GMail.com

This problem is a bit time sensitive as I am trying to hit a deadline of next week to have this ironed out. This having been said....

I've got a relatively large spreadsheet (~70 MB) containing numerous pivot tables (all based on one mater table), numerous chart exhibits (50+). I've written a lengthy procedure to format all of the different charts to a specification I can then CopyPicture to PowerPoint.

Here's my problem. If I run the code individually on a single chart it works like a charm. If I pass in a list of the charts, it will format some correctly, some slightly off and some so screwed up you don’t recognize them. (see attached JPG for an example)

However, if I go back to the most screwed up chart and re-run the routine individually, bang, all is well. I'm going to have a lot of these spreadsheets as this is intended to be a PowerPoint deck generator, so having the ability to have all these charts formatted exactly is critical. We’ll be generating some 15 to 20 of these at one time so having to do each one by hand would prove to be extraordinarily inefficient.

I would love to have some advice on this. Attached is a copy of the formatting procedure (it is not prettied up yet, but does the trick) and I can upload a copy of the file if necessary, but remember it is some 70mb.

A couple of other comments:

I've seen some (but not much) commentary out there that seems to point to a print driver, but having followed the poster's advice and installed a different (HP 5M - recommended by poster), still no luck.

I have seen that if I have the zoom set to different values (i.e. 25% so I can see most of the charts or 75% to get a good look at it) it seems to somehow make a difference.

Does the pnt count (i.e. cht.ChartTitle.Top=6)change when you zoom?

VB:Public Const rrGreen As Long = 26112 Public Const rrGold As Long = 52479 Public Const rrBlue As Long = 13395507 Public Const rrOrange As Long = 26316 Public Const rrRedBrown As Long = 128 Public Const rrAuto As Long = -4105 Public Const rrNone As Long = -4142 [SIZE=2]Public Sub ResetIndividualFormat()[/SIZE] [SIZE=2] Dim co As Excel.Chart[/SIZE] [SIZE=2] Set co = ActiveChart[/SIZE] [SIZE=2] Call ResetFormat(co)[/SIZE] [SIZE=2] Set co = Nothing[/SIZE] [SIZE=2]End Sub[/SIZE] [SIZE=2]Public Sub ResetFormat(co As Excel.Chart)[/SIZE] [SIZE=2] Dim sr As Series[/SIZE] [SIZE=2] Dim seriesCol As SeriesCollection[/SIZE] [SIZE=2] Dim pt As Point[/SIZE] [SIZE=2] Dim wks As Excel.Worksheet[/SIZE] [SIZE=2] Dim ax As Axis[/SIZE] [SIZE=2] Dim axyl As Axis[/SIZE] [SIZE=2] Dim axyr As Axis[/SIZE] [SIZE=2] Dim pa As PlotArea[/SIZE] [SIZE=2] Dim dt As DataTable[/SIZE] [SIZE=2] Dim rngNames As Range[/SIZE] [SIZE=2] Dim j As Double[/SIZE] [SIZE=2] Dim x As Double[/SIZE] [SIZE=2] Dim threemonval As Double[/SIZE] [SIZE=2] Dim twelevemonval As Double[/SIZE] [SIZE=2] Set rngNames = Range("CE_NAMES")[/SIZE] [SIZE=2] itm = rngNames.Value[/SIZE] [SIZE=2] 'Sized for powerpoint[/SIZE] [SIZE=2] co.Parent.Height = 393.75[/SIZE] [SIZE=2] co.Parent.Width = 720[/SIZE] [SIZE=2] If co.ChartType = xlLine Then[/SIZE] [SIZE=2] Call ResetLine(co)[/SIZE] [SIZE=2] Goto cleanup[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] If co.HasDataTable = True Then[/SIZE] [SIZE=2] y = 7[/SIZE] [SIZE=2] ElseIf co.HasLegend = True Then[/SIZE] [SIZE=2] y = 6[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] Goto cleanup[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] j = 0[/SIZE] [SIZE=2] For Each sr In co.SeriesCollection[/SIZE] [SIZE=2] 'ROLLING 3[/SIZE] [SIZE=2] If InStr(1, sr.Name, "Rolling 3") Then[/SIZE] [SIZE=2] sr.ChartType = xlLineMarkers[/SIZE] [SIZE=2] sr.MarkerBackgroundColor = rrGold[/SIZE] [SIZE=2] sr.MarkerForegroundColor = rrGold[/SIZE] [SIZE=2] sr.MarkerStyle = xlMarkerStyleSquare[/SIZE] [SIZE=2] sr.MarkerSize = 5[/SIZE] [SIZE=2] sr.Border.Color = rrGold[/SIZE] [SIZE=2] sr.Border.Weight = xlThick[/SIZE] [SIZE=2] On Error Resume Next[/SIZE] [SIZE=2] sr.AxisGroup = xlPrimary[/SIZE] [SIZE=2] 'find largest rolling 3 value for chart scaling[/SIZE] [SIZE=2] If IsEmpty(sr.Values(1)) Then[/SIZE] [SIZE=2] threemonval = 0[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] threemonval = sr.Values(1)[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] For Each v In sr.Values[/SIZE] [SIZE=2] If v > threemonval Then[/SIZE] [SIZE=2] threemonval = v[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] Next v[/SIZE] [SIZE=2] 'ROLLING 12[/SIZE] [SIZE=2] ElseIf InStr(1, sr.Name, "Rolling 12") Then[/SIZE] [SIZE=2] sr.ChartType = xlColumnClustered[/SIZE] [SIZE=2] sr.Interior.Pattern = 1[/SIZE] [SIZE=2] sr.Interior.Color = rrGreen[/SIZE] [SIZE=2] On Error Resume Next[/SIZE] [SIZE=2] If InStr(co.ChartTitle.Caption, "Ratio") + InStr(co.ChartTitle.Caption, "Renewal Payroll Change") + InStr(co.ChartTitle.Caption, "Renewal Rate Change") > 0 Then[/SIZE] [SIZE=2] sr.AxisGroup = xlPrimary[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] sr.AxisGroup = xlSecondary[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] 'find largest rolling 12 value for chart scaling[/SIZE] [SIZE=2] If IsEmpty(sr.Values(1)) Then[/SIZE] [SIZE=2] twelvemonval = 0[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] twelvemonval = sr.Values(1)[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] twelvemonval = sr.Values(1)[/SIZE] [SIZE=2] For Each v In sr.Values[/SIZE] [SIZE=2] If v > twelvemonval Then[/SIZE] [SIZE=2] twelvemonval = v[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] Next v[/SIZE] [SIZE=2] 'MONTH[/SIZE] [SIZE=2] ElseIf InStr(1, sr.Name, "Month") Then[/SIZE] [SIZE=2] sr.ChartType = xlLineMarkers[/SIZE] [SIZE=2] sr.MarkerBackgroundColorIndex = 1[/SIZE] [SIZE=2] sr.MarkerForegroundColorIndex = 1[/SIZE] [SIZE=2] sr.MarkerStyle = xlMarkerStyleSquare[/SIZE] [SIZE=2] sr.MarkerSize = 5[/SIZE] [SIZE=2] sr.Border.LineStyle = xlLineStyleNone[/SIZE] [SIZE=2] On Error Resume Next[/SIZE] [SIZE=2] sr.AxisGroup = xlPrimary[/SIZE] [SIZE=2] On Error Goto err_handler[/SIZE] [SIZE=2] 'BUDGET[/SIZE] [SIZE=2] ElseIf sr.Name = "Budget" Then[/SIZE] [SIZE=2] sr.ChartType = xlLine[/SIZE] [SIZE=2] sr.Border.ColorIndex = 3[/SIZE] [SIZE=2] sr.Border.Weight = xlThick[/SIZE] [SIZE=2] On Error Resume Next[/SIZE] [SIZE=2] sr.AxisGroup = xlPrimary[/SIZE] [SIZE=2] On Error Goto err_handler[/SIZE] [SIZE=2] ElseIf sr.Name = "Indexed Change" Then[/SIZE] [SIZE=2] sr.ChartType = xlColumnClustered[/SIZE] [SIZE=2] sr.Interior.Pattern = 1[/SIZE] [SIZE=2] sr.Interior.Color = rrGreen[/SIZE] [SIZE=2] ElseIf sr.Name = "2007 Baseline" Then[/SIZE] [SIZE=2] sr.ChartType = xlLine[/SIZE] [SIZE=2] sr.Border.ColorIndex = 6[/SIZE] [SIZE=2] sr.Border.Weight = xlThick[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] Next sr[/SIZE] [SIZE=2] For Each cg In co.ChartGroups()[/SIZE] [SIZE=2] cg.GapWidth = 40[/SIZE] [SIZE=2] Next cg[/SIZE] [SIZE=2] Set axyl = co.Axes(xlValue, xlPrimary)[/SIZE] [SIZE=2] axyl.HasTitle = True[/SIZE] [SIZE=2] axyl.Border.Color = rrGreen[/SIZE] [SIZE=2] If InStr(co.ChartTitle.Caption, "Ratio") + InStr(co.ChartTitle.Caption, "Renewal Payroll Change") + InStr(co.ChartTitle.Caption, "Renewal Rate Change") > 0 Then[/SIZE] [SIZE=2] axyl.HasTitle = False[/SIZE] [SIZE=2] ElseIf InStr(co.ChartTitle.Caption, "Renewal Payroll Change") > 0 Then[/SIZE] [SIZE=2] axyl.HasTitle = False[/SIZE] [SIZE=2] ElseIf InStr(co.ChartTitle.Caption, "Renewal Rate Change") > 0 Then[/SIZE] [SIZE=2] axyl.HasTitle = False[/SIZE] [SIZE=2] ElseIf InStr(co.ChartTitle.Caption, "Items") > 0 Then[/SIZE] [SIZE=2] axyl.AxisTitle.Caption = "3 Month Items"[/SIZE] [SIZE=2] ElseIf InStr(co.ChartTitle.Caption, "Premium") > 0 Then[/SIZE] [SIZE=2] axyl.AxisTitle.Caption = "3 Month Premium (000s)"[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] If InStr(co.ChartTitle.Caption, "Ratio") + InStr(co.ChartTitle.Caption, "Renewal Payroll Change") + InStr(co.ChartTitle.Caption, "Renewal Rate Change") = 0 Then[/SIZE] [SIZE=2] Set axyr = co.Axes(xlValue, xlSecondary)[/SIZE] [SIZE=2] axyr.MajorGridlines.Border.Color = rrGreen[/SIZE] [SIZE=2] axyr.HasTitle = True[/SIZE] [SIZE=2] If InStr(co.ChartTitle.Caption, "Items") > 0 Then[/SIZE] [SIZE=2] axyr.AxisTitle.Caption = "12 Month Items"[/SIZE] [SIZE=2] ScaleCalcIncrement = 10[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] axyr.AxisTitle.Caption = "12 Month Premium (000s)"[/SIZE] [SIZE=2] ScaleCalcIncrement = 1000[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] axyr.AxisTitle.Orientation = -90[/SIZE] [SIZE=2] axyl.MinimumScaleIsAuto = False[/SIZE] [SIZE=2] axyr.MinimumScaleIsAuto = False[/SIZE] [SIZE=2] axyl.MinimumScale = 0[/SIZE] [SIZE=2] axyr.MinimumScale = 0[/SIZE] [SIZE=2] axyl.MaximumScale = 0[/SIZE] [SIZE=2] axyr.MaximumScale = 0[/SIZE] [SIZE=2] If twelvemonval Mod threemonval 0 Then[/SIZE] [SIZE=2] Ratio = Int(twelvemonval / threemonval) + 1[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] Ratio = twelvemonval / threemonval[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] j = 0[/SIZE] [SIZE=2] Do Until axyl.MaximumScale > threemonval[/SIZE] [SIZE=2] axyr.MaximumScale = WorksheetFunction.Ceiling(twelvemonval + j, ScaleCalcIncrement)[/SIZE] [SIZE=2] axyl.MaximumScale = WorksheetFunction.Ceiling(threemonval, 25)[/SIZE] [SIZE=2] axyl.MaximumScale = axyr.MaximumScale / Ratio[/SIZE] [SIZE=2] axyl.MajorUnit = axyr.MajorUnit / Ratio[/SIZE] [SIZE=2] j = j + ScaleCalcIncrement[/SIZE] [SIZE=2] If j > 1000000 Then[/SIZE] [SIZE=2] MsgBox "Scale Calc Error"[/SIZE] [SIZE=2] Goto cleanup[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] Loop[/SIZE] [SIZE=2] axyl.MinimumScaleIsAuto = False[/SIZE] [SIZE=2] axyr.MinimumScaleIsAuto = False[/SIZE] [SIZE=2] axyl.MinimumScale = 0[/SIZE] [SIZE=2] axyr.MinimumScale = 0[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] co.HasAxis(xlCategory, xlPrimary) = True[/SIZE] [SIZE=2] Set ax = co.Axes(xlCategory, xlPrimary)[/SIZE] [SIZE=2] ax.HasTitle = False[/SIZE] [SIZE=2] co.ChartArea.AutoScaleFont = False[/SIZE] [SIZE=2] co.ChartArea.Font.FontStyle = "Arial"[/SIZE] [SIZE=2] co.ChartArea.Font.Size = 11.5[/SIZE] [SIZE=2] co.ChartTitle.Font.Size = 10[/SIZE] [SIZE=2] co.ChartTitle.Font.Color = rrGreen[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.PlotArea.InsideLeft + co.PlotArea.InsideWidth / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] co.ChartArea.Interior.ColorIndex = xlNone[/SIZE] [SIZE=2] co.ChartArea.Fill.Visible = msoFalse[/SIZE] [SIZE=2] Set pa = co.PlotArea[/SIZE] [SIZE=2] pa.Interior.ColorIndex = xlNone[/SIZE] [SIZE=2] pa.Border.Color = rrGreen[/SIZE] [SIZE=2] pa.Border.Weight = xlThin[/SIZE] [SIZE=2] pa.Border.LineStyle = xlContinuous[/SIZE] [SIZE=2] axyl.AxisTitle.Font.Size = 8[/SIZE] [SIZE=2] axyl.TickLabels.Font.Size = 8[/SIZE] [SIZE=2] axyl.Border.Color = rrGreen[/SIZE] [SIZE=2] axyl.MajorGridlines.Border.Color = rrGreen[/SIZE] [SIZE=2] axyl.MajorGridlines.Border.Weight = xlThin[/SIZE] [SIZE=2] axyl.MajorGridlines.Border.LineStyle = xlContinuous[/SIZE] [SIZE=2] axyr.AxisTitle.Font.Size = 8[/SIZE] [SIZE=2] axyr.TickLabels.Font.Size = 8[/SIZE] [SIZE=2] axyr.Border.Color = rrGreen[/SIZE] [SIZE=2] axyl.MajorGridlines.Border.Weight = xlThin[/SIZE] [SIZE=2] axyl.MajorGridlines.Border.LineStyle = xlContinuous[/SIZE] [SIZE=2] co.ChartTitle.Font.Color = rrGreen[/SIZE] [SIZE=2] Select Case y[/SIZE] [SIZE=2] Case 6[/SIZE] [SIZE=2] ax.TickLabels.Orientation = 90[/SIZE] [SIZE=2] ax.TickLabels.Font.Size = 8[/SIZE] [SIZE=2] co.HasDataTable = False[/SIZE] [SIZE=2] co.HasLegend = True[/SIZE] [SIZE=2] co.Legend.Position = xlLegendPositionBottom[/SIZE] [SIZE=2] co.PlotArea.Left = 20 '17[/SIZE] [SIZE=2] co.PlotArea.Top = 48[/SIZE] [SIZE=2] co.PlotArea.Width = 650 '663[/SIZE] [SIZE=2] co.PlotArea.Height = 312[/SIZE] [SIZE=2] co.Legend.Font.Size = 8[/SIZE] [SIZE=2] co.Legend.Border.Color = rrGreen[/SIZE] [SIZE=2] co.Legend.Interior.ColorIndex = xlNone[/SIZE] [SIZE=2] co.Legend.Left = co.PlotArea.InsideLeft + ((co.PlotArea.InsideWidth - co.Legend.Width) / 2)[/SIZE] [SIZE=2] co.ChartTitle.Top = 8[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Left + co.ChartArea.Width / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyl.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyr.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyl.AxisTitle.Left = 1[/SIZE] [SIZE=2] axyr.AxisTitle.Left = 700[/SIZE] [SIZE=2] If InStr(co.ChartTitle.Caption, "Renewal Payroll Change") + InStr(co.ChartTitle.Caption, "Renewal Rate Change") 0 Then[/SIZE] [SIZE=2] axyl.MaximumScaleIsAuto = True[/SIZE] [SIZE=2] axyl.MinimumScaleIsAuto = True[/SIZE] [SIZE=2] axyl.MajorUnitIsAuto = True[/SIZE] [SIZE=2] ax.TickLabelPosition = xlTickLabelPositionLow[/SIZE] [SIZE=2] co.PlotArea.Left = 17[/SIZE] [SIZE=2] co.PlotArea.Top = 48[/SIZE] [SIZE=2] co.PlotArea.Width = 663[/SIZE] [SIZE=2] co.PlotArea.Height = 312[/SIZE] [SIZE=2] co.HasLegend = True[/SIZE] [SIZE=2] co.Legend.Position = xlLegendPositionBottom[/SIZE] [SIZE=2] co.Legend.Font.Size = 8[/SIZE] [SIZE=2] co.Legend.Border.Color = rrGreen[/SIZE] [SIZE=2] co.Legend.Interior.ColorIndex = xlNone[/SIZE] [SIZE=2] co.Legend.Left = co.PlotArea.InsideLeft + ((co.PlotArea.InsideWidth - co.Legend.Width) / 2)[/SIZE] [SIZE=2] co.ChartTitle.Top = 8[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyl.AxisTitle.Top) / 2[/SIZE] [SIZE=2] co.ChartTitle.Top = 6[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Left + co.ChartArea.Width / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] axyl.AxisTitle.Left = 1[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] Case 7[/SIZE] [SIZE=2] co.HasDataTable = True[/SIZE] [SIZE=2] co.DataTable.Font.Size = 7[/SIZE] [SIZE=2] co.HasLegend = False[/SIZE] [SIZE=2] Set dt = co.DataTable[/SIZE] [SIZE=2] dt.ColorIndex = xlNone[/SIZE] [SIZE=2] dt.Border.Color = rrGreen[/SIZE] [SIZE=2] dt.weigh = xlThin[/SIZE] [SIZE=2] dt.LineStyle = xlContinuous[/SIZE] [SIZE=2] dt.ShowLegendKey = True[/SIZE] [SIZE=2] dt.HasBorderHorizontal = True[/SIZE] [SIZE=2] dt.HasBorderVertical = True[/SIZE] [SIZE=2] dt.HasBorderOutline = True[/SIZE] [SIZE=2] If InStr(1, co.ChartTitle.Caption, "Items") > 0 Then[/SIZE] [SIZE=2] co.PlotArea.Width = 685[/SIZE] [SIZE=2] co.PlotArea.Height = 312[/SIZE] [SIZE=2] co.PlotArea.Left = 5[/SIZE] [SIZE=2] co.PlotArea.Top = 48[/SIZE] [SIZE=2] co.ChartTitle.Top = 8[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Left + co.ChartArea.Width / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyl.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyr.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyl.AxisTitle.Left = 0[/SIZE] [SIZE=2] axyr.AxisTitle.Left = 701[/SIZE] [SIZE=2] ElseIf InStr(1, co.ChartTitle.Caption, "Premium") > 0 Then[/SIZE] [SIZE=2] co.PlotArea.Width = 650 '685[/SIZE] [SIZE=2] co.PlotArea.Height = 312[/SIZE] [SIZE=2] co.PlotArea.Left = 5[/SIZE] [SIZE=2] co.PlotArea.Top = 48[/SIZE] [SIZE=2] co.ChartTitle.Top = 8[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Left + co.ChartArea.Width / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyl.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyr.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyr.AxisTitle.Top) / 2[/SIZE] [SIZE=2] axyl.AxisTitle.Left = 0[/SIZE] [SIZE=2] axyr.AxisTitle.Left = 701[/SIZE] [SIZE=2] ElseIf InStr(co.ChartTitle.Caption, "Renewal Payroll Change") + InStr(co.ChartTitle.Caption, "Renewal Rate Change") 0 Then[/SIZE] [SIZE=2] axyl.MaximumScaleIsAuto = True[/SIZE] [SIZE=2] axyl.MinimumScaleIsAuto = True[/SIZE] [SIZE=2] axyl.MajorUnitIsAuto = True[/SIZE] [SIZE=2] ax.TickLabelPosition = xlTickLabelPositionLow[/SIZE] [SIZE=2] co.PlotArea.Left = 17[/SIZE] [SIZE=2] co.PlotArea.Top = 48[/SIZE] [SIZE=2] co.PlotArea.Width = 663[/SIZE] [SIZE=2] co.PlotArea.Height = 312[/SIZE] [SIZE=2] co.HasLegend = True[/SIZE] [SIZE=2] co.Legend.Position = xlLegendPositionBottom[/SIZE] [SIZE=2] co.Legend.Font.Size = 8[/SIZE] [SIZE=2] co.Legend.Border.Color = rrGreen[/SIZE] [SIZE=2] co.Legend.Interior.ColorIndex = xlNone[/SIZE] [SIZE=2] co.Legend.Left = co.PlotArea.InsideLeft + ((co.PlotArea.InsideWidth - co.Legend.Width) / 2)[/SIZE] [SIZE=2] co.ChartTitle.Top = 8[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.ChartArea.Height[/SIZE] [SIZE=2] axyl.AxisTitle.Top = co.PlotArea.InsideTop + co.PlotArea.InsideHeight / 2 - (co.ChartArea.Height - axyl.AxisTitle.Top) / 2[/SIZE] [SIZE=2] co.ChartTitle.Top = 6[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Width[/SIZE] [SIZE=2] co.ChartTitle.Left = co.ChartArea.Left + co.ChartArea.Width / 2 - (co.ChartArea.Width - co.ChartTitle.Left) / 2[/SIZE] [SIZE=2] axyl.AxisTitle.Left = 1[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2] End Select[/SIZE] [SIZE=2] Application.Wait (Now + TimeValue("0:00:01"))[/SIZE] [SIZE=2]cleanup:[/SIZE] [SIZE=2] Set sr = Nothing[/SIZE] [SIZE=2] Set seriesCol = Nothing[/SIZE] [SIZE=2] Set pt = Nothing[/SIZE] [SIZE=2] Set wks = Nothing[/SIZE] [SIZE=2] Set co = Nothing[/SIZE] [SIZE=2] Set ax = Nothing[/SIZE] [SIZE=2] Set axyl = Nothing[/SIZE] [SIZE=2] Set axyr = Nothing[/SIZE] [SIZE=2] Set pa = Nothing[/SIZE] [SIZE=2] Set dt = Nothing[/SIZE] [SIZE=2] Exit Sub[/SIZE] [SIZE=2]err_handler:[/SIZE] [SIZE=2] If Err.Number = 91 Then[/SIZE] [SIZE=2] Resume cleanup[/SIZE] [SIZE=2] Else[/SIZE] [SIZE=2] MsgBox Err.Number & " " & Error(Err.Number)[/SIZE] [SIZE=2] Resume cleanup[/SIZE] [SIZE=2] End If[/SIZE] [SIZE=2]End Sub[/SIZE]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution.

From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable.

Ideally, i would find the range of the third value, assign a gradient based on the spread, and colorize each point.

My VBA syntax is lacking for chart functions - Any takers?

Thanks,

N. Tabb

Anyway, my new boss and I use one Excel chart almost every day, to keep tabs of work assignments. (The chart has been used for about 1-1/2 years and was created by who-knows.) Because the chart was becoming very long and cumbersome, I decided to do something about it:

I deleted rows below the last row used and columns to the right of the last column used (Column G), so we wouldn’t have to look at unused space. Then I separated the chart into 2 worksheets, 2002 and 2003. At the time, the simplest way seemed to be to copy the entire chart and insert it into a 2nd worksheet, then delete everything below 12/31/02 in the 2nd worksheet and everything above 01/01/03 in the 1st worksheet. That was all easy enough. . . . or so I thought.

Beforehand, light gray gridlines printed vertically and horizontally *throughout* the chart (as did periodic shading to indicate assignments which have been completed). HOWEVER, now they only show up on Columns A-F, but not in Column G. No matter what I do, I can't get gridlines, or borders, in any color, to print for Column G in either worksheet. After a few days of fiddling with it and exhausting the Excel Help feature in everyway I could think of, I turned over the problem to a rep from our IS Dept. to try to figure out what happened and, more importantly, fix it. Between the 2 of us, we've been working at it ad nauseum on and off again for over a week, to absolutely no avail. We are both completely stumped and getting *extremely* frustrated.

:banghead:

Due to the sensitive nature of our work, for review in this forum I have attached sanitized copy of this Problem Child.

Anyone who can help me fix this mess will have my undying gratitude (it’s the best I can offer). Thanks!

So I am trying to create a macro that will update graphs within a Word document with data in an excel file..

Right now I have this code in place. It is code that was used to export data from an excel document to a powerpoint presentation that I have modified to export to word.(It's well documented, but refers to the export to PPT version of the code) The problem I am having is that the if statements below don't seem to do what I want them to do. That being, find the graph that's in the word document and update it with the data from the excel document.

The code below that is meant to modify the text thats in a textbox in the word document with the text from cell C7 on the active excel sheet. That doesn't seem to work either.

Anyone know what I am doing wrong?

VB:Any help is appreciatedSub UpdateWORDDEM() Dim oGraph As Object Dim DocPath As String Dim KeyPt As String ' Get workbook path DocPath = ThisWorkbook.Path ' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint. ' If PowerPoint is already open, you would instead use the GetObject ' method instead. Set wrdApp = CreateObject("Word.Application") ' Set PowerPoint to be Visible. wrdApp.Visible = msoTrue wrdApp.Documents.Open DocPath & "ExportDemo.doc" ' Set rngNewRange to the collection of cells in the active Excel ' workbook and active sheet. Set rngNewRange = ActiveSheet.Range("B10:F14") KeyPt = ActiveSheet.Range("C7") rngNewRange.Select rngNewRange.Copy ' On slide one of Presentation1.ppt, loop through each shape. ' Check to see whether shape is an OLE object. ' Check to see whether OLE object is a Graph 2000 object. The ProgID ' is case sensitive. ' Set oGraph to the Graph object on the slide. ' Paste the cell range into the upper leftmost cell of the graph ' datasheet. This position is designated "00" (two zeros). To designate ' a range to start in the second row, first column, you would use "01". ' Likewise first row, second column is "A0". This will also link the ' datasheet to the Excel Workbook cell range. If you do not want to ' link to the Workbook, just omit the word "True". The default ' choice for the Paste method is "False". With wrdApp.ActiveDocument For Each wrdShape In .Shapes If wrdShape.Type = msoEmbeddedOLEObject Then If wrdShape.OLEFormat.ProgId = "MSGraph.Chart.8" Then Set oGraph = wrdShape.OLEFormat.Object oGraph.Application.DataSheet.Range("00").Paste False End If End If Next wrdShape End With With wrdApp.ActiveDocument For Each wrdShape In .Shapes If wrdShape.Type = msoTextBox Then If wrdShape.Name = "KeyPoint" Then wrdShape.TextFrame.textrange.Text = KeyPt Text = KeyPt End If End If Next wrdShape End With Excel.Application.Visible = msoTrue ThisWorkbook.ActiveSheet.Range("A1").Select Application.CutCopyMode = False End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

THX!

Is there a way I can tabulated (probably in a separate worksheet) the

different results against different tuples of parameter automatically or

easily?

Eg. For the worksheet that calculates current balance (the result) of my

investment porfolio given a set of exchange rates (the parameters), I want a

summary table showing how my balance changes with different exchange rates.

With that table I shall then plot a chart for further sensitivity analysis.

BTW, I am using Excel 2003 professional.

Thanks in advance!

So why does the pivot graph crash. This means my users will have to pre-know what is compatible and what conditions are mutually exclusive, right?

My clients are executive who don't really know any of this...

Any tips/suggestions would be appreaciated~

G.

sensitivity of a series of variables. I want to export this chart and the

spinner capabilty to a power point presentation so that I can use the

spinners to demonstrate the sensitivity of the variables to my audience. How

do I do this?

--

Mike B

I'll simply my problem if I can to this: I have made a financial cash flow budget model with sensitivity parameters in a table below it. For example, a parameter might be exchange rate and along a row are cells for each year that I can enter rates into which are linked back to the model. So changing the rate in 2017 to '$1.50' changes the cost calculations in the model accordingly for that year. Then there is a cell (named Exchangeratebasecase) that I can enter a rate into and all cells in the row (named Exchangerate) for exchange rates will change to that when I click on a button I inserted. That button is assigned to a simple macro like this where the whole row changes to that value:

Sub Exchangerate() Range("Exchangerate").Value = Range("Exchangeratebasecase").Value End SubNow, I would like to find a way where I can eventually have various graphs and charts and tables made (and changed) anytime I run a new macro. That macro would essentially need to run through a "loop" changing the (Exchangeratebasecase) cell to different numbers that I have in another table which in turn change all the values in the whole row (Exchangerate) and the resulting and changing NPV/IRR will be copied to yet another table that will be used for graphs and such. So I might want to have an exchange rate of 1, then 1.1 then 1.2 then 1.3 and each one is tested in the cash flow. I know how to use Selection.copy but that's about it.

I'm trying to create a dynamic stock chart that returns only the 120 most current values in column(D). D1 is the header, and D2:D6 are empty due to adjacent data-sensitive formulas. Data formally begins at D7 and ends at D759. The desired chart series would initially be D:640,D:759. What would be the necessary steps / formula to create a dynamic chart that would continue to 'roll' as new daily-data is entered?

Finally, using the same data range requirements, how would I have H2 for instance, continually calculate the Average of this dynamic series?

The attached file contains all the above information, and any I may have omitted. Thanks for your time and any help.

-Chris

What I have historical data (2010 and 2011relating to

1. number fo students in a program per month/quater and year

2. The price per program per month/qauter and year.

I want to increase the school fees by say 4% in 2012. By increasing the price, will it have a positive/negative effect on the number of student that might enrol for 2012 by month/quater/year?

How can I show this using a chart and pv table?

I will really appreciate your assisatnce.

What I have historical data (2010 and 2011relating to

1. number fo students in a program per month/quater and year

2. The price per program per month/qauter and year.

I want to increase the school fees by say 4% in 2012. By increasing the price, will it have a positive/negative effect on the number of student that might enrol for 2012 by month/quater/year?

How can I show this using a chart and pv table?

I will really appreciate your assisatnce.

Desperate kay

I've adapted a friend's tornado chart to exactly how I want it.

The problem is it only has 4 items in the chart.

I want to recreate the same chart with 5,6,7,8,9,10,11,12,13,14,15 items so that I have a template ready for whenever I need one.

I can't figure out how he has displayed the data labels because they come from a different series to where they actually sit on the chart

(it is a stacked chart with hidden bits and pieces so the labels are actually correct)

Does anyone have any ideas on how this chart was created? (Or how to recreate it in a simpler way that people like myself could recreate?

I have attached the file, feel free to use it if you like it :-)

It's great for presenting NPV sensitivities!

I have little experience using VBA with PPT. What I want to do is relatively simple.

I have two excel sheets with 5 charts, each chart has a specific name.

I have a powerpoint presentation with 10-15 slides, all with different content but five of those slides (slide numbers vary every month) hold the excel charts. Those five slides also have other content like textboxes, etc.

I want to:

1) open the PPT presentation from my hard drive

2) paste the 5 charts into their "places" in the PPT.

Is this possible? How do I "identify" in PPT where each chart goes? Is there a placeholder I can use in PPT or would the excel VBA code include "Top" and "Left" data for each target slide?

If someone could put some sample code I would appreciate it.

Thanks a million,

Onetimesten

I inherited some code that is used to increase the range by one column (on both the upper and lower bound of the data) for each series in whatever bar chart is selected on a sheet.

In most instances this works fine, if the range of data is through a rolling period. However, I'd like to modify the code to increase the range of the upper limit but still keep the lower limit constant (i.e. say the original range is $B$2:$S$2, after running the code I would want the range to be $B$2:$T$2 for that series. If run again it would be $B$2:$U$2 for that series and so on...)

Here is the code. Basically a function 'GetChartRange' is called twice to grab the range for x and y values in the selected chart, the range is returned and then increased using Offset. I think its a case of modifying the 2 statements with .Offset, but Im not sure how I would change them to only increase the upper limit? Any help would be appreciated.

VB:Increase_Chart_Range() Dim mySrs As Series Set MyChart = ActiveChart For Each mySrs In ActiveChart.SeriesCollection Set xv = GetChartRange(MyChart, mySrs.PlotOrder, "xvalues") Set V = GetChartRange(MyChart, mySrs.PlotOrder, "values") ValuesRange = V.Address XValuesRange = xv.Address MyChart.SeriesCollection(mySrs.PlotOrder).Values = V.Offset(0, 1) MyChart.SeriesCollection(mySrs.PlotOrder).XValues = xv.Offset(0, 1) Next mySrs End Sub Function GetChartRange(cht, mySrs, ValOrX) As Range Dim Sf As String Dim CommaCnt As Integer Dim Commas() As Integer Dim ListSep As String * 1 Dim Temp As Variant Set GetChartRange = Nothing On Error Resume Next Sf = cht.SeriesCollection(mySrs).Formula ' Check for noncontiguous ranges by counting commas ' Also, store the character position of the commas CommaCnt = 0 ListSep = Application.International(xlListSeparator) For i = 1 To Len(Sf) If Mid(Sf, i, 1) = ListSep Then CommaCnt = CommaCnt + 1 Redim Preserve Commas(CommaCnt) Commas(CommaCnt) = i End If Next i If CommaCnt > 3 Then Exit Function Select Case UCase(ValOrX) Case "XVALUES" Temp = Mid(Sf, Commas(1) + 1, Commas(2) - Commas(1) - 1) Set GetChartRange = Range(Temp) Case "VALUES" Temp = Mid(Sf, Commas(2) + 1, Commas(3) - Commas(2) - 1) Set GetChartRange = Range(Temp) End Select End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

John

Thanks!

Mukesh

Thank You

sample data.xlsx

So far i was doing it using one data table :

PU TYPE 20120907 20120914 20120921 20121005 20121012 20121019 ESMOT-M2VA-071 LOAD 2054 330 1886 1046 ESMOT-M2VA-071 CONFIRM 1500 1500 1500 1046 ESMOT-M2VA-071 PU ATP 1500 1500 1500 1500 1500 1500 ESMOT-JA1 LOAD ESMOT-JA1 CONFIRM ESMOT-JA1 PU ATP

As You can see i have three rows for each product containing different data and it works but its a nightmare if i want to add another value to compare, i would have to insert rows below each three rows of one product, and lets say its 1000 product types. so is there a way to create a pivot chart from three data tables (load,confirm,pu atp).

I can't do it now, I'm using Excel 2010.

Thanks,

Dave

VB:Range("C8").Select ActiveCell.FormulaR1C1 = "=(R[-3]C2)*2-R[-6]C2" Range("C8").Select Selection.AutoFill Destination:=Range("C8:C12"), Type:=xlFillDefault Range("C8:C12").Select Range("D4").Select ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[-2]C[-2]:RC[-2]),3)" Range("D4").Select Selection.AutoFill Destination:=Range("D4:D12"), Type:=xlFillDefault Range("D4:D12").Select Range("E6").Select ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[-4]C[-3]:RC[-3]),5)" Range("E6").Select Selection.AutoFill Destination:=Range("E6:E12"), Type:=xlFillDefault Range("E6:E12").Select Range("F10").Select ActiveCell.FormulaR1C1 = "=IF(RC[-4]

I am trying to create a stacked bar-of-bar chart, where two series in the primary stacked bar chart have more detailed information in another stacked bar chart. The following image should describe what I'm trying to do. ( the basic data I'm trying to graph is a bunch of "summary" costs [the primary stacked bar], which in-turn has more supporting cost data that I'd like to represent in another stacked bar chart)

stacked_bar_reqd.jpg

Any help would be greatly appreciated.

Thanks,

K

No luck finding an answer? You could always try Google.