+ Reply to Thread
Results 1 to 10 of 10

Extract polynomial coeffients on filtered (hidden) data

  1. 04-28-2024, 10:49 AM #1
    Canadane
    Canadane is offline
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    13

    Extract polynomial coeffients on filtered (hidden) data

    Hello

    The equation of the polynomial trendline which is produced on a graph updates when you filter data which is what I want.

    I want to extract these coeffients using the equations below (i.e. 4th order polynomial)

    b4=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1)
    b3=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,2)
    b2=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,3)
    b1=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,4)
    a=INDEX(LINEST((H9:H18),(G9:G18)^{1,2,3,4}),1,5)


    They work fine on the complete data set, but not on a filtered table where some datapoints are filtered (hidden).

    I have seen some examples of using a helper column based on the subtotal command: subtotal(3,I9) al the way to subtotal(3,I18)

    I then modified my equation to be:

    b4=INDEX(LINEST(H9:H18,IF(I9:I18,G9:G18^{1,2,3,4})),1)


    but this gives me a #VALUE! error. I have tried putting parenthesis around them but it is not working.

    I have attached a small spreadsheet for clarity

    Anyone can help?

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-28-2024, 04:42 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    Still using Excel 2013?
    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
    Reply With QuoteRegister To Reply
  3. 04-28-2024, 05:20 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    No reply. So, I have assumed that you ARE still using Excel 2013.

    I'd replace your fpour formulae for unfiltered data with this array formula, that delivers the lot:
    =LINEST(H9:H18,G9:G18^{1,2,3,4})

    I'd then use this as the helper column, copied down:
    =AGGREGATE(3,7,[@x])

    and this array formula to deliver the paramters of the FILTERED list:
    =LINEST(INDEX(H:H,AGGREGATE(15,6,ROW(Table1[Helper])/(Table1[Helper]=1),ROW(INDIRECT("1:"&COUNTIF(Table1[Helper],1))))),INDEX(G:G,AGGREGATE(15,6,ROW(Table1[Helper])/(Table1[Helper]=1),ROW(INDIRECT("1:"&COUNTIF(Table1[Helper],1)))))^{1,2,3,4})

    IF you have O365, this can be made considerably shorter and the use of INDIRECT can be avoided.


    As stated, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-28-2024, 05:50 PM #4
    Canadane
    Canadane is offline
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    13

    Re: Extract polynomial coeffients on filtered (hidden) data

    Hello

    I am using version 16.16.27 (201012) for Mac
    Reply With QuoteRegister To Reply
  5. 04-28-2024, 05:53 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    I have no idea what that means. What is the PRODUCT? O365, Excel 2021 or what? Please amned your profile to show what you ARE using...
    Reply With QuoteRegister To Reply
  6. 04-28-2024, 05:59 PM #6
    Canadane
    Canadane is offline
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    13

    Re: Extract polynomial coeffients on filtered (hidden) data

    It was part of the Office 2016 package
    Reply With QuoteRegister To Reply
  7. 04-28-2024, 06:06 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    OK. Please update your profile, but it'll not enable me to do much to simplify the formula. Use it as is... Have you looked at it yet?
    Reply With QuoteRegister To Reply
  8. 04-28-2024, 07:13 PM #8
    Canadane
    Canadane is offline
    Registered User
    Join Date
    08-28-2019
    Location
    Montreal
    MS-Off Ver
    2013
    Posts
    13

    Re: Extract polynomial coeffients on filtered (hidden) data

    Hi again

    I can't seem to get the CNTRL+SHIFT+ENTER to work on the MAC. I tried OPTION SHIFT ENTER but to no avail.

    I will try it tomorrow at the ofrfice where I am running on Windows

    Thanks
    Reply With QuoteRegister To Reply
  9. 04-28-2024, 07:35 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    Command-shift-trturn is the Mac equivalent I think...
    Reply With QuoteRegister To Reply
  10. 04-28-2024, 07:39 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    44,150

    Re: Extract polynomial coeffients on filtered (hidden) data

    Another www suggests command-return...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy data in selection with hidden rows/filtered table and paste it
    By jsuar123 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-28-2019, 04:24 AM
  2. Problem with data order when looping through a filtered table with hidden columns
    By michielske in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-30-2018, 07:25 AM
  3. [SOLVED] Delete Table Rows of filtered data - when some columns are hidden
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2017, 10:42 PM
  4. Make Changes to Filtered Tables without Changing Hidden data underneath.
    By Barry34 in forum Excel General
    Replies: 2
    Last Post: 10-29-2016, 03:33 PM
  5. Keep hidden rows hidden, in a protected, filtered worksheet
    By djp630 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2014, 10:22 PM
  6. copying filtered data WITH the hidden columns
    By noraaa in forum Excel General
    Replies: 2
    Last Post: 07-04-2012, 04:28 AM
  7. extract coefficients of polynomial regression?
    By shepman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2005, 12:53 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1