+ Reply to Thread
Results 1 to 9 of 9

Two variable data table - both variables in rows

  1. #1
    Registered User
    Join Date
    06-28-2011
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010, Excel 2011 for mac
    Posts
    82

    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

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    06-28-2011
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010, Excel 2011 for mac
    Posts
    82

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

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Two variable data table - both variables in rows

    Replace the formula in B6 with:

    Formula: copy to clipboard
    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.

  5. #5
    Registered User
    Join Date
    06-28-2011
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010, Excel 2011 for mac
    Posts
    82

    Re: Two variable data table - both variables in rows

    Quote Originally Posted by OllyXLS View Post
    Replace the formula in B6 with:

    Formula: copy to clipboard
    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.

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,483

    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.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    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

  9. #9
    Registered User
    Join Date
    06-28-2011
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010, Excel 2011 for mac
    Posts
    82

    Re: Two variable data table - both variables in rows

    Hi Max,

    Thank you. That would work and had thought about it.

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

    Regards,
    Kbka

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. selecting a table with variable rows and columns
    By prjames3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 08:59 PM
  2. PLEASE HELP!!!Variables, Variable everyone loves variables!
    By THEEXCELGEEK in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-28-2013, 02:20 PM
  3. How to format a table with variable rows using a macro
    By peteros in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2012, 11:21 AM
  4. Replies: 3
    Last Post: 09-11-2012, 02:03 AM
  5. Create Table using Variable Column and Rows
    By Craxuan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2011, 10:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1