[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Determine y=mx+b without graph

Posted on 2012-04-10
8
Medium Priority
?
1,246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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