Using Linear Regression Equations

Question

Using Linear Regression Equations

Hi, I am new to Numbers and having some difficulty.

I made a calibration curve using a line graph and got the linear equation from it. I now need to solve for an "x" value from the "y=mx+b" equation.

First I tried using the "FORECAST" function, but it keeps telling me "Cannot divide by zero." I don't have values of just "0", however most of my values are "0.906" etc. Can Numbers not recognize the difference between 0 and 0.9?

Either way, I have the equation from the chart "y=0.0161x+0.8879". I am trying to rearrange the equation to solve for x, so it would be "x=(y-b)/m". In this, I would plug in my known y value, and then the b and m values from the equation of the line.

Can someone tell me how to do this in Numbers? Thanks so much.

2009 macbook pro, Mac OS X (10.6.2)

Posted on Jan 27, 2010 12:05 PM

Reply

Answer 1

KOENIG Yvan

Level 8

41,819 points

Jan 27, 2010 12:08 PM in response to littlemissbis

I'm not sure that I understand your problem.
There is no solver in Numbers.

Yvan KOENIG (VALLAURIS, France) mercredi 27 janvier 2010 21:08:26

Reply

Answer 2

Jan 27, 2010 12:23 PM in response to KOENIG Yvan

Well, in Excel I was able to put into a cell an equation. For example, I could enter "=(y-b)/m" with values plugged into the variables and calculation would appear. So in this case, I'm trying to enter "=(0.615-0.12)/0.086" and then it tells me "Cannot divide by zero." Even using the "FORECAST" function, which allows you to plug numbers into the equation to forecast the slope of a linear equation, does not allow me to 0.9 in as a value. Can Numbers not recognize the difference?

Reply

Answer 3

Jan 27, 2010 12:27 PM in response to littlemissbis

bis,

I'll start you off with our standard advice to new Numbers users: Download the Numbers User Guide and the Formulas and Functions User Guide from the Help Menu and read them. They are written at a pretty basic level and will get you off on the right foot.

Here's my take on your problem...

In the cell below the "X", enter: =IF(ISBLANK(B), "", (B-C)/D).

Enter your Y value, and X will be calculated according to the expression you asked about.

Regards,

Jerry

Reply

Answer 4

Jan 27, 2010 1:03 PM in response to littlemissbis

I have one more question regarding this.

Now in the "formula cell" it calculates the number. But the number is to about 10 decimal places, I round it to about 3 decimal places. However, when I click on the number it brings up the formula, not the answer. How can I do this?

Thanks so much.

Reply

Answer 5

Jan 27, 2010 1:05 PM in response to littlemissbis

Ugh I also can't figure out how to type text (for units) after the number either. I am looking through the manual and can't find an answer. Sorry about all the questions.

Reply

Answer 6

Badunit

Level 7

30,426 points

Jan 27, 2010 1:30 PM in response to littlemissbis

I recommend you use the FORECAST function rather than copying the formula from your chart. First, FORECAST will be more accurate. Second, if your data changes, FORECAST will keep up with it; the hard-coded formula will have to be manually updated.

Reply

Answer 7

Jan 27, 2010 1:31 PM in response to littlemissbis

biss,

Thanks for the feedback.

Select View > Show Inspector and Click on the tab that has the number 42 in a box.

With your result cell selected, you can now adjust the number of decimals displayed. This will not round the result, just the display of the result. If you are going to use the result in another formula and actually want to round the calculated number, then another formula will be required. Just ask.

Jerry

Reply

Answer 8

Jan 27, 2010 1:37 PM in response to Jerrold Green1

Jerry,

Thanks, that worked perfect. Also do you know how to put text after the number also?

I really appreciate all the help!

bis

Reply

Answer 9

Jan 27, 2010 1:38 PM in response to Badunit

Badunit,

I've tried using the FORECAST function, but it isn't working for me. First, it says that it solves for Y, but I need to solve for X. Also, whenever I put my values in under the FORECAST function, it tells me "Cannot be divided by zero". Any thoughts?

Thanks

Reply

Answer 10

Jan 27, 2010 1:44 PM in response to littlemissbis

littlemissbis wrote:
Ugh I also can't figure out how to type text (for units) after the number either. I am looking through the manual and can't find an answer. Sorry about all the questions.

Also in the Cells Inspector, select Custom format...

You can drag the number formats and also type your own text into the format.

Jerry

Reply

Answer 11

Jan 27, 2010 2:03 PM in response to Jerrold Green1

Awesome, thanks so much!

Reply

Answer 12

Badunit

Level 7

30,426 points

Jan 27, 2010 3:27 PM in response to littlemissbis

When you get it working, swap the X's and Y's in the formula. Then it will solve for X.

Can you post an image of your spreadsheet and the exact formula that is giving this error? Or can you post the spreadsheet somewhere so we can download it and look at it. Without seeing it, it is hard to diagnose what is going on.

Reply