Calculate Unknown Concentration from Standard Curve in Excel

If you are looking for some special tricks to learn how to calculate an unknown concentration from the standard curve in Excel, you’ve come to the right place. This article will discuss every step of calculating an unknown concentration from the standard curve in Excel. Let’s follow the complete guide to learn all of this.

What Is Standard Curve?

In order to determine the correlation between two parameters, standard curves are used. In this method, the value of an unidentified parameter is found by comparing it with a quantity that can be measured more easily. A sample unknown calculation is calculated by generating a standard curve and using the TREND function. An image of the standard curve can be seen below.

In the following section, we will use one effective and tricky method to calculate an unknown concentration from the standard curve in Excel. In the following example, we will first generate a standard curve and then calculate the unknown concentration based on absorbance. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Here, we will demonstrate how to calculate an unknown concentration from the standard curve in Excel. The first step is to generate a standard curve in Excel, and then we will show you how to calculate unknown concentration. We must follow some specified rules to generate a standard curve in Excel. At first, we want to make a dataset. To do this, we have to follow the following rules.

• Firstly, write ‘Calculating Unknown Concentration from Standard Curve’ in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.
• After completing the heading part, you must enter the Pipe, Concentration, and Absorbance columns.
• In the Concentration column, we will enter the concentration value in each pipe or tube.
• We will enter the corresponding absorbance data for each concentration in the Absorbance column.
• Therefore, the first table will look like this.

Step 1: Generating a Standard Curve to Calculate Unknown Concentration in Excel

In this step, we are going to generate a standard curve. Let’s walk through the following steps to generate a standard curve in Excel.

• First of all, we are going to insert a scatter chart for our dataset.
• Then, select the range of the cells C5:D10.
• Now, in the Insert tab, click on the drop-down arrow of the Insert Scatter (X, Y) or Bubble Chart from the Charts group.
• Then, choose the Scatter chart.

• Therefore, you will get the following chart.

• Next, select the points in the scatter chart, and right-click on them. Then, select the Add Trendline option.

• Consequently, you will get the following standard chart.

• Now, we are going to add graph elements. In Quick Elements, some elements are already added or removed. But you can manually edit the graph to add or remove any elements of the chart by using the Add Chart Element option.
• After clicking on the Add Chart Element, you will see a list of elements.
• Next, you have to click on them one by one to add, remove, or edit.
• Alternatively, you can find the list of chart elements by clicking the Plus (+) button from the right corner of the chart.
• Here, you have to mark the elements to add or unmark the elements to remove.
• You will find an arrow on the element, where you will find other options to edit the elements.
• Here, we check Axis Titles and uncheck Gridlines.

• Therefore, the standard curve will look like this.

• To modify the chart style, select Chart Design and then, select your desired Style 8 option from the Chart Styles
• Alternatively, you can click on the chart, select the Chart Styles icon, and then select the chart you want.

• Finally, the standard curve with the dataset will look like this.

Step 2: Obtain Unknown Concentration from the Standard Curve

In this step, we will demonstrate how to calculate an unknown concentration. Follow the following steps to do the task. Here, we will use the TREND function to calculate the unknown concentration based on absorbance. The TREND function calculates the values of a given set of X and Y and returns additional Y-values by using the least square method based on a new set of X-values along with a linear trend line.

• First of all, input the absorbance in cell C12.
• Then, we will use the following formula in cell C13:

C5:C12 = known_y’s, dependent y-values.

D5:D12 = known_x’s, independent x-values.

C12 = new_x’s, new x-values to calculate the TREND value for.

• Next, press Enter.

• Finally, you will be able to calculate an unknown concentration from the standard curve as shown below.

Read More: How to Determine Protein Concentration from Standard Curve in Excel

💬 Things to Remember

✎ Before inserting a scatter chart, you must select anywhere in the dataset. The rows and columns will need to be manually added otherwise.

✎ When the given values of X, Y, and new X are non-numeric, and when the const argument is not a Boolean value (TRUE or FALSE), then the TREND function returns  #VALUE! Error.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to calculate an unknown concentration from the standard curve in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!

<< Go Back to Excel Standard Curve | Excel Charts | Learn Excel