+ Reply to Thread
Results 1 to 5 of 5

Reference Input Excel Multiple Regression

  1. #1
    Registered User
    Join Date
    11-28-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    2

    Reference Input Excel Multiple Regression

    Hello everyone,

    I am currently having issues doing a multiple regression in Excel, the only issue I have is that the independent variable (X) columns are not next to each other. For example, in the "Data" -> "Data Analysis" -> "Regression" screen:

    Input Y range: $B$150:$B$174
    Input X range: $D$150:$F$174

    This will yield a satisfactory result, the regression is successful without errors.

    However, in my case the X columns are not next to each other, so I have to alter the input in the "Regression" screen:

    Input Y range: $B$150:$B$174
    Input X range: $D$150:$D$174+$E$150:$E$174+$F$150:$F$174

    When I use this reference, Excel claims "the reference you typed is not valid...".

    What is the correct way to refer to X variables in a regression in Excel which are not next to each other in a table?
    Attached Files Attached Files
    Last edited by m1e; 11-28-2021 at 05:23 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Reference Input Excel Multiple Regression

    Hello m1e. Welcome to the forum.

    In order to get a clearer ideal of what you are working with it is almost always beneficial to upload a small sample workbook that clearly exemplifies both what you are working with and what you expect the outputs to be.
    See the instructions in the 'gold' banner at the top of this page. HOW TO ATTACH YOUR SAMPLE WORKBOOK:
    Dave

  3. #3
    Registered User
    Join Date
    11-28-2021
    Location
    Europe
    MS-Off Ver
    365
    Posts
    2

    Re: Reference Input Excel Multiple Regression

    I have added an example file in the OP

  4. #4
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: Reference Input Excel Multiple Regression

    Well, the tool gives you (at least me) an explicit message that the range must be a contiguous reference.
    So what you want to do is not possible to do this way.

    1.) Why is rearranging your data not an option rather than trying to have formulas/functions cope with badly structured data?
    2.) All the values from the regressions analysis are available as worksheet functions where you may be able to pass non-contiguous ranges to the function.

    Is either an option?

  5. #5
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    472

    Re: Reference Input Excel Multiple Regression

    To illustrate what I mean by 2.), e.g. for the coefficients:

    =LINEST(A35:A59,CHOOSE({1,2,3},C37:C61,E42:E66,G34:G58))

+ 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. input of variables for excel regression
    By marina1 in forum Excel General
    Replies: 2
    Last Post: 11-15-2016, 08:26 AM
  2. Multiple Regression in Excel
    By nandhamnk in forum Excel General
    Replies: 2
    Last Post: 07-27-2009, 09:57 AM
  3. Multiple Regression in Excel 2007
    By jerseyguy1996 in forum Excel General
    Replies: 1
    Last Post: 03-25-2009, 01:18 AM
  4. regression input data
    By Teresa in forum Excel General
    Replies: 1
    Last Post: 07-25-2006, 01:45 PM
  5. Multiple Regression in Excel 2002 SP3 (Office XP)?
    By Mike Middleton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 10:05 PM
  6. [SOLVED] Multiple Regression in Excel 2002 SP3 (Office XP)?
    By KA in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  7. Multiple Regression in Excel 2002 SP3 (Office XP)?
    By Mike Middleton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 08:05 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