Solved

Excel Determine y=mx+b without graph

Posted on 2012-04-10
8
455 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office.Com /myAccount 9 39
Recover lost Hyperlink destination/target in OneNote 3 28
Boolean help 6 27
Add a range in an Excel graph 5 27
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

13 Experts available now in Live!

Get 1:1 Help Now