New "WHATIF" scenario function / macro

[Kelvin Stott]

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

<!-- controls -->

 

[Kelvin Stott]

I'm still struggling with this, but had a thought which might help:

Would it be possible to define the function to automatically refresh/recalculate the entire sheet and output cell after replacing an indirect input reference with a given value? However it must somehow do this in the background without actually changing the original input value.

I consider myself quite strong in Excel, but I don't know any VBA (I usually try to avoid macros altogether) and I don't see any other way. It would be one very powerful function indeed, if it can be done...

 

[Andrew Poulsom]

A function can only return a value. It can't change the contents of a cell (other than the one that calls it).

 

[Kelvin Stott]

How about if the function defines some kind of macro that does this in the background?

 

[Andrew Poulsom]

Nope. Same as I said in post #12.

 

[Kelvin Stott]

OK, thanks.

In the meantime, I think I've come up with a workaround, by creating a table where the column input variable points to different input cell references (which already includes each substitution), rather than alternative substitution values for a single input cell.

I'll post an update on the result...

In the meantime, thanks for all your help. At the very least you saved me a lot of time by making it clear what a function can and can't do.

Cheers,
Kelvin

 

[Kelvin Stott]

It worked!