+ Reply to Thread
Results 1 to 10 of 10

LINEST - exclude x variables and empty rows

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Question LINEST - exclude x variables and empty rows

    Hi, all

    I'm trying to populate a regression model here for multiple parameters here hence, I believe VBA can make my life easier.

    1. Is it possible if the VBA to exclude the x variable(s) for LINEST() formula in cell range J3:M3 if the column(s) is/are empty?
    2. Is it possible if the VBA to eliminate empty rows or rows with "BAD" data?
    3. Is it possible if the VBA can populate the formula to each cell in column G (model) which has good data in instead of having the formula in every cell of the column?

    Hereby I attached my Excel file. Hope anyone can enlighten me. Thank you so much!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: LINEST - exclude x variables and empty rows

    Hi, anyone can help me with this?

    Thanks in advance!

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

    Re: LINEST - exclude x variables and empty rows

    Your "sample" is massive. 10-20 rows is usually enough. There are no blank rows, or I simply got bored looking for them. What is a "bad" row?
    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.

  4. #4
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: LINEST - exclude x variables and empty rows

    Hi, Glenn

    I was told that the more data I have, the more accurate the regression would be, and just to give an idea to all how much data would this VBA need to manage. Hence I gave an example of this much of data. There are blank rows at row 7066 as an example, and "BAD" data is rows that's showing "BAD".

    Thanks.

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

    Re: LINEST - exclude x variables and empty rows

    What is needed is a sample of your data, not thousands of rows of it. Your real data can be massive, but we don't need to see anything like it all. Thousands of rows make manual checking tedious/difficult/impossible.

  6. #6
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: LINEST - exclude x variables and empty rows

    Hi, Glenn

    If that's the case then, would gladly change. Here's the new attachment.

    Thanks.
    Attached Files Attached Files

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

    Re: LINEST - exclude x variables and empty rows

    Apologies.... but I didn't spot your revised attachment until a few seconds ago. I cut your sample down to a tiny handful of rows to establish the principle of working with 2 conditions (non-blank and non-"Bad").

    This array formula does work, insofar as it produces the same answer as your formula does on the same dataset, minus blanks and "Bad"s.

    I have no idea how it'll work on your entire dataset, but it's worth a try:

    Select 4 cells in a row, paste this and enter as an array:

    =LINEST(INDEX(D:D,N(IF(1,MODE.MULT(IF(A3:A10<>{"Bad",""},ROW(D3:D10)))))),INDEX(A:C,N(IF(1,MODE.MULT(IF(A3:A10<>{"Bad",""},ROW(D3:D10))))),N(IF(1,{1,2,3}))),TRUE,TRUE)

    Best to take a look at my sheet !!!
    Attached Files Attached Files

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

    Re: LINEST - exclude x variables and empty rows

    If it's too slow, someone here who knows VBA might be able to give you a VBA version. I can't 'cos I know nothing about VBA.

  9. #9
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: LINEST - exclude x variables and empty rows

    Hi, Glenn

    Unfortunately "BAD" data can happen anywhere in the known x's hence, could not use your formula but thanks.

    Anyone can give me a hand in this?

  10. #10
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: LINEST - exclude x variables and empty rows

    Hi, all

    A little update:
    I manage to solve number 2 and 3 of my problems.

    For the case of number 1, I believe using formula and functions might be able to be used hence, I would post there instead and mark this as solved. Thanks.

+ 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 to use variables to increment range of cell used in LINEST function
    By gloink in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-20-2017, 12:38 AM
  2. Linest maximum predictor variables
    By varat_pond in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2015, 07:57 AM
  3. Replies: 0
    Last Post: 02-13-2014, 11:42 AM
  4. Replies: 3
    Last Post: 09-11-2012, 02:03 AM
  5. LINEST for dummy variables?
    By BlueFortyTwo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2007, 10:00 PM
  6. Want to use trend() and linest() but some cells are empty, #N/A, o
    By ACcompressor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-02-2006, 07:55 PM
  7. [SOLVED] LINEST maximum number of predictor variables
    By Peter N in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-18-2005, 10: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