# Using Regression Analysis in Excel - Predicting a Value

#### neoth

##### Involved In Discussions
hi all,

if i had a set of readings, does anyone knows how do i find the value in between?

x y
9 10
11 12
14 15
18 20
25 30

if i have a table like above, and i wan to find the value of y when x=20, what formulae should i use?

i tried using the regression in excel, but it does nt seem to work.

please help...

Elsmar Forum Sponsor
S

#### statdoug

Re: Predict a value

Are you assuming a linear model? if so, you can use the slope function and the intercept function to define the line formula. Then plug in your value for X. To get a predicted Y from a known X is simply Y=mX + b where m=the slope and b=the intercept. This does not give you an indication of the error that you would get with the regression function, but it will give the least squares fit for the data points given.

#### Pancho

##### wikineer
Super Moderator
Re: Predict a value

Not sure why your Excel regression didn't work.

Linearly, Y = 1.2543X - 1.9169. Your R2 is over 99%. At X = 20, Y = 23.169.

#### neoth

##### Involved In Discussions
Re: Predict a value

Not sure why your Excel regression didn't work.

Linearly, Y = 1.2543X - 1.9169. Your R2 is over 99%. At X = 20, Y = 23.169.

Hi Pancho,

thanks for the reply,

but i tried to check back using my original data, when X=14,

Y=(1.25438*14)-1.9169, Y = 15.64,

but base on my original table, when X=14, Y should be = 15.

this is the problem i encounter. anyone knows where goes wrong?

please help.

thanks

#### Pancho

##### wikineer
Super Moderator
Re: Predict a value

Neoth, this means is that there are other sources of variation on your Y values other than X. You will not get an exact fit from a linear regression unless you only have two points or the data is exactly linear.

You could get an exact fit by using a function (say a polynomial) with independent parameters equal to your number of data points, but that would probably be too contrived and would likely not explain the relationship between X and Y any better than a line does.

S

#### statdoug

Re: Predict a value

Have you looked at a scatter plot? You will see what Pancho is talking about.

#### bobdoering

##### Stop X-bar/R Madness!!
Trusted Information Resource
You major error is that the data is not linear (see attached), so therefore using a linear regression will provide a result that is not accurate.

#### Attachments

• 10.5 KB Views: 703

#### neoth

##### Involved In Discussions
ic, so my problem here is my data are not linear,

so in this case, what can i do with this type of non linear data?

regards

K

#### kaikai

To add quadratic term to the model, you can get this equation.

y = 3.5019757 + 0.5345879*x + 0.0210296*x^2

R-square 0.999917
RMSE 0.102951

B

#### Bill Pflanz

The use of the quadratic equation is overkill for the data analysis. The r square for the linear equation is 0.99384 which means that only 0.006 or 0.6%is unexplained variance and the regression would be considered valid enough to use for prediction.

There is a potential source of error due to the small number of data points used in the analysis. It is also important in regression analysis that there is a real relationship between the two values and not just two random sets of values that have no dependence on each other.

Bill Pflanz

Thread starter Similar threads Forum Replies Date
S Regression Analysis / ANOVA using Excel Excel .xls Spreadsheet Templates and Tools 2
J Setting Manufacturing Yield Targets using Regression Analysis Statistical Analysis Tools, Techniques and SPC 3
B Using Minitab 17's Stepwise Regression to Predict Feature Using Minitab Software 2
S Using Minitab for Multinomial Logistic Regression Using Minitab Software 2
J Evaluating a Regression Model Using the Constant Variance Assumption Six Sigma 8
Risk Analysis using Monte Carlo Simulation instead of Scoring and Heat Map Risk Management Principles and Generic Guidelines 0
Using tailoring guidelines to tailor a QMS procedure ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
We found out we have been using a equipment without validation for past 4 years Quality Manager and Management Related Issues 6
Using Laboratory Notebooks in R&D and Design and Development ISO 13485:2016 - Medical Device Quality Management Systems 3
Importer shell game - Using a third party logistics provider (3PL) in the EU EU Medical Device Regulations 4
Work performed in Canada on US patients using US device Canada Medical Device Regulations 1
Is using ANSI/ASQ Z1.4-2008 the correct sampling plan to determine Pass/Fail of Apparel measurements? AQL - Acceptable Quality Level 4
What are the pros and cons of using an audit software for internal auditing? General Auditing Discussions 4
Evaluating nonconformances for escalation using Bayesian methods? Statistical Analysis Tools, Techniques and SPC 2
Using non-conforming components even though the final assembly is conforming? Manufacturing and Related Processes 5
Using Unreleased Documents & Process Maps for Internal Audit purposes ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 12
Clause 7.7 Replicate, Recalibration and Intermediate checks using Artifact ISO 17025 related Discussions 1
Are any medical device companies using the 2011 FDA process validation guidance instead of GHTF/SG3/N99-10:2004? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 1
Unit of Use DI (Device Identifier) - Products using the same device US Food and Drug Administration (FDA) 0
Using Arduino based sensors for Poke-Yoke Manufacturing and Related Processes 6
Using your Manufacturer's ISO certification ISO 13485:2016 - Medical Device Quality Management Systems 5
Adverse Event Clinical Trial using a 510K approved Device Other US Medical Device Regulations 6
Using non CE parts in a machine CE Marking (Conformité Européene) / CB Scheme 1
Gauge R&R on multiple dimensions using 3D measurement system Capability, Accuracy and Stability - Processes, Machines, etc. 6
Hospital IT expectations for connected medical device using WIFI Medical Information Technology, Medical Software and Health Informatics 0
Cp / Cpk on position using multiple MMC bonuses Capability, Accuracy and Stability - Processes, Machines, etc. 2
Using external FDA and ISO 13485 audit as internal audit Internal Auditing 6
Using "Particle Size Standard" templates as gauges - How to avoid giving a gauge # while using for process control? General Measurement Device and Calibration Topics 2
Class 1 Medical Device - Using a UPC over the UDI? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 0
Reducing CE marking cost using manufacturer test reports CE Marking (Conformité Européene) / CB Scheme 5
Using clinical trial safety data for evidence for CE marking EU Medical Device Regulations 7
Accredited Calibration Sevice Provider using computerized system to issue calibration certificate Qualification and Validation (including 21 CFR Part 11) 3
Accredited Calibration Sevice Provider using computerized system to issue calibration certificate General Measurement Device and Calibration Topics 2
Raw stock material testing discrepancy using an XRF (x-ray fluorescence) analyzer Manufacturing and Related Processes 7
Using FMEA for Knowledge Management FMEA and Control Plans 6
NASA to Develop a Novel Approach for All-Electric Aircraft Using Cryogenic Liquid Hydrogen as Energy Storage World News 2
How many of you are using Robotic process automation for calibration lab management? ISO 17025 related Discussions 0
Including Repeats in DoE using Minitab Using Minitab Software 5
Legal Manufacturer FDA Reporting Obligations for Using New Contract Sterilization Site Other Medical Device Regulations World-Wide 0
What size pinhole can be reliably detected using visual inspection? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 7
I'm new in IMDS special using the IMDS application Manufacturing and Related Processes 8
Workplace by Facebook - Any other organizations using this collaborative tool? Misc. Quality Assurance and Business Systems Related Topics 0
Flatness - Rectangular part using the three jack method Inspection, Prints (Drawings), Testing, Sampling and Related Topics 10
How important is using the "correct" GMDN? Other Medical Device Regulations World-Wide 13
Overkill? Using the 3L5W tool on non-conformities Nonconformance and Corrective Action 3
GD&T tolerance or band? Using a symbol like parallelism // Inspection, Prints (Drawings), Testing, Sampling and Related Topics 8
Informational Expanded Gage R&R Analysis using Minitab Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 18
Evaluating the possibilities of using qmswrapper or greenlightguru Document Control Systems, Procedures, Forms and Templates 4
Is it possible to make an educated decision using a very very small sample size? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Using K-Factor(Tolerance Interval) Analysis for Design Verification Statistical Analysis Tools, Techniques and SPC 3