| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining relationship between 3 variables
I have 3 variables: a 'real' rate named Rreal in cell D4, a
'nominal' rate named Rnominal in cell D7, and an inflation rate named Inflation in cell J7. The relationship between these variables should be as follows: ( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + ( Inflation / 1000 ) ) I would like a macro that automatically runs whenever a value changes in D4, D7 or F7, that maintains the relationship between the variables. That is, the nominal rate is recalculated automatically if, say, the inflation rate is changed, or the implied inflation rate changes if either the nominal rate or real rate is changed. Can anyone show me what the macro would look like? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Maintaining relationship between 3 variables
If, say, RNominal changes, how do know which of the other 2 stays constant
and which varies ? NickHK egroups.com... I have 3 variables: a 'real' rate named Rreal in cell D4, a 'nominal' rate named Rnominal in cell D7, and an inflation rate named Inflation in cell J7. The relationship between these variables should be as follows: ( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + ( Inflation / 1000 ) ) I would like a macro that automatically runs whenever a value changes in D4, D7 or F7, that maintains the relationship between the variables. That is, the nominal rate is recalculated automatically if, say, the inflation rate is changed, or the implied inflation rate changes if either the nominal rate or real rate is changed. Can anyone show me what the macro would look like? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Maintaining relationship between 3 variables
Thanks Nick
Good question, I didn't think of that (albeit obvious now you mention it). Perhaps a default rule could be applied? i.e. (a) if Rnominal changes, recalculate the Rreal for a given Inflation, (b) if Inflation changes, recalculate Rnominal for a given Rreal, and (c) if Rreal changes, recalculate Rnominal for a given Inflation. Does this help? Mike NickHK wrote: If, say, RNominal changes, how do know which of the other 2 stays constant and which varies ? NickHK egroups.com... I have 3 variables: a 'real' rate named Rreal in cell D4, a 'nominal' rate named Rnominal in cell D7, and an inflation rate named Inflation in cell J7. The relationship between these variables should be as follows: ( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + ( Inflation / 1000 ) ) I would like a macro that automatically runs whenever a value changes in D4, D7 or F7, that maintains the relationship between the variables. That is, the nominal rate is recalculated automatically if, say, the inflation rate is changed, or the implied inflation rate changes if either the nominal rate or real rate is changed. Can anyone show me what the macro would look like? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Maintaining relationship between 3 variables
Mike,
Here's one way : Name the 3 cells accordingly. Private Sub Worksheet_Change(ByVal Target As Range) '(1 + (Rnominal / 1000)) = (1 + (Rreal / 1000)) * (1 + (Inflation / 1000)) 'Avoid keep firing this change event Application.EnableEvents = False Select Case True Case Not Intersect(Target, Range("RNominal")) Is Nothing Range("RReal").Value = Range("RNominal").Value / Range("RInflation").Value Case Not Intersect(Target, Range("RInflation")) Is Nothing Range("RNominal").Value = Range("RInflation").Value / Range("RReal").Value Case Not Intersect(Target, Range("RReal")) Is Nothing Range("RNominal").Value = Range("RReal").Value / Range("RInflation").Value End Select 'Reset event Application.EnableEvents = True End Sub You should add error handling also. I have left implementing the correct formulae as an exercise for the reader. NickHK groups.com... Thanks Nick Good question, I didn't think of that (albeit obvious now you mention it). Perhaps a default rule could be applied? i.e. (a) if Rnominal changes, recalculate the Rreal for a given Inflation, (b) if Inflation changes, recalculate Rnominal for a given Rreal, and (c) if Rreal changes, recalculate Rnominal for a given Inflation. Does this help? Mike NickHK wrote: If, say, RNominal changes, how do know which of the other 2 stays constant and which varies ? NickHK egroups.com... I have 3 variables: a 'real' rate named Rreal in cell D4, a 'nominal' rate named Rnominal in cell D7, and an inflation rate named Inflation in cell J7. The relationship between these variables should be as follows: ( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + ( Inflation / 1000 ) ) I would like a macro that automatically runs whenever a value changes in D4, D7 or F7, that maintains the relationship between the variables. That is, the nominal rate is recalculated automatically if, say, the inflation rate is changed, or the implied inflation rate changes if either the nominal rate or real rate is changed. Can anyone show me what the macro would look like? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Maintaining relationship between 3 variables
Nick - many thanks for your pointers, I got it to work fine in the end
Mike NickHK wrote: Mike, Here's one way : Name the 3 cells accordingly. Private Sub Worksheet_Change(ByVal Target As Range) '(1 + (Rnominal / 1000)) = (1 + (Rreal / 1000)) * (1 + (Inflation / 1000)) 'Avoid keep firing this change event Application.EnableEvents = False Select Case True Case Not Intersect(Target, Range("RNominal")) Is Nothing Range("RReal").Value = Range("RNominal").Value / Range("RInflation").Value Case Not Intersect(Target, Range("RInflation")) Is Nothing Range("RNominal").Value = Range("RInflation").Value / Range("RReal").Value Case Not Intersect(Target, Range("RReal")) Is Nothing Range("RNominal").Value = Range("RReal").Value / Range("RInflation").Value End Select 'Reset event Application.EnableEvents = True End Sub You should add error handling also. I have left implementing the correct formulae as an exercise for the reader. NickHK groups.com... Thanks Nick Good question, I didn't think of that (albeit obvious now you mention it). Perhaps a default rule could be applied? i.e. (a) if Rnominal changes, recalculate the Rreal for a given Inflation, (b) if Inflation changes, recalculate Rnominal for a given Rreal, and (c) if Rreal changes, recalculate Rnominal for a given Inflation. Does this help? Mike NickHK wrote: If, say, RNominal changes, how do know which of the other 2 stays constant and which varies ? NickHK egroups.com... I have 3 variables: a 'real' rate named Rreal in cell D4, a 'nominal' rate named Rnominal in cell D7, and an inflation rate named Inflation in cell J7. The relationship between these variables should be as follows: ( 1 + ( Rnominal / 1000 ) ) = ( 1 + ( Rreal / 1000 ) ) * ( 1 + ( Inflation / 1000 ) ) I would like a macro that automatically runs whenever a value changes in D4, D7 or F7, that maintains the relationship between the variables. That is, the nominal rate is recalculated automatically if, say, the inflation rate is changed, or the implied inflation rate changes if either the nominal rate or real rate is changed. Can anyone show me what the macro would look like? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relationship between number | Excel Worksheet Functions | |||
| Look up functions and a one to many relationship | Excel Worksheet Functions | |||
| Graphing relationship between 3 variables | Charts and Charting in Excel | |||
| Relationship Math Across a Row | Excel Discussion (Misc queries) | |||
| Vlookup with "many to many" relationship? | Excel Worksheet Functions | |||
Hybrid Mode
