# Two variable data table - both variables in rows

1. ## Two variable data table - both variables in rows

Hi,

I'm interested in creating a two variable data table with the two variables in the top two rows.

Is this possible?

I'm aware that two variable data tables are usually done in an array with variables in the top row and leftmost column. However, as there are multiple outputs to the variations that I want to make it makes sense to have the variations in the two top rows.

Regards
Kbka  Register To Reply

2. ## Re: Two variable data table - both variables in rows

Can you attach a workbook, containing an example of your source data, and what your expected outcome would look like?  Register To Reply

3. ## Re: Two variable data table - both variables in rows

Hi again,

I attached an example workbook. I would like to insert a data table to fill out C6:G10 with equations B6:B10, where the numbers in the two top rows (B4:G5) are used in cells L4 and L5, respectively.

At the moment I can only use one row for row input cell.

regards,
Kbka  Register To Reply

4. ## Re: Two variable data table - both variables in rows

Replace the formula in B6 with:

Formula:  `Please Login or Register  to view this content.`

Copy across to C6:G6

You can follow the same principle for the varying formulae in rows 7:10.  Register To Reply

5. ## Re: Two variable data table - both variables in rows Originally Posted by OllyXLS Replace the formula in B6 with:

Formula:  `Please Login or Register  to view this content.`

Copy across to C6:G6

You can follow the same principle for the varying formulae in rows 7:10.

lol... The attached is a very simplified workbook.

In the workbook where I need to use the DataTable, the inputs (Amount and price change) affect the final result after multiple steps of calculations and iterations.
Simply writing the formula is not an option.  Register To Reply

6. ## Re: Two variable data table - both variables in rows

Okay - I'm good at decoding, simplifying and automating multiple steps of calculation and iteration.

I'm NOT so good at mind reading.

I can only work with what you give me...

So, either you show me what it is you want, or I can't help.  Register To Reply

7. ## Re: Two variable data table - both variables in rows

I will agree with OllyXLS -- if you want specific help with the specific algorithm you are working with, you have to give enough details about that algorithm for us to translate the algorithm into worksheet functions.

That said, questions about the algorithm are not very often about Excel -- we would have the same problem if we were talking about programming this in VB or C or Java or [insert favorite programming language here].

In a generic, "how would you structure a spreadsheet to do this", here are some of my thoughts.
I recall years ago, in my first programming classes in high school, one of the first principles they taught us was that all programming in whatever language has three basic steps: input, processing, output. It often seems to me that, in spreadsheet programming in particular, we want to combine the "processing" and "output" steps into a single cell. I personally find that spreadsheet programming is a lot easier if I separate processing from output. So, in a case like this, I would probably use however many of the 1E6 rows beneath each variation that I needed to perform the desired calculation. Then, the result could be brought back up to B6:G6. Using OllyXLS's simple solution to the problem in your example spreadsheet, I've shown how I would do this in a spreadsheet. Granted, the proposed formulas are simple enough to make this structure overkill, it would be the kind of approach I would use for an algorithm that requires "multiple steps of calculations and iterations." and for cases where "simply writing a [single cell] formula is not an option."

Even though it is very generic, I hope it helps. If you need specific help for the specific algorithm you need to use for this, explain the algorithm, or provide an example of the algorithm, and we should be able to help you program that algorithm into Excel.  Register To Reply

8. ## Re: Two variable data table - both variables in rows

Some general thoughts for post #1 ...
From my recall, those are the constraints with using the 2 var data table. For multiple pairs of the 2 var, you could do it up for one pair nicely in a sheet, then make copies of that entire sheet as needed, and input the other pairs into each copy. Then re-gather all results into a summary sheet if need. As recalcs are v.likely intensive, do set the calc mode of the file to manual before you begin
-------------------------------
Any worth? Wave it, whack the little star at the bottom left of my responses  Register To Reply

9. ## Re: Two variable data table - both variables in rows

Hi Max,

I was hoping that there was a way to make the datatable function slightly more flexible. Perhaps not.

Regards,
Kbka  Register To Reply