New "WHATIF" scenario macro / function

**Kelvin Stott** · 10-26-2010, 11:51 AM

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 and/or without $ anchors) over a large array of cells.

Any help would be very much appreciated, thanks!

Kind regards,
Kelvin

**TMS** · 10-26-2010, 12:07 PM

I think that's probably Tools | Goal Seek...

Regards

**Kelvin Stott** · 10-26-2010, 12:11 PM

Originally Posted by TMShucks

I think that's probably Tools | Goal Seek...

Thanks, but I looked at that. I need to define an explicit function that I can write in a cell and copy, but goalseek is a tool, not a function. Also, goalseek determines the input for a given output, but I need to determine the output for a given input (without changing the value in the input cell).

**Kelvin Stott** · 10-26-2010, 01:22 PM

Never mind, I just received the answer in another forum:

http://www.mrexcel.com/forum/showthread.php?p=2489271

;-)

**romperstomper** · 10-26-2010, 03:20 PM

I suggest you read the rules before posting again. If you cross-post, you must post links to the other posts. (the same is true of most forums incidentally)

**Kelvin Stott** · 10-26-2010, 04:54 PM

I apologize, sorry. Looking back I agree it wasn't good etiquette, I'll review the rules more carefully (EDIT: done :-).

Regarding the problem, it seems the solution didn't quite work as intended, so the issue remains open, in need of a solution...

Sorry again for the cross-post.