+ Reply to Thread
Results 1 to 7 of 7

Nonparametric Pairwise Difference function in Excel

Hybrid View

  1. #1
    Registered User
    Join Date
    10-05-2019
    Location
    West Elmira NY
    MS-Off Ver
    Excel 16.30
    Posts
    3

    Nonparametric Pairwise Difference function in Excel

    I'm looking to subtract each value from one column from all those in another. This is a non-parametric test called a "pairwise difference" and it is native in Minitab. Unfortunately, MiniTab costs $2,000, which is a bit stee for this one calculation. Does anyone know how to do this in Excel???

    Ex:

    8 1
    3 2
    6 10
    4 4
    6 1
    4 8
    2 9
    11 1
    13 20
    22 3


    I want to subract the first value (8) from all values in column 2, store them in column 3, then do the same thing with the second value in column 1 (3). I want it to stack the results in column 3, but I can do that manually.

    Any help is appreciated!

    ~Aaron C
    Last edited by theglassguy; 10-05-2019 at 10:33 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Nonparametric Pairwise Difference function in Excel

    I don't understand what you mean by "stack the results in column 3".

    Please attach an Excel sheet showing what you want and where you want to see it.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,457

    Re: Nonparametric Pairwise Difference function in Excel

    Easiest approach I know (ignoring the "stack in column 3" part) is to:
    1) Select column B and copy to the clipboard.
    2) Select B1 -> paste special -> transpose.
    3) Delete the extraneous values in column B
    4) Move column A down 1 row.
    5) In B2, enter =$A1-B$1 (note the mix of relative and absolute references).
    6) Copy B2 and paste/fill into B2:K11.

    From there, I would want to understand exactly how you are using this and how this fits into the rest of the project.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    10-05-2019
    Location
    West Elmira NY
    MS-Off Ver
    Excel 16.30
    Posts
    3

    Re: Nonparametric Pairwise Difference function in Excel

    By "Stack the results", I mean I want all the results sequentially in column 3.

    This is a simple example from MiniTab "pairwise differences" where the sequence of stacked results are 84-33;84-40;60-33;60-40 or what you see in the third column.If you're asking... why not just use MiniTab... it costs $2,000 and I'musing a free trial, which I need for longer than the trial period.


    2019-10-06_11-04-34.png

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Nonparametric Pairwise Difference function in Excel

    Assuming your data is in A1:B10, you can use this formula in C1:

    =INDEX(B:B,MOD(ROWS($1:1)-1,10)+1) - INDEX(A:A,INT((ROWS($1:1)-1)/10)+1)

    Then copy down as required (in this case, to row 100 as you have 10 * 10 calculations to do).

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    10-05-2019
    Location
    West Elmira NY
    MS-Off Ver
    Excel 16.30
    Posts
    3

    Re: Nonparametric Pairwise Difference function in Excel

    Thanks Pete. I think this will work, I'm playing around with it now.

    When they say "guru" you guys certainly fit the bill; I used to think I knew Excel well... I'm a noob compared to you folks! This is my new go-to Excel help site.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,426

    Re: Nonparametric Pairwise Difference function in Excel

    Thank you for the kind words, and for the rep.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Pete

+ 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. Excel function - find last difference in line
    By ZickZak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-19-2018, 08:30 AM
  2. difference between excel irr & xirr function
    By Jonno1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2018, 01:12 PM
  3. pairwise scatter plot
    By MikeSS in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-18-2016, 06:35 PM
  4. Replies: 1
    Last Post: 01-08-2016, 08:58 AM
  5. Macro for calculating pairwise rolling correlations
    By TrueTears in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2013, 06:53 AM
  6. [SOLVED] Excel function to find difference of timestamps in milliseconds?
    By Srikanth in forum Excel General
    Replies: 1
    Last Post: 07-06-2006, 03:10 AM
  7. Difference in Values for Price function in Excel
    By abhi_23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2006, 02:51 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