BL4820 Biochemistry Techniques

BL4820 Links: Home Labs/Lectures Help Sheets
Lec1 Lec2 Lec3 Lec4 Lec5 Lec6 Lec7 Lec8

Directions for obtaining linear regression data in Excel

PDF File for Printing: Linear Regression in Excel

Notes by Bill Campbell

Note: There is a "Quick Summary of Actions to Make LINEST Work" at the end of this document and it may be all you need to get the linear regression function in Excel to work fully. This is especially true if you are already pretty much used to working with Excel functions and inputting equations.

This description assumes that you have a data set with paired "X" and "Y" values, where X = amounts of standard (like mg of protein) and Y = absorbance values, which already have the blank subtracted.

The description begins after the data set has been collected or entered in Excel and averages of Abs taken and the average blank value subtracted from each average Abs. It will be illustrated as if the X and Y are in columns next to each other but that is not actually a requirement .

1. Data Set looks like this:

Protein (mg)	Subtracted Abs
0	0.0000
1	0.0757
2.5	0.0750
5	0.3880
10	0.8067

2. Select 1 cell next to 0.0000 Abs value as shown below:

mg	Abs
0	0.0000	"Hi-Lite"
1	0.0757
2.5	0.0750
5	0.3880
10	0.8067

3. Click on paste function, which is the tool on upper or main toolbar (paste function has icon "fx"). This starts the "function" Wizard, which brings up a dialog box showing all the functions for manipulating number values and doing calculations with them.

4. The first time you use the function Wizard you will have to look up the linear regression function, which is called "LINEST", in "Statistical" catalog of this dialog box. After that it will be stored in the "Most Recently Used" catalog which is shown to you when the function Wizard dialog box opens. In fact the LINEST function will be at the top of the list and highlighted. Once you locate LINEST, select it by highlighting it and click OK.

5. The action in step 4 brings up a new dialog box which flies up in the left hand corner of the data window and covers over the data. If this dialog box covers up data you want to use, you can move it by clicking the mouse near the top and dragging the box where ever you want to put it. The LINEST dialog box is a bit complicated (but it does have some logic in a strange way if you are a Microsoft programmer but most normal people would set this up in a different way).

The cursor is blinking in the data entry line window called "Known_y’s". Take the mouse and drag it over the Abs values and while you do, the range of the cells containing these values appears in the Known_y’s window. These become fixed when you let up on the left mouse button after you have highlighed the Abs data set.

6. At this point, you can either hit the tab key or click the mouse in the next window which is called "Known_x’s". Either way, the cursor should now begin to blink in the Known_x’s window. Drag the mouse over the NO3-N values and the range of their cell assignments will appear in the Known_x’s window.

NOTE: if you forget to hit tab (or otherwise get into the next window), the NO3-N value cell range will be written over the Abs values, since you are still in the window where you entered them. This happens cause this dialog does not go automatically go to next window after you have entered the cell range. Not to fret! Just go back and put in the Abs values again, click tab and then enter the range of the cells for the NO3-N values by dragging the mouse over them.

7. Press tab (or click in the next window) to get the cursor in window called "Const". Type True in this window and then click tab. This puts you in next window which is called "Stats". Type True here also. And then click OK.

8. This insane dialog box now disappears and the LINEST function appears in the edit window of the main data entry window of Excel and the slope of the line appears in the cell you selected by highlighting back in step 2 of this too long description. Too bad but the dialog box does not lead to the action it suggests it might do and you do not get the Y-intercept or any statistical values. The Web help on this subject says if you highlight more boxes in step 2 you will get the other numbers you want to see, but I do not find that to be true no matter what I do. So more insanity is required to get the function to put the rest of the information, which it is so deftly hiding, on to the data sheet where it should be. Plus the slope value has too many significant figures based on the data you gave it, but we will fix that later.

9. Select a set of cells which is 2 X 3 by dragging the mouse starting in the cell where the slope is. Then click the mouse in the data entry window where the LINEST function is shown with the data sets you entered along with the two logical "Trues". Magically the data sets are boxed in color. Now comes the trick which makes you get what you want.

10. Hold down the shift and Ctrl keys and press Enter.

Now LINEST puts its hidden gems in the proper boxes with the Y-intercept in the box to the right of the slope and the r² value below the slope 2 boxes down.

11. At this point, the LINEST results are all highlighted but still with a ridiculous number of significant figures. To adjust these to reasonable numbers use the increase decimal and decrease decimal tools on the second tool bar. These two icons have an arrow pointing left and 0.0/0.00 for the increase decimal and an arrow pointing right between 0.00 and 0.0 for the decrease decimal. If you hold the mouse over one of these icons without pressing a button, the help function of Excel will bring a little info box saying increase decimal when you are over its icon. So click on the decrease decimal icon and the set of numbers highlighted will all be decreased by one less significant figure. Doing this does not change the number, but just changes its appearance in the cell on the data sheet. In the end we want 4 significant figures, ie. 0.0000.

