How to Calculate Bacterial Growth Rate in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Ways to calculate bacteria growth rate in Excel requires a basic Exponential Formula depending on its characteristics. However, plotting the bacteria culture values against time can also provide us with the required growth rate. In this article, we demonstrate ways to calculate bacterial growth rate using an Exponential Formula and Excel Chart.

Let’s say we have a dataset that showcases numbers in the growth of bacteria culture against time. Therefore, we want to calculate the growth rate of bacteria in those cultures.

Dataset-Calculate Bacterial Growth Rate in Excel


Download Excel Workbook


2 Easy Ways to Calculate Bacterial Growth Rate in Excel

Method 1: Using an Exponential Formula to Calculate Bacterial Growth Rate in Excel

Bacteria in cultures follow exponential growth. From experiments, we get the formula to calculate the growth rate. The Growth Rate (µ) formula is

 µ = ((log10 N - log10 N0) 2.303) / (t - t0)

Here,

N = Number of Bacteria at t second

No = Number of Bacteria at to second

t = a particular time in the growth period

to = starting time of bacteria culture

Step 1: Write the following formula in any blank cell (i.e., E6).

= ((log10 N - log10 N0) 2.303) / (t - t0)

Formula insertion-Calculate Bacterial Growth Rate in Excel

Step 2: Press ENTER then Drag the Fill Handle to display the Growth Rate for Bacteria Culture 1 as depicted in the image below.

Resultant values

You can repeat Steps 1 and 2 in order to bring out the Growth Rate for Bacteria Culture 2 similar to the picture below.

Result

Step 3: After getting the Growth Rate percentages for Bacteria Culture 1, apply the AVERAGE function. The AVERAGE function calculates the average Growth Rate percentages out of those resultant percentages.

Type the below formula

=AVERAGE(E5:E14)

average formula insertion-Calculate Bacterial Growth Rate in Excel

Again, following Step 3, apply the AVERAGE formula to calculate the average Growth Rate of bacteria in Culture 2.

Percentages

Step 4: You have to combine the two average Growth Rates into an Accumulated Average Growth Rate using an AVERAGE formula. Use the formula written below

 =AVERAGE(E15:F15)

Accumulated average growth rate-Calculate Bacterial Growth Rate in Excel

Step 5: Hit the ENTER key and you get the Accumulated Average Growth Rate for the two Bacteria Cultures.

Final result

Calculating a bacteria culture Growth Rate requires experimental data and the formula must be compatible with the characteristics of that bacteria culture. However, we calculate the Bacteria Growth Rate using a typical empirical formula. You can use yours that is compatible with the bacteria characteristics.

🔁 To display the Growth Rate in percentages, you have to pre-formatted the cell in percentage Category from the Format Cells window.

Select a range of cells then Press Keyboard shortcuts CTRL+1 altogether to bring out the Format Cells window. Afterward, Select the Number section > Choose Percentage as Category > Assign the number of Decimal Places you want your data in.

Click OK.

Format Cells

Read More: How to Calculate Revenue Growth Rate in Excel 


Method 2: Using Chart Method to Display the Growth Rate

In the previous method, we used an Exponential Formula to calculate the Growth Rate of a Bacteria Culture. Alternatively, we can use the dataset to display a Curve Chart from where we extract the Exponential Formula. Just by looking at the formula, we can easily find out the Growth Rate for each Bacteria Culture. The typical Exponential Formula of Bacteria Culture is

y=b*emx

In the formula,

y = yielding number of bacteria after x time.

b = Constant.

m = Bacteria Growth Rate

Step 1: Select the two columns (i.e., Time vs Number for Bacteria Culture 1). Then Go to the Insert tab > Select a Chart Type (shown in the screenshot) from Scatter options (from the Charts section).

Chart-Calculate Bacterial Growth Rate in Excel

Step 2: The Chart appears then Right Click on the Chart. The Context Menu pops up. From the Context Menu, Select Select Data from the options.

context menuStep 3: The Select Data Source window appears. You can see there is an added data source already. You want to add the bacteria culture 2 entries as another data source. In order to do so, Click on Add in the Select Data Source window.

Select data source window

Step 4: The Edit Series window opens up. Insert the respected data in Series X and Y values. Give the Series a Name (i.e., Number of Bacteria (Culture 2)).

Click OK.

Edit series window

Step 5: Clicking OK on the Edit Series dialog box takes you to the Select Data Source window again. In that window, we see there are two data sources. The two data sources mean you’ll have two Curves on the Chart of the two equations.

Click on OK.

Select Data source window

Similar to your expectations, you have two Curves on the Chart. One is there for Culture 1 and the other for Culture 2 as depicted in the following image.

Curve on Chart-Calculate Bacterial Growth Rate in Excel

Step 6: As we want to insert the formulas for each Curve. Click on any curve (i.e., Orange Colored) then Right Click on it.

The Context Menu appears. From the Context Menu, Select Add Trendline.

Add trendlineStep 7: The Format Trendline side common box appears. From the Format Trendline command box, Choose Exponential as Trendline Options. Afterward, Scroll Down.

Format trendline window

Step 8: After Scrolling, you see an option saying Display Formula on Chart. Check the option as shown in the picture below.

Display formula on the chart-Calculate Bacterial Growth Rate in Excel

Step 9: Go repeat Step 6, 7, and 8 for the Blue Colored curve and you get the exponential equations in the same colors for both Curves.

Equation for both curvesIf you want to add the Chart and Axis title, click on the big PLUS Icon button. A couple of options appear, Tick the Axis and Chart Title to display them on the chart.

Adding titleAssign suitable titles for the Chart and Axis Title. The entire chart looks similar to the image below.

Final chart with equationYou see the equations (i.e., y=7566.7e0.0605x and y=7525.3e0.0594x) for both data sources. By comparing the equations to the typical Exponential Equation (i.e., Y=b*emx ), you can say that the Growth Rate of the Bacteria Culture 1 and 2 are 0.605 or 6.05% and 0.0594 or 5.94% respectively.

You can use as many data sources as you want. Then find the Growth Rate just by acquiring the equations.


Conclusion

In this article, we demonstrate ways to calculate the bacteria growth rate in Excel. We mainly use the empirical Exponential Formula to calculate the bacteria growth rate. We also use Excel Chart to provide us with the exponential formula. However, growth rates achieved by those two methods don’t match. This happens because we use an average formula to calculate the growth rate ignoring the phases of bacteria growth in cultures. But we use the most realistic approach by using an Excel Chart to find out the growth rate. Hope you find these methods easy to comprehend and use. Comment, if you have further inquiries or anything to add.


Related Articles


<< Go Back to Excel GROWTH Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo