asked on

Excel Determine y=mx+b without graph

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

kgerb

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

u002dag

ASKER

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

ASKER CERTIFIED SOLUTION

kgerb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

u002dag

ASKER

Thanks.

u002dag

ASKER

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

kgerb

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

u002dag

ASKER

Ok, it's working well. Thanks for following up and helping me. Dennis

kgerb

You're welcome. Glad to help.
Kyle