+ Reply to Thread
Results 1 to 4 of 4

Extracting Matrix Values *Off*-Diagonal

  1. #1
    Registered User
    Join Date
    06-14-2015
    Location
    Reno, Nevada, USA
    MS-Off Ver
    Current
    Posts
    3

    Extracting Matrix Values *Off*-Diagonal

    Hello Everyone,

    I'm an electrophysiologist not an IT guru so you'll have to forgive the task I'm about to beg your help for:

    So I shock my brain cell once then I'll shock it again after a 0.1ms delay. Then I'll do that again, but the delay will increase by another 0.1ms delay.
    Here's my problem: I'm collecting data every 0.02ms, which is faster than the delays. The result is that the excel sheets come out to be oblong matrices.

    For instance, If I have the following array of data, I'm only interested in the cells I've highlighted in red text.
    0 -140 -140 -140
    0 -140 -140 -140
    0 -140 -140 -140
    0 -140 -140 -140
    0 -140 -140 -140
    0 0 -140 -140
    0 0 -140 -140
    0 0 -140 -140
    0 0 -140 -140
    0 0 -140 -140
    0 0 -140 -140
    0 0 0 -140
    0 0 0 -140
    0 0 0 -140
    0 0 0 -140
    0 0 0 -140
    0 0 0 0

    Up until now, I've tried =index(A1:D1,,rows($1:1) which extracts the value of the square matrix about the diagonal of (A+1)(1+1). What I'm looking for is a formula to extract (A+1)(1+5). I would do this by hand if I had a few lines of data but I'm working with an array of 260K Cells right now so I'm desperate!

    I appreciate your help!

    -Bobby

  2. #2
    Registered User
    Join Date
    06-14-2015
    Location
    Reno, Nevada, USA
    MS-Off Ver
    Current
    Posts
    3

    Re: Extracting Matrix Values *Off*-Diagonal

    So I found a solution, however, I know there has to be something more convenient.
    What I did was make an array of row numbers and column numbers that correlate to my data points of interests.
    5 10 15 20 (10=Cell 1+5 dragged over)
    1 2 3 4 (2=Cell 1+1 dragged over)
    =index($A$1:$D$20,select cell with 5, select cell with 1), then drag that over.
    That gives you the number, which then needs to be copied and pasted as a value, transposed, and correlated to the point in time at which it occurred to make an effective graph. Again, there has to be something simpler than that.

    Much appreciated, -bobby

  3. #3
    Registered User
    Join Date
    06-14-2015
    Location
    Reno, Nevada, USA
    MS-Off Ver
    Current
    Posts
    3

    Re: Extracting Matrix Values *Off*-Diagonal

    So I found a solution, however, I know there has to be something more convenient.
    What I did was make an array of row numbers and column numbers that correlate to my data points of interests.
    5 10 15 20 (10=Cell 1+5 dragged over)
    1 2 3 4 (2=Cell 1+1 dragged over)
    =index($A$1:$D$20,select cell with 5, select cell with 1), then drag that over.
    That gives you the number, which then needs to be copied and pasted as a value, transposed, and correlated to the point in time at which it occurred to make an effective graph. Again, there has to be something simpler than that.

    Much appreciated, -bobby

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extracting Matrix Values *Off*-Diagonal

    Welcome to the forum

    Often, a copy/paste to a post, doesnt quite come out the way you planned - this may be 1 of those times

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. How do I get the diagonal of a matrix from excel?
    By JoeNiner in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-27-2017, 02:04 AM
  2. Diagonal Matrix Repeat value
    By smurts in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-15-2014, 12:57 PM
  3. Diagonal Matrix Fill
    By don.harvey in forum Excel General
    Replies: 5
    Last Post: 09-05-2009, 03:24 AM
  4. copy matrix (diagonal)
    By Mut in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2006, 09:52 PM
  5. [SOLVED] show a set of diagonal values in a matrix as a set of consecutive.
    By Kelvin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2005, 09:06 AM

Tags for this Thread

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