On Thu, Jul 31, 2008 at 7:50 AM, Brian Barker <[EMAIL PROTECTED]> wrote:
> At 07:08 31/07/2008 -0700, Mark Knecht wrote:
>>
>> I have two tables of data, both arranged vertically. I need to create a
>> matrix of data mapping all combinations of the difference between one column
>> in in the first table against a similar column in the second table. I.e. -
>> two tables of 100 entries each will create an array with 10,000 data points.
>> (100x100 square)
>>
>> Is there any straight forward way in OO to build this matrix?
>
> I think so.
>
> It is probably easier - though not necessary - to have the two existing sets
> of values across the top and down the side of your new table.
> o Copy one array into the left column of your new table, e.g. A2 to A101.
> o Copy your other array into the top row of your new table, e.g. B1 to CW1.
> To do this, copy the column, select the new B1, use Edit | Paste Special...
> (or right-click | Paste Special..., or Ctrl+Shift+V), and tick the Transpose
> box under Options.
> o In B2, construct the formula =B$1-$A2 (or =$A2-B$1 if you prefer).
> o Drag the fill handle to copy this formula down column B from B2 to B101.
> o *With the resulting column still selected*, drag the result across all
> rows at once from column B to column CW.
>
> You can equally drag across row 2 and then down if you prefer, of course.
>
> I trust this helps.
>
> Brian Barker
>
Brian,
Thanks for the pointer to the transpose feature. That's certainly
part of the solution. I appreciate it.
My problem seems to be that your instructions work great *if*, and
this is important at my limited level of spreadsheet expertise, the
data table is just numbers. Everything worked great following your
instructions. Thanks.
Unfortunately in my case it seems more difficult. First, the
workbook is many pages. The first spreadsheet receives a large amount
of data from an external source. The data page can be updated at any
time. On a second page I have logic that uses some inputs I make in
certain cells which then drive a bunch of indirect function calls to
the data spreadsheet to get the values I'm interested in copied to the
second page.
The problem seems to be that when I copy and transpose this table
of indirect function calls the pasted version offsets the values in
the calls. As an example, if the cells A64:A66 contain
=INDIRECT("Data!b"&X64)
=IF(INDIRECT("Data!b"&X65)<A64,99999,INDIRECT("Data!b"&X65))
=IF(INDIRECT("Data!b"&X66)<A65,99999,INDIRECT("Data!b"&X66))
where 'Data' is the name of the data spreadsheet page and X64 contains
the offset into that data I'm interested in, then when I copy and
paste this for the transpose into cell AA64:AC64 I get
=INDIRECT("Data!b"&AA87)
=IF(INDIRECT("Data!b"&AB87)<AA64,99999,INDIRECT("Data!b"&AB87))
=IF(INDIRECT("Data!b"&AC87)<AB64,99999,INDIRECT("Data!b"&AC87))
the problem being that the transpose doesn't correctly transpose the
values inside the equations. Essentially the X64/X65/X66 references
should have been rotated to something like AA84/AA85/AA86 instead of
the AB87/AC87 references.
In my case, since the values in the X column remain valid for the
rotated version I could even accept that these values weren't changed
at all, but unfortunately it's a huge amount of work to go back and
make every entry $X$64, $X$65, etc. I'll do it if I have to but I'd
rather not.
Any ideas how I get around this?
If this is too complicated to do in words I'll post a small
spreadsheet as an example.
Thanks,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
