Solved

Excel Determine y=mx+b without graph

Posted on 2012-04-10
8
505 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Closing Comment

by:u002dag
ID: 37828667
Thanks.
0
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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