Time (days) DO Deficit (mg/L) BOD (mg/L) D Saturation (mg/L) 0 4.8531267 0 11.8 1…
- User
Time (days) DO Deficit (mg/L) BOD (mg/L) D Saturation (mg/L) 0 4.8531267 0 11.8 1 4.374886 3.379314 11.8 2 3.921396 6.646798 11.8 3 3.726639 8.95721 11.8 4 3.469271 11.201694 11.8 5 3.095646 13.369197 11.8 6 2.711171 15.478202 11.8 7 2.291464 17.475697 11.8 8 1.905049 19.392453 11.8 9 1.573087 21.206602 11.8 10 1.276978 22.979827 11.8 11 1.069378 24.658221 11.8 12 0.810507 26.148802 11.8 13 0.573198 27.575648 11.8 14 0.382703 28.922258 11.8 15 0.248826 30.215279 11.8 16 0.123396 31.428123 11.8 17 0.036846 32.586973 11.8 18 0.025103 33.698167 11.8 19 0.015757 34.755354 11.8 20 0.010061 35.774726 11.8 21 0.006646 36.759231 11.8 22 0.004238 37.709411 11.8 23 0.002771 38.629163 11.8 24 0.001836 39.519324 11.8 25 0.001266 40.38093 11.8 26 0.000851 41.215126 11.8 27 0.00057 42.022125 11.8 28 0.000376 42.80397 11.8 29 0.000249 43.561373 11.8 30 0.000166 44.295706 11.8 provide me excel sheet Based on the image you shared, it looks like you're calculating the Dissolved Oxygen (DO), BOD (Biochemical Oxygen Demand), and related values in Excel. The formula for calculating the DO deficit and BOD is already implemented there. Here's a breakdown to help guide you through:
DO Deficit Calculation: It seems you're using an equation like this:
𝐷 ( 𝑡 )
( 𝑘 1 𝑘 2 − 𝑘 1 ) [ 𝑒 − 𝑘 1 𝑡 − 𝑒 − 𝑘 2 𝑡 ] 𝐿 0 + 𝐷 0 𝑒 − 𝑘 2 𝑡 D(t)=( k 2 −k 1
k 1
)[e −k 1 t −e −k 2 t ]L 0 +D 0 e −k 2 t
In your Excel sheet, column D might be representing this.
BOD Calculation: The BOD curve calculation:
𝐵 𝑂 𝐷 ( 𝑡 )
𝐿 0 ( 1 − 𝑒 − 𝑘 1 𝑡 ) BOD(t)=L 0 (1−e −k 1 t ) seems to be in another column (possibly column H or I from what I see in the image).
Superimposing the Curves: To plot both the DO Deficit and BOD curves on the same plot in Excel:
Highlight the data for time (column A), DO Deficit (possibly column D or F), and BOD (column G or H). Go to the Insert tab, choose Line Chart and select a 2D Line Chart. Both curves should appear on the same graph, which will let you compare them over the 30-day period.
- Assistant
Certainly! I can help you create an Excel sheet to calculate and visualize Dissolved Oxygen (DO) Deficit, Biochemical Oxygen Demand (BOD), and related parameters over a 30-day period. Below is a step-by-step guide, including the necessary data, formulas, and instructions to create charts for analysis.
Step 1: Setting Up the Excel Sheet
-
Open Excel and create a new workbook.
-
Create Column Headers in Row 1 as follows:
A B C D Time (days) DO Deficit (mg/L) BOD (mg/L) D Saturation (mg/L) -
Input Your Data starting from Row 2 down to Row 31 (for 0 to 30 days). Here's the data you provided:
Time (days) DO Deficit (mg/L) BOD (mg/L) D Saturation (mg/L) 0 4.8531267 0 11.8 1 4.374886 3.379314 11.8 2 3.921396 6.646798 11.8 3 3.726639 8.95721 11.8 4 3.469271 11.201694 11.8 5 3.095646 13.369197 11.8 6 2.711171 15.478202 11.8 7 2.291464 17.475697 11.8 8 1.905049 19.392453 11.8 9 1.573087 21.206602 11.8 10 1.276978 22.979827 11.8 11 1.069378 24.658221 11.8 12 0.810507 26.148802 11.8 13 0.573198 27.575648 11.8 14 0.382703 28.922258 11.8 15 0.248826 30.215279 11.8 16 0.123396 31.428123 11.8 17 0.036846 32.586973 11.8 18 0.025103 33.698167 11.8 19 0.015757 34.755354 11.8 20 0.010061 35.774726 11.8 21 0.006646 36.759231 11.8 22 0.004238 37.709411 11.8 23 0.002771 38.629163 11.8 24 0.001836 39.519324 11.8 25 0.001266 40.38093 11.8 26 0.000851 41.215126 11.8 27 0.00057 42.022125 11.8 28 0.000376 42.80397 11.8 29 0.000249 43.561373 11.8 30 0.000166 44.295706 11.8 Tip: To quickly input the data, you can copy the table above and paste it into your Excel sheet starting from cell A1.
Step 2: Calculating DO Deficit and BOD
From your data, it appears the DO Deficit and BOD values are already calculated. However, if you need to implement the formulas for future calculations or to understand the methodology, here's how you can set them up:
1. DO Deficit Calculation
The DO Deficit can be calculated using the following formula:
[ D(t) = \left( \frac{k_2 - k_1}{k_1} \right) \left[ e^{-k_1 t} - e^{-k_2 t} \right] L_0 + D_0 e^{-k_2 t} ]
Where:
- ( D(t) ) = DO Deficit at time ( t )
- ( k_1 ) and ( k_2 ) = Deoxygenation rates (constants)
- ( L_0 ) = Ultimate BOD
- ( D_0 ) = Initial DO Deficit
Implementation in Excel:
Assuming:
- ( k_1 = 0.04 ) day⁻¹
- ( k_2 = 0.02 ) day⁻¹
- ( L_0 = 44.295706 ) mg/L (from day 30)
- ( D_0 = 4.8531267 ) mg/L (from day 0)
You can place these constants in separate cells for easy reference:
- Cell F1:
k1= 0.04 - Cell F2:
k2= 0.02 - Cell F3:
L0= 44.295706 - Cell F4:
D0= 4.8531267
Then, in Cell B2 (DO Deficit for day 0), input:
=D0 * EXP(-$F$2 * A2)Drag this formula down to Cell B31 for all days.
2. BOD Calculation
The BOD can be calculated using the first-order decay equation:
[ \text{BOD}(t) = L_0 \left( 1 - e^{-k_1 t} \right) ]
Implementation in Excel:
In Cell C2 (BOD for day 0), input:
=$F$3 * (1 - EXP(-$F$1 * A2))Drag this formula down to Cell C31 for all days.
Note: Adjust ( k_1 ), ( k_2 ), and ( L_0 ) based on your specific scenario or dataset.
Step 3: Visualizing the Data with Charts
To compare the DO Deficit and BOD over the 30-day period, you can create a combined line chart.
1. Creating the Line Chart
-
Select the Data:
- Highlight the range from A1 to C31 (including headers).
-
Insert a Line Chart:
- Go to the Insert tab on the Ribbon.
- Click on Insert Line or Area Chart.
- Choose Line with Markers for better visibility.
-
Customize the Chart:
- Chart Title: Click on the chart title and rename it to "DO Deficit and BOD Over Time".
- Axes Titles:
- Horizontal Axis (X-Axis): Days
- Vertical Axis (Y-Axis): Concentration (mg/L)
- Legend: Ensure that both "DO Deficit" and "BOD" are clearly labeled.
2. Adding D Saturation to the Chart (Optional)
If you wish to include D Saturation in your analysis:
-
Select Additional Data:
- You might need to create a separate Y-axis for D Saturation if its scale differs significantly.
- Select column D (D Saturation) along with Time.
-
Add to Existing Chart:
- Right-click on the chart and choose Select Data.
- Click Add to include the D Saturation series.
- Assign it to a Secondary Axis for clarity.
Example Excel Layout
Here's an illustrative layout of how your Excel sheet should appear:
A B C D E F Time (days) DO Deficit (mg/L) BOD (mg/L) D Saturation (mg/L) 0 4.8531267 0 11.8 1 4.374886 3.379314 11.8 2 3.921396 6.646798 11.8 ... ... ... ... 30 0.000166 44.295706 11.8 Constants k1 0.04 k2 0.02 L0 44.295706 D0 4.8531267 Note: Cells F1 to F4 hold your constants (
k1,k2,L0,D0). You can adjust these based on your specific requirements.Step 4: Final Touches
- Data Validation: Ensure all formulas are correctly applied and that the data aligns accurately.
- Formatting: Use cell formatting to set appropriate number formats (e.g., two decimal places for concentrations).
- Documentation: It’s good practice to add comments or a separate sheet explaining the formulas and constants used for future reference.
Additional Tips
- Dynamic Analysis: By setting up your constants (k1, k2, L0, D0) in separate cells, you can easily adjust them and see real-time updates in your calculations and charts.
- Sensitivity Analysis: You can explore how changes in deoxygenation rates or initial conditions affect DO Deficit and BOD over time.
- Advanced Visualization: Consider adding trendlines or using different chart types for enhanced data representation.
By following this guide, you'll have a comprehensive Excel sheet that not only tracks DO Deficit and BOD over time but also allows for insightful visual analysis through charts. If you need further customization or assistance with specific features in Excel, feel free to ask!
-