[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel Determine y=mx+b without graph

Posted on 2012-04-10
8
Medium Priority
?
1,684 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 2000 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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

612 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