Solved

Excel Determine y=mx+b without graph

Posted on 2012-04-10
8
445 Views
Last Modified: 2012-04-10
Hi,
I learned how to create a graph and have Excel create y=mx+b. Is there a way to do this  without a graph. I have a lot of data sets where one or two data points are missing and I just need the m value. If I had the slope value, I can then solve for the missing value.

Thanks,
Dennis
0
Comment
Question by:u002dag
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37827618
I'm not sure what you're asking.  If you have two points you can figure out the slope between them like this.

m = (y2 - y1)/(x2 - x1)

Is this what you're looking for?

Kyle
0
 

Author Comment

by:u002dag
ID: 37827983
Thanks for getting back to me so quickly.
For x values, 1, 2, 3, 4, 5, Here are some y values listed horizontally in Excel:
45.69
46.26
blank cell
47.11
46.92

I draw the graph and get y=0.331x+45.502
equation is created whether or not there is a blank cell

That works. But I have a lot of similar simple data sets and I don't want to create a dozens of these little graphs just so I can create an equation and then I can manually fill in the value in the blank cell. The blank cell could be in any one or two of the locations.

I hope that helps.

Thanks, Dennis
0
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 37828204
Ok, I think this will get you where you want to be.

What you're looking for is a least squares linear regression.  This can be done manually using Excels LINEST function.  Look at the example workbook for an example using your data.  One caveat is that it doesn't like blanks.  So, before you can us the formula you need to do a linear interpolation to fill in the empty cells.  If you empty cell is D2 and you want to linearly interpolate between cells C2 and E2 you would use the following formula.

D2 = (C2+E2)/2

This needs to be done for each blank cell in your data.  Then you will be able to use the LINEST formula.  LINEST is an array formula so it must be confirmed using Ctrl+Shift+Enter.  To recreate the example worksheet follow these steps.

1.  Enter the linear interpolation into cell D2
2.  Select the range C5:D5
3.  Type the following:  =LINEST(B2:F2,B1:F1)
4.  Confirm with Ctrl+Shift+Enter

C5 will not contain the slope of the regression line and D5 will contain the y-intercept.  If you have questions or get stuck let me know.

Kyle
Q-27668930.xlsx
0
 

Author Closing Comment

by:u002dag
ID: 37828667
Thanks.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:u002dag
ID: 37829084
Hi Kyle,
Are you still there?
I got the slope in the first cell.
I can't get the x-intercept in the adjacent cell.
Do I have to push the F2 key somehow? Is there a specific sequence of keys?

Could you please describe the exact sequence of keys you hit.

1.  Enter the linear interpolation into cell D2
2.  Select the range C5:D5
3.  Type the following:  =LINEST(B2:F2,B1:F1)
4.  Confirm with Ctrl+Shift+Enter
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37829139
No, there is no need to press F2 at any time to get this to work.  Here are some more detailed instructions.
Make sure in step 2 you have both cells selected but C5 should be the active cell.  For example if you select both cells and press tab you will be able to toggle the active cell back and forth.  Make sure C5 is active.
Then, with both cells selected, press the equals sign "=" and enter the formula LINEST( with a beginning parentheses.
Select the known_y's for the first argument, and the known_x's for the second argument (B2:F2 and B1:F1 respectively in our current example).
The final two arguments are optional and can be ignored in this instance.
Add a closing parentheses ")" and confirm the formula by pressing Ctrl+Shift+Enter.

That should do it.  Let me know how you make out.

Kyle
0
 

Author Comment

by:u002dag
ID: 37829289
Ok, it's working well. Thanks for following up and helping me. Dennis
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37829300
You're welcome.  Glad to help.
Kyle
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now