Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sensitivity Chart

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


Post your answer or comment

comments powered by Disqus
Sorry for the length of this...
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


I need to create a sensititivity table (tables). I am at a loss. I know you can create one using the table function in excel (data-->table).

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%.

Assignment will be due soon but you do not have an easy way to finish it?
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

Assignment will be due soon but you do not have an easy way to finish it?
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

I'm not sure if the title on this post is quite there - Dave, if you've got a better way of expressing this, please feel free to change the title.

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


Gentlemen (and gentlewomen),

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

I’m going to give as much detail here as possible in hopes that a solution to my current dilemma can be found. Please note that while I am quite versed in other areas of the computer, I am an Excel novice (in fact, I've always done everything in my power to avoid Excel at all costs – it just doesn't make sense to me).

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!

Hey everyone,
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:
	
 
Sub 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is appreciated

THX!

I have a worksheet calcuating some results driven by different parameters.
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!

I have a Pivot chart that is very sensitive. When mutually exclusive variables are selected in any of the 4 visible drop down filters, it crashes. I understand why it does this, I'm just wondering how to prevent it. When you do this mismatch in a pivot table, the data field just goes blank, but it does not crash and reboot/recover file.

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.

I have created an excel chart with a spinner which allows me to look at the
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

Hi, I'm using Excel 2007.

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 Sub
Now, 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.

Hope everyone is well. I turn to your expertise for assistance with the following two items - any and all help will be greatly appreciated.

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

I have a file which I have attached and I have been (for a week) trying to see how to calculate (1) price sensitivity and price elasticity when a price is increased by a certain percentage.
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.

I have a file which I have attached and I have been (for a week) trying to see how to calculate (1) price sensitivity and price elasticity when a price is increased by a certain percentage.
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

Hi,
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!

Hi geniuses,

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

Hello,
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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

John

I do have a time series data for such as company's share price, volume, P/E ratio for 3/4 companies. i want to build chart for the same and update frequently. I want to built the macro for the same. How can I do it. I haven't used macro before. Please help!
Thanks!
Mukesh

I have been trying to figure out how to properly do offset match and for the life of me I can't get it to work. I have a named range that was created out of a pivot table that continually changes size. I am trying to search the top row for a name then go down x number of rows and over 1 column. For all the ones I didn't need to go over 1 column I just used Hlookup() but now I have to go over 1 column and Hlookup wont work. Due to the sensitive nature of the data I attached a sample of book that just illustrates what is going on. In the data attached the lookup value would have been (blank) but I want to return the value under the column to the right of it and I can't change the pivot chart because the data is a certain way. If anyone can help with this issue that would be great.

Thank You

sample data.xlsx

Hi, I need to create a Pivot Chart using data from 3 or more Data tables, data in tables contain different types of values for the same type of product. In example one data contains consumption of products for each week, another data table contains available production abilities for each week. i need to compare the values in a pivot chart.
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.

I do not have a file example. I've created a tool that includes a custom menu, based largely on Dave Hawley's instruction ( http://www.ozgrid.com/VBA/custom-menus.htm ) and John Walkenbach's website ( http://spreadsheetpage.com/index.php/file/menu_maker/ ). The menu is created in a menu bar item called "Add-Ins." That's fine. However, that menu item is not visible when looking at a chart page in the work book. Is there any way to make it visible whether one is looking at a sheet tab or a chart tab?

Thanks,

Dave

plse add code so as to paste 3 o 5 or both into the chart

	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]

Hi,

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.