Kelvin Stott
Active Member
- Joined
- Oct 26, 2010
- Messages
- 338
Hi,
I would like to create a macro to define a new Excel function as follows:
=WHATIF(output_ref, input_ref, input_value)
The function would calculate the result of a given output cell on changing a specific input cell to an alternative value, for example:
A1 = 10
A2 = 20
A3 = A1+A2 = 30
A4 = WHATIF(A3,A1,5) = 25
In other words, the "WHATIF" function would calculate the result of the formula in output_cell in the scenario where input_cell is changed to input_value.
Does anybody know if this function already exists, or can anybody suggest suitable VBA code to define it?
Unfortunately none of the built-in tools (goal seek, data tables, scenarios, etc.) do the job because I would like to copy this "WHATIF" formula (with or without $ anchors) over a large array of cells.
Any help would be very much appreciated, thanks!
Kind regards,
Kelvin <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
I would like to create a macro to define a new Excel function as follows:
=WHATIF(output_ref, input_ref, input_value)
The function would calculate the result of a given output cell on changing a specific input cell to an alternative value, for example:
A1 = 10
A2 = 20
A3 = A1+A2 = 30
A4 = WHATIF(A3,A1,5) = 25
In other words, the "WHATIF" function would calculate the result of the formula in output_cell in the scenario where input_cell is changed to input_value.
Does anybody know if this function already exists, or can anybody suggest suitable VBA code to define it?
Unfortunately none of the built-in tools (goal seek, data tables, scenarios, etc.) do the job because I would like to copy this "WHATIF" formula (with or without $ anchors) over a large array of cells.
Any help would be very much appreciated, thanks!
Kind regards,
Kelvin <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
<!-- controls -->
