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

 

[Andrew Poulsom]

Welcome to the Board.

Try this UDF:

Function WHATIF(output_ref, input_ref, input_value)
    WHATIF = Evaluate(Replace(output_ref.Formula, input_ref.Value, input_value))
End Function

 

[Kelvin Stott]

Welcome to the Board.

Try this UDF:

Function WHATIF(output_ref, input_ref, input_value)
    WHATIF = Evaluate(Replace(output_ref.Formula, input_ref.Value, input_value))
End Function

Thank you so much, that was very quick indeed!

It looks very nice and simple, I'll give it a try and post an update...

Cheers,
Kelvin

 

[Kelvin Stott]

Hmmm, it seems to give very strange results when I apply it to the example in my original post above

 

[Andrew Poulsom]

I see why:

Function WHATIF(output_ref As Range, input_ref As Range, input_value)
    WHATIF = Evaluate(Replace(Application.ConvertFormula(output_ref.Formula, xlA1, xlA1, xlRelative), input_ref.Address(False, False), input_value))
End Function

 

[Kelvin Stott]

WOW, perfect - you're a genius, thanks!

 

[Kelvin Stott]

Oh no, it doesn't appear to work with more complex formulae involving chains of more than one simple calculation, for example:

A1 = 10
A2 = 20
A3 = A1+A2 = 30
A4 = A3-A2 = 10

A5 = WHATIF(A4,A1,5) = 5

For some reason the code you gave only works when input_ref is a direct input, but not when it's an indirect input.

Any idea how to fix this?

Thanks again, Kelvin

 

[Kelvin Stott]

Also, it works with the following (note the comma in SUM function):

A1 = 10
A2 = 20
A3 = SUM(A1,A2) = 30
A4 = WHATIF(A3,A1,5) = 25

But strangely not with the following (note the colon in SUM function):

A1 = 10
A2 = 20
A3 = SUM(A1:A2) = 30
A4 = WHATIF(A3,A1,5) = 25

 

[Andrew Poulsom]

Sorry, the function only works with direct references because it simply replaces the reference with a value and evaluates the formula. You can't have:

=SUM(5:A2)

 

[Kelvin Stott]

Understood, thanks.

Is there any workaround, or alternative solution to this problem?

Essentially I need this function to test the impact of changing each one of 140 different inputs (independently, in turn) on the final output (which is calculated through about 1,000 lines of progressive calculations). This means repeating the entire cascade of calculations with an alternative given value substituted into each input.

I can't think how to do this without creating such a function.