12. Now the slope, Y-intercept and r² value as well as a few other constants with meaning I do not know. (Maybe someone has some idea what these values are, but they are not important nor of interest to us). However, in order to get the r² value to appear on the data sheet we must also get this other stuff.

13. Now your data set and the linear regression results will look like this (I added some labels for identification):

mg	Abs	slope	y-intcpt
0	0.0000	0.0829	-0.0378
1	0.0757	0.0078	0.0401
2.5	0.0750	0.9741	0.0623
5	0.3880	r² value
10	0.8067

14. The linear regression constants give the equation of a straight line:

y = (slope) x + Y-intercept or Abs = (slope) mg + Y-intercept

In many cases, the Y-intercept is almost 0.0000 and can be ignored. But in the example above, the Y-intercept has a significant value and can not be ignored to get precise results.

15. To use this equation to calculate the amounts of unknowns based on the Abs values obtained in the assay for them, you need to rearrange the equation (you might say, "Hey, why did we enter the Abs values as Y values and mg standard as X values and not the other way around and avoid rearranging the equation now?" and I say, "If we make a graph of these results we always put the Abs found for the known standards, as Y and amounts of standards as X values". Remember that dependent variables like the Abs values are always put on Y axis on graphs while independent variables like mg protein standards values are put on the X axis):

Unk sample’s mg protein = [(Abs Unk) – (Y-intercept)]/slope

16. Now this equation can be entered in the Excel data sheet to do the calculations on your unknowns for you. I will assume that the Abs values for your unknowns are entered in a single column of the Excel data sheet where they have been averaged and the average blank values subtracted from them, which puts them in the same form as the Abs values of the standards used to calculate the linear regression equation.

So in the cell to right of the top value for an Unk Abs click the mouse to highlight it. Then click mouse in the edit window and type an equals sign "=". This will allow you to enter the equation "free hand" (ie. without using the function Wizard like we did above for LINEST):

After the "=" type the equation: ((cell for 1^st Unk Abs) – (Y-intercept value))/slope

After you put the "((" you can click the mouse on the cell of the 1^st Unk Abs and the cell assignment will appear in the equation. When you enter ")" the highlighted cell will lose the focus and you can proceed using the mouse. So you could then enter the "-(" of the next part of the equation and click on the cell of the Y-intercept, but if you do then you need to fix the cell location using special "$" characters or it will change when you copy this function in the next step. So it is easier to just enter the real value you see in the data sheet by typing it, but be sure to enter the sign of this value (for example the Y-intercept = -0.0378 in the above example). This means that if the Y-intercept is negative, it will get added to the Unk Abs. Best to also enter the slope as its rounded value as seen in the data sheet, but no need to worry about sign since it is always positive. Now hit Enter and the mg of protein value for the 1^st Unknown Abs should appear in the cell.

17. Last step before saving the data sheet and printing it is: copy the cell you just created by clicking on the copy icon on the top toolbar. This will result in the cell being highlighted with a "moving" dashed line. Now take the mouse and drag it over the boxes below the highlighted cell in parallel to the values for the Unk Abs that you want to calculate using the standard curve. Then click on the paste icon in the top toolbar and the values for the mg protein in each Unk will appear next to their corresponding Abs values. To get rid of the highlighted cell you used to copy the function, hit Esc key. If you do not hit Esc, you will get interesting results if you try to do more things on the data sheet.

Quick Summary of Actions to Make LINEST Work:

1. Set up your data set of mg protein and Abs values in columns.
2. Highlight cell to right of 1^st Abs value, which should be 0.0000
3. Call the function Wizard by clicking on its icon on the top toolbar.
4. Select LINEST function and its dialog box will appear when you hit OK.
5. Enter your cell ranges for data of interest in LINEST as well as 2 "Trues".
6. Click OK and LINEST dialog box disappears and you get slope in the cell you highlighted.
7. Highlight 2 X 3 set of cells to receive the linear regression data.
8. Click on edit window to activate the LINEST function.
9. Hold down Shift and Ctrl keys and hit Enter. The linear regression constants for slope, Y-intercept and r² will appear in the highlighted cells like magic!
10. Adjust the number of significant figures using decrease decimal icon on bottom toolbar.
11. Save the file at this point since you are finished if you only have one set of standard data to get the linear regression for.
12. If you have Unk Abs for which you want to calculate mg protein values, enter the equation for the line in the cell to the right of the 1^st Unk Abs. Make sure that the value you get from your equation makes sense (ie. does the mg protein value obtained for the1^st Unk Abs fall on the standard curve where you think it should be based on the Abs values for the mg of standard protein). If you think it is not right look over your equation and even do the calculation by hand with a calculator to make sure you entered the equation correctly.
13. Finally, copy the cell with the equation for the line and paste it next to all the Unk Abs values for which you want to get the mg protein.
14. Save the file and then print it.

BL4820 Links: Home Labs/Lectures Help Sheets