Trying to create an output grid/matrix with custom data.

You may be better off posting the spreadsheet instead of screenshots, so we can have a look at the "formula". Explain the process that you followed to arrive at the first row of values. If it can be described with words, it can be automated.

The first row of values were made by switching X to 1, copying the output, and pasting it at 1,1. Then I repeated by switching X to 2, copying the output, and pasting it at 2,1. etc. The "formula" is very large and complex taking up over 3,000 cells to compute. Granted it is poorly optimized. The graph in the center is merely a representation of the "formula" to make sure it's working correctly. I can't post the actual spreadsheet since the "formula" is proprietary.
Excel Help 2.jpg

From Excel Help 1, it looks to me like you have a function z=f(x,y) and you are trying to find z for many different values for x and y, and then plot them in a contour plot (z vs x at different values for y). In many ways, I find this layout to be very effective for this -- IF f can be reduced down to fit in a single cell. For something that currently requires 3000 cells to compute f, it does not seem likely that you will reduce it down to a single cell with Excel formulas.

I can't post the actual spreadsheet since the "formula" is proprietary.

As one who believes that it is easier (if not necessary) to understand the math before it can be put into Excel, it is going to be very difficult for us to help you in any specific way if we don't/can't know f. So you are on your own as far as f goes (but it sounds like you have a working even if it isn't optimized way of calculating f, so you must be capable of getting f).

I expect this is one of those cases where VBA will be required. To me it is mostly a question of how to best use VBA to get what you want. When I have a complex function f that I want to calculate many times over - I like to code a function procedure in VBA that will compute f. Then I can enter that function into a single cell (like any of Excel's built in functions) and get z. With a UDF, the table that you started in ExcelHelp1 will be easy to build -- simply enter the UDF with the appropriate relative and absolute references =myudf($A2,B$1) and copy it across the table and you will fill up the table. Again, the hard part of this kind of approach is that you will have to convert that 3000 cell spreadsheet into VBA, and we won't be able to help you since we can't know f. But, if this were my project, that's how I would do it.

To get you started, here is a simple UDF that calculates the plane Z=A+Bx+Cy

Please Login or Register  to view this content.

It would be called from a spreadsheet cell as =myudf(A1,A2,B1,B2,B3) where the appropriate values are in those references.

2D data table?

http://office.microsoft.com/en-us/ex...005199586.aspx