Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Is there a formula for simple linear regression

I have two columns data like below
A        B
1.2     0.518
4.6     0.776
5.6     0.805

3.8       ?(should be between 0.518 and 0.776, any formula I can use?)

Now I want to calculate data B when A = 3.8 and suppose it is linear relation
0
jjxia2001
Asked:
jjxia2001
  • 3
  • 2
  • 2
  • +1
1 Solution
 
StephenJRCommented:
If 3.8 is in A4 then I think this works

=B1+(A4-A1)/(A2-A1)*(B2-B1)

Btw this is interpolation, not regression.
0
 
jjxia2001Author Commented:
I used the similar formula, but I want to know if there is a function built inside Excel that I can use.
0
 
StephenJRCommented:
Nothing built in.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
barry houdiniCommented:
of course that formula assumes that you already know that 3.8 falls between A1 and A2. If that you need the formula to work unaltered for other values, e.g. if A4 = 4.8 then you can use this formula

=FORECAST(A4,OFFSET(B1:B3,MATCH(A4,A1:A3)-1,0,2,1),OFFSET(A1:A3,MATCH(A4,A1:A3)-1,0,2,1))

A4 must be >=A1 and < A3

regards, barry
0
 
byundtCommented:
Is there a function built into Excel? Of course there is--the TREND function. You use like this:
=TREND(B2:B4,A2:A4,D2)
=TREND(KnownX, KnownY, NewX)         returns Predicted Y

TREND does a linear regression to make its prediction, and can handle more than one X variable if necessary.
TrendFunctionQ27300562.xlsx
0
 
jjxia2001Author Commented:
This is great! This is what I'm looking for!  
0
 
byundtCommented:
I misspoke. The format for TREND is:
=TREND(KnownY, KnownX, NewX)         returns Predicted Y

The sample workbook and suggested formula with addresses were both correct, however.
0
 
barry houdiniCommented:
Hi Brad,

Surely TREND doesn't give your linear interpolation (unless the known Xs and known Ys are only 2 value ranges)? If you use 4.6 in D2, i.e. one of the A2:A4 values then wouldn't you expect the result to be the corresponding B2:B4 value, i.e. 0.776 - with TREND you get 0.754

For the original sample you can also use LOOKUP for linear interpolation, i.e. this will do the same as the FORECAST function I posted

=LOOKUP(A4,A1:A2,B1:B2+(B2:B3-B1:B2)*(A4-A1:A2)/(A2:A3-A1:A2))

regards, barry
0
 
barry houdiniCommented:
See attached file which demonstrates the different results obtained. There are random X and Y values in A2:B9 and (mostly) random "lookup values" in D2:D6. Press F9 key to re-generate random numbers

regards, barry
interpolation.xlsx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now