Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
I need a macro to vary my sales forecast (which are values contained in a
range named "Forecast") by the factor specified in Cell A1. I've written this macro:- Sub Vary() Dim cell As Range For Each cell In Range("Forecast") cell.Value = cell.Value * Range("A1") Next End Sub Although this macro does vary the forecasts by the factor specified in cell A1, the results are all values. I need each cell within the range "Forecast" to actually contain a reference to cell A1 (i.e. *A1). In future, I can vary the forecast by just changing the figure in cell A1 without running any macro. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
Hi
change the line cell.Value = cell.Value * Range("A1") to cell.formula = "=$A$1*" & cell.value -- Regards Frank Kabel Frankfurt, Germany RK wrote: I need a macro to vary my sales forecast (which are values contained in a range named "Forecast") by the factor specified in Cell A1. I've written this macro:- Sub Vary() Dim cell As Range For Each cell In Range("Forecast") cell.Value = cell.Value * Range("A1") Next End Sub Although this macro does vary the forecasts by the factor specified in cell A1, the results are all values. I need each cell within the range "Forecast" to actually contain a reference to cell A1 (i.e. *A1). In future, I can vary the forecast by just changing the figure in cell A1 without running any macro. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
Forget the macro.
With your data in cell A1, in any other blank cell, put =$A$1 Now copy that cell, select your Forecast range and do Edit / Paste Special / Multiply. They will all now contain a reference that looks like *($A$1). Varying the value in A1 will trundle on through to every cell in your range. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "RK" wrote in message ... I need a macro to vary my sales forecast (which are values contained in a range named "Forecast") by the factor specified in Cell A1. I've written this macro:- Sub Vary() Dim cell As Range For Each cell In Range("Forecast") cell.Value = cell.Value * Range("A1") Next End Sub Although this macro does vary the forecasts by the factor specified in cell A1, the results are all values. I need each cell within the range "Forecast" to actually contain a reference to cell A1 (i.e. *A1). In future, I can vary the forecast by just changing the figure in cell A1 without running any macro. Thanks! --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
Ken Wright wrote:
Forget the macro. With your data in cell A1, in any other blank cell, put =$A$1 Now copy that cell, select your Forecast range and do Edit / Paste Special / Multiply. They will all now contain a reference that looks like *($A$1). Varying the value in A1 will trundle on through to every cell in your range. Ken nice one :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
Thanks Frank/Ken! I was actually using a method without macros (but a much
more arduous method than that suggested by Ken). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sensitivity Analysis
LOL - I did it by accident one day, screwing up what I had really intended to
do, and thought what the hell have I done - then figured I could probably use it. Have found it quite useful a number of times since, and usually for exactly this kind of scenario. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Frank Kabel" wrote in message ... Ken Wright wrote: Forget the macro. With your data in cell A1, in any other blank cell, put =$A$1 Now copy that cell, select your Forecast range and do Edit / Paste Special / Multiply. They will all now contain a reference that looks like *($A$1). Varying the value in A1 will trundle on through to every cell in your range. Ken nice one :-) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.613 / Virus Database: 392 - Release Date: 04/03/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sensitivity analysis | New Users to Excel | |||
What do I need to do to perform a sensitivity analysis | Excel Worksheet Functions | |||
Excel Sensitivity (What If) analysis | Charts and Charting in Excel | |||
Sensitivity Analysis to 2 variables | Excel Discussion (Misc queries) | |||
Sensitivity analysis | Excel Worksheet Functions |