Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 4: Q29P (page 93)

Excel LINEST function. Enter the following data in a spreadsheet and use LINEST to find slope, intercept, and standard errors. Use Excel to draw a graph of the data and add a trendline. Draw error bars of±syon the points.

x:3.010.020.030.040.0y:-0.074-1.411-2.584-3.750-5.407

Short Answer

Expert verified

The slope of the given data is-0.13789

The intercept of the given data is0.195343

The standard error of the given data is0.197625

The graph of the given data by the use of the LINEST function is given in step 2.

Step by step solution

01

Determine standard deviation of y : sy and the standard uncertainty of the slope and the intercept

The standard deviation ofyis mathematically represented as

σy≈sy=∑(di2)n-2.

The standard uncertainty of a slope is mathematically represented as

um2=sy2nD.

The standard uncertainty of an intercept is mathematically represented as

ub2=sy2∑(xi2)D.

02

Determine the slope, intercept, and standard errors

The given data is listed below.

Use the Excel LINEST function to calculate the slope, the intercept, and the standard errors.

Use Excel to draw the graph, the error bars of ±syon the points.

Excel LINEST function:

The Excel LINEST function used (Figure 1) to calculate the slope, the intercept and the standard errors; and to draw a graph is shown in Figure 2.

From the spreadsheet, the slope is -0.13789, the intercept is 0.195343and the standard error found is 0.197625

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

The CdSe content (g/L) of six different samples of nanocrystals was measured by two methods. Do the two methods differ significantly at the 95 % confidence level?

Now we use a built-in routine in Excel for the paired t test to see if the two methods in Problem 4-15 produce significantly different results. Enter the data for Methods 1 and 2 into two columns of a spreadsheet. For Excel 2007 and 2010, find Data Analysis in the Data ribbon. If Data Analysis does not appear, follow the instructions at the beginning of Section 4-5 to load this software. Select Data Analysis and then select t-Test: Paired Two Sample for Means. Follow the instructions of Section 4-5 and the routine will print out information including tculculaed (labeled "tStat")andtuable(labeled " t Critical two-tail"). You should reproduce the results of Problem 4-15.

Here are mass spectrometric signals for methane inH2:

CH4(vol%):00.0620.1220.2450.4860.9711.921Signal(mV):9.147.595.6193.8387.5812.51671.9

(a) Subtract the blank value (9.1) from all other values. Then use the method of least squares to find the slope and intercept and their uncertainties. Construct a calibration curve.

(b) Replicate measurements of an unknown gave 152.1, 154.9, 153.9 and 155.1mV , and a blank gave 8.2,9.4,10.6 and 7.8mV . Subtract the average blank from the average unknown to find the average corrected signal for the unknown.

(c) Find the concentration of the unknown, its standard uncertainty (ux) , and the95%confidence interval.

Blood plasma proteins of patients with malignant breast tumors differ from proteins of healthy people in their solubility in the presence of various polymers. When the polymers dextran and poly(ethylene glycol) are mixed with water, a two-phase mixture is formed. When plasma proteins of tumor patients are added, the distribution of proteins between the two phases is different from that of plasma proteins of a healthy person. The distribution coefficient ( K) for any substance is defined as K =[concentration of the substance in phase[concentration of the substance in phase B ]. Proteins of healthy people have a mean distribution coefficient of 0.75 with a standard deviation of 0.07. For the proteins of people with cancer, the mean is 0.92 with a standard deviation of 0.11.

(a) Suppose that Kwere used as a diagnostic tool and that a positive indication of cancer is taken asK≥0.92. What fraction of people with tumors would have a false negative indication of cancer becauseK≥0.92?

(b) What fraction of healthy people would have a false positive indication of cancer? This number is the fraction of healthy people withK≥0.92, shown by the shaded area in the graph below. Estimate an answer with Table 4 - 1 and obtain a more exact result with the NORMDIST function in Excel.

(c) Vary the first argument of the NORMDIST function to select a distribution coefficient that would identify 75% of people with tumors. That is, 75% of patients with tumors would have K above the selected distribution coefficient. With this value of K, what fraction of healthy people would have a false positive result indicating they have a tumor?

Spreadsheet for standard deviation. Let's create a spreadsheet to compute the mean and standard deviation of a column of numbers in two different ways. The spreadsheet here is a template for this exercise.

(a) Reproduce the template on your spreadsheet. Cells B4to B8contain the data ( xvalues) whose mean and standard deviation we will compute.

(b) Write a formula in cell B9to compute the sum of numbers in B4to B8.

(c) Write a formula in cell B10to compute the mean value.

(d) Write a formula in cell C4to compute (- mean), where xis in cellB4 and the mean is in cell B10. Use Fill Down to compute values in cells C5to C8.

(e) Write a formula in cellto compute the square of the value in cell. Use Fill Down to compute values in cellsto.

(f) Write a formula in cell D9 to compute the sum of the numbers in cells D4to D8.

(g) Write a formula in cell B11to compute the standard deviation.

(h) Use cells B13to B18to document your formulas.

(i) Now we are going to simplify life by using formulas built into the spreadsheet. In cell B21type ''=SUM(B4:B8)''which means find the sum of numbers in cells B4to B8. Cell B21should display the same number as cell B9. In general, you will not know what functions are available and how to write them. In Excel 2010, use the Formulas ribbon and Insert Function to find SUM.

(j) Select cellB22. Go to Insert Function and find AVERAGE. When you type "=AVERAGE(B4:B8)" in cell B22, its value should be the same asB10.

(k) For cellB23, find the standard deviation function(=STDEVB4:B8n)and check that the value agrees with cell B11.
See all solutions

Recommended explanations on Chemistry Textbooks

Nuclear Chemistry

Read Explanation

Organic Chemistry

Read Explanation

Physical Chemistry

Read Explanation

Chemistry Branches

Read Explanation

Ionic and Molecular Compounds

Read Explanation

Inorganic Chemistry

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free