Select a range of values and automatically use as input into an array?

GrandmasCookies

New Member
Joined
Mar 5, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi, so here is the problem I'm working on...

I have a LINEST function that I use to tabulate the coefficients of a polynomial trendline. The function looks like this:

=LINEST(C37:C47,B37:B47^{1,2,3,4,5,6},TRUE,TRUE)

Currently I have to manually change the range of cells I want to reference in column C and B, but I would like that range to change automatically based on a value I input into cell J4.

Column C is numbered from 0 to 200 in increments of 4. If I change the number in J4 , I want my LINEST function to pull a range of about 5 cells above and below the number from J4. So if I enter 20 into J4, the LINEST function should automatically change the range from column C to include numbers 0, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40 (that corresponds to the range C18:C28 in my sheet) Then the range for column B should change to match.

Additionally, I have a graph that used the same ranges and plots the points. Is there a way to get the graph automatically use the new ranges too.

Thank you for any help!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
This does it:

=LINEST(INDEX($C$1:$C$51,MATCH($J$4,$C$1:$C$51,0)-5):INDEX($C$1:$C$51,MATCH($J$4,$C$1:$C$51,0)+5),INDEX($B$1:$B$51,MATCH($J$4,$C$1:$C$51,0)-5):INDEX($B$1:$B$51,MATCH($J$4,$C$1:$C$51,0)+5)^{1,2,3,4,5,6},TRUE,TRUE)

It has potential for error if you dont stick to the boundaries of C1:C21 with the number you put in J4. Dont use 16 or lower or 184 or higher.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,382
Messages
5,635,931
Members
416,888
Latest member
Mike66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top