What IF Analysis OR VBA for scenarios with large set of data ?

[dbaz80]

Ok first port,

i have about 10 variables (A1,B1,...,J1) that are given as input to a large table with about 200,000 cells (2,000x100).

After some calculations we have an output that is a table with 10,000 cells (200x50)

Now i need to test the above with hundreds of different sets of 10 variables (An,Bn,...,Jn).

I wonder what is the easiest (and most efficient) way to do that.

Is what if analysis suitable for that kind of data size? Maybe prefer a VBA function? Or Something else?

Thoughts?

 

[shg]

Welcome to the board.

Maybe you could share the nature of the calculation.

 

[dbaz80]

Thanks shg

The calculation itself is not something complicated (there is a bunch of numbers with simple math functions, min max, and some Index,Match to retrieve the headers of where the min,max happen)

What poses the most problems is the size of the whole thing, and i'm far from being an excel expert to have experienced something like that before.

My first thought was to use the what if analysis, but by searching its features, it seems it's made for far simpler problems with fewer variables.

 

[shg]

It can probably be done via formulas, and can certainly be done via VBA, and can perhaps be done via other methods. Which is faster or more efficient depends entirely on facts not in evidence.