+ Reply to Thread
Results 1 to 3 of 3

When copying Excel formulas, can you swap around the way Excel treats columns & rows?

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    9

    When copying Excel formulas, can you swap around the way Excel treats columns & rows?

    Confessedly the above title is a bit vague. This problem likely has a simple solution, but it's a bit difficult to explain in a sentence. To elaborate...

    Essentially, for some work recently, I was confronted with something akin to the following table, ready-made, with which to conduct data analysis:

    Data Table T Test - Copy.xlsx

    On the face of it a very simple task to simply do T tests and averages for all the values in Tables 2 and 3 and copy them into table 1. A handy formula, easily copied into all the requisite rows, should have enabled me to do this quite quickly.

    Unfortunately, whoever made the three tables I was presented with had helpfully failed to appreciate that when copying formulas down (unless I use the $ sign, of course), Excel changes the rows in the formula; whilst when copying formulas across, Excel changes the columns. This posed a challenge because tables 2-3 are organised in the opposite way to table 1.

    So I'm looking for a simple way to reverse this tendency of Excel? I did try to search for solutions online at the time, but sadly I could find none which I (as a beginner user!) could understand clearly enough to actually apply to the task at hand!

    This is a non-urgent issue, but any help would be much-appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: When copying Excel formulas, can you swap around the way Excel treats columns & rows?

    There are two common methods used, the OFFSET and INDEX/MATCH. I prefer INDEX/MATCH

    So, for example, you want C20:G20 transposed into C4:C8.

    In C4, copied down

    =INDEX($C$20:$G$20, ROWS($A$1:$A1))
    Rows counts the number of rows in that range (ROWS(A1:A1) = 1) which increases by 1 as you drag it down.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    11-13-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: When copying Excel formulas, can you swap around the way Excel treats columns & rows?

    Thanks for this - much-appreciated. Having read a bit more around the issue, I now understand these functions and they can solve a lot of my issues!

    However, unfortunately it doesn't entirely address everything. You might be better able to understand the problem if you look at the attached spreadsheet, which is an updated version of the earlier one:

    Data Table T Test - Updated.xlsx

    As you can see, I've updated all the columns I can using your rule. The averages before and average after - both for all participants, participant type 1 and participant type 2 - have been filled in with the formula.

    But I encounter a problem when I try to conduct a T-Test. This is because a T-test, to return an accurate value, needs to know how many participants you are working with for each value. This means when telling Excel to calculate the T-test you have to drag and select the exact data array you want.*

    I'm thus working with several arrays of data and need to alter the INDEX / ROWS function so that it selects several arrays of data and works its way along all columns of the table. You had no reason to know this so perhaps you assumed I could just work off the same averages as before - to the best of my knowledge (someone correct me if I'm wrong), you can't really do this with an Excel T-Test.

    So the question I'm asking is: how do you alter the INDEX / ROWS function so they select several arrays of data, rather than just a single data point? It's clear enough that I need to fill in the column_num section for the INDEX function rather than just row_num, but I still can't get my head around using the function to read multiple arrays of data (which may not be possible). My best guess is in the attached spreadsheet. I also need T-Tests not just for all participants, but Participant Type 1 and Type 2 as well - this means working the function around these issues as well.

    Hope that makes sense.

    * Granted, excel does also have a data analysis tool pak which allows you to do the T-test without using an Excel function, and you can type the number of participants and the averages into a text box. This is of course great when doing one T-test for a single study, but it's not helpful when - like with me - you're being asked to do a load of separate T-Tests.

+ 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. [SOLVED] Copying formulas across 351 columns in excel
    By 10999989 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2013, 03:47 AM
  2. How to swap rows/columns??
    By Ima in forum Excel General
    Replies: 1
    Last Post: 09-22-2009, 11:02 PM
  3. Excel should let you select 2 rows, right click, and SWAP them
    By DG_Crewdson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2006, 02:15 PM
  4. [SOLVED] How to swap rows and columns?
    By no@spam.com in forum Excel General
    Replies: 5
    Last Post: 09-21-2005, 04:05 AM
  5. Counting Rows/Columns for Copying Formulas
    By SamDev in forum Excel General
    Replies: 0
    Last Post: 06-24-2005, 12:05 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