2 Ways to Calculate Bacterial Growth Rate in Excel

There may be times when you need to use Excel to calculate the bacterial growth rate. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to use Excel to calculate bacterial growth rates with examples.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Bacterial Growth Rate Explanation

Under good conditions, the number of bacteria grows in a pattern called a geometric sequence. In a regular way, the number of bacteria doubles every so often. So, if the culture has two bacteria after one hour, there will be four after two hours, eight after three hours, sixteen after four hours, and so on. Exponential growth is the name for this kind of growth. Exponential growth can only happen in the lab, where conditions are ideal.

bacterial growth rate


Learn to Calculate Bacterial Growth Rate in Excel Using These 2 Handy Ways

To figure out how fast bacteria grow in Excel, you need to use the basic Exponential Formula, LOG function, and its properties.  Finally, we will use the AVERAGE function. The central tendency of a bunch of numbers in a statistical distribution is measured by the AVERAGE function. But we can also get the required growth rate by putting the values of the bacteria cultures against time. Here we have a set of data that shows how the number of bacteria in a culture changes over time. So, we need to figure out how fast the bacteria in those cultures grow.

Dataset

Method 1

1. Using Exponential Formula

In cultures, bacteria grow in a way that is called exponential. Experiments give us the formula we need to figure out the growth rate. The formula for Growth Rate (µ) is:

Using Exponential Formula

Where,

μ = Bacterial growth rate

N = Total count of bacteria at the time (Second), t

No = Total count of bacteria at the time, to

t = Time of the growth rate

to = Starting time of bacteria counting

Here we will use the first formula where the base of the logarithm is 10.

We are going to use the LOG function to determine the log values and AVERAGE function to mean out the time intervals.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we put the formula in cell D7.
=((LOG(C7)-LOG(C6))*2.303)/(B7-B6)

We cannot put the formula in the first cell D6 as we cannot get the growth rate for the first value.

  • Then we press Enter and use Autofill to drag down the formula to fill the rest of the cells.

how to calculate bacterial growth rate

  • Finally, we use the AVERAGE function to calculate the average growth rate of the bacterial growth using this formula in cell D16:
=AVERAGE(D6:D15)

It will give the average.

  • If the cells do not show percentages by default, for displaying the result in percentages, we select the result column and right-click.
  • Then we select Format Cells.

  • From the number box, we go to Percentages and put the 2 Decimal places.

how to calculate bacterial growth rate in excel with average percentage

  • The final result will look like this.

how to calculate bacterial growth rate in excel

📕 Read More: 5 Easy Methods to Add 10 Percent to a Number in Excel

Method 2

2. Inserting Chart for Display

We can make a curve by putting in the number of bacteria and the time at which they grew. Then, we’ll use an Excel command to make an equation that reflects the curve. From the equation, it’s easy to figure out how fast bacteria grow.

Excel generates the curve equation as follows:

Y = b*m^x

Where,

y = It is the total number of bacteria in the x given time.

b = Constant value.

m = Bacteria Growth Rate

⬇️⬇️ STEPS ⬇️⬇️

  • We start with selecting the whole datasheet or two columns.
  • Then we go to the Insert After that, we go to the Charts group and select the Scatter menu. You can select any style you want.

Selecting chart

  • From the graph, we select the curve line and right-click on it. Then we click Add Trendline…

  • From the Format Trendline, we click on Exponential in the Trendline Options. After that, we put a tick mark on Display Equation on the Chart We can modify the style of the equation by clicking on it.

  • Now we click on the plus mark and put a tick mark on the Chart Title and give a suitable title to it.

chart title

  • Finally, we changed the axis titles and get the result.

Axis title

From our equation (y = 7501.1e0.0599x ), We see the value of m is 0.0599 which indicates the growth rate of bacteria is 5.99%.

how to calculate bacterial growth rate in excel with chart

📕 Read More: 6 Ways to Forecast Growth Rate in Excel


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is a space where they can practice.


📝  Takeaways from This Article

📌 The reader will be able to use an exponential formula to figure out the bacterial growth rate in Excel.

📌 Readers can apply using a chart to show the growth rate.

📌 Finally, the readers will get familiar with the Excel chart systems.


Conclusion

That concludes today’s session. These are the methods for using Excel to calculate the bacterial growth rate. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.


Related Articles

(Visited 48 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo