+ Reply to Thread
Results 1 to 7 of 7

Getting a fixed R-square value from linear trendline

  1. #1
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Getting a fixed R-square value from linear trendline

    Hello people


    (Please refer the excel file)

    I have to select specific data starting from 100 at X-axis till the point where R-square value is 0.99

    Right now I have do it manually by first removing the initial points from 0 to 100 and then removing points backwards from 700 or so till 300 something and see when I get 0.99 as the R-square.

    Is there a shortcut to do it? I have a lot of raw data and any help with save loads of my time.

    Thank you in advance!


    Anmol
    Attached Files Attached Files

  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: Getting a fixed R-square value from linear trendline

    Maybe thisin C2, copied down:

    =CORREL(A2:$A$2325,B2:$B$2325)^2

    and scroll down till you find 0.99

    You may need ; instead of ,
    Attached Files Attached Files
    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
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Re: Getting a fixed R-square value from linear trendline

    Thanks for the reply Glenn.

    But I am looking for this R square value of 0.99 starting from 100 in the x till around 330 or so (till wherever I get it)

    By your method it starts from 540 something which is R square of the whole curve.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Getting a fixed R-square value from linear trendline

    a
    b
    c
    d
    e
    248
    99.867
    0.770
    249
    100.267
    0.772
    330.0667
    d249: {=index(a250:a2325, match(2, 1/((c250:c2325 >= 0.99) * (c251:c2326 < 0.99))))}
    250
    100.867
    0.774
    1.00000
    c250: =rsq(a$249:a250, b$249:b250)
    251
    101.600
    0.775
    0.97050
    252
    102.267
    0.777
    0.98823
    253
    103.200
    0.777
    0.90185
    254
    103.800
    0.777
    0.84443
    255
    104.467
    0.778
    0.87779
    256
    105.000
    0.778
    0.90321
    257
    105.400
    0.779
    0.92645
    258
    105.867
    0.781
    0.93836
    259
    106.267
    0.783
    0.91688
    260
    106.667
    0.785
    0.88855
    261
    106.933
    0.786
    0.88177
    262
    107.133
    0.790
    0.84443
    263
    107.467
    0.791
    0.83395
    264
    107.867
    0.795
    0.82108
    265
    108.333
    0.795
    0.83409
    266
    108.733
    0.799
    0.84020
    267
    109.133
    0.801
    0.85096
    268
    109.667
    0.803
    0.86385
    269
    110.067
    0.805
    0.87647
    270
    110.667
    0.807
    0.88872
    271
    111.133
    0.809
    0.90097
    272
    111.733
    0.810
    0.91272
    273
    112.333
    0.812
    0.92305
    274
    113.133
    0.813
    0.93198
    275
    113.667
    0.814
    0.93852
    276
    114.267
    0.815
    0.94353
    277
    114.733
    0.816
    0.94766
    278
    115.133
    0.816
    0.95067
    279
    115.467
    0.819
    0.95482
    280
    115.800
    0.820
    0.95841
    281
    116.200
    0.823
    0.96195
    282
    116.600
    0.824
    0.96502
    283
    117.067
    0.827
    0.96780
    284
    117.533
    0.828
    0.97028
    285
    118.067
    0.831
    0.97252
    286
    118.533
    0.832
    0.97459
    287
    118.933
    0.834
    0.97643
    288
    119.333
    0.836
    0.97798
    289
    119.667
    0.838
    0.97938
    290
    120.133
    0.840
    0.98061
    291
    120.667
    0.842
    0.98182
    292
    121.200
    0.844
    0.98291
    293
    121.600
    0.846
    0.98382
    294
    121.800
    0.848
    0.98433
    295
    121.933
    0.854
    0.98311
    296
    122.200
    0.855
    0.98232
    297
    122.667
    0.858
    0.98148
    298
    123.333
    0.856
    0.98216
    299
    124.067
    0.859
    0.98299
    300
    124.733
    0.861
    0.98385
    301
    125.533
    0.863
    0.98476
    302
    126.267
    0.863
    0.98559
    303
    127.000
    0.866
    0.98639
    304
    127.600
    0.868
    0.98710
    305
    128.067
    0.870
    0.98780
    306
    128.333
    0.874
    0.98843
    307
    128.533
    0.879
    0.98861
    308
    128.867
    0.879
    0.98893
    309
    129.200
    0.883
    0.98887
    310
    129.667
    0.883
    0.98916
    311
    130.133
    0.886
    0.98936
    312
    130.800
    0.884
    0.98986
    313
    131.600
    0.887
    0.99035
    314
    132.267
    0.884
    0.99046
    315
    132.933
    0.884
    0.99024
    316
    133.400
    0.886
    0.99001
    317
    133.800
    0.886
    0.98970
    318
    133.933
    0.887
    0.98958
    319
    134.067
    0.893
    0.98997
    320
    134.333
    0.899
    0.99030
    321
    134.733
    0.899
    0.99066
    746
    329.400
    1.807
    0.99013
    747
    329.600
    1.815
    0.99009
    748
    329.800
    1.817
    0.99006
    749
    330.067
    1.825
    0.99001
    750
    330.467
    1.829
    0.98996
    751
    330.867
    1.835
    0.98990
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Re: Getting a fixed R-square value from linear trendline

    Thanks for the awesome format shg!

    But where do I put the formula/ how do I use it?

    Anmol

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Getting a fixed R-square value from linear trendline

    ??

    Put the formula that's shown for D249 in D249, and the formula that's shown for C250 in C250 and copy down.
    Last edited by shg; 11-25-2019 at 01:54 PM.

  7. #7
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Re: Getting a fixed R-square value from linear trendline

    Oh I am sorry, I didn't see it. Thanks for your help!

+ 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. Linear trendline:wrong equation
    By Jan M. in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 09-28-2016, 02:09 PM
  2. Equation & R-Square Value of Trendline Remained Unchanged
    By Wes-at-UoB in forum Excel General
    Replies: 1
    Last Post: 05-22-2015, 05:44 PM
  3. How do I get my linear trendline to stop at zero?
    By HaileyGoddard in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-06-2013, 02:22 PM
  4. Replies: 2
    Last Post: 04-28-2012, 10:19 PM
  5. Excel Linear Trendline Query
    By frcrilly in forum Excel General
    Replies: 4
    Last Post: 02-19-2011, 10:51 AM
  6. Trendline R square
    By balmalik in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-28-2006, 10:55 AM
  7. Linear Trendline
    By maperalia in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-04-2006, 06:30 PM

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