Step 1 – Formatting Data in Excel to Calculate Cut and Fill Volume
- To make the data more understandable, we’ll format it appropriately.
- Assume that we’ve divided a place into sections, and each section has a uniform distance of 30 meters between them.
- If our reference point is 0 (zero), the first section would be 0+30 meters long.
- To format the distances, follow these steps:
- Select the range where you want to store these distances.
- Go to the Number menu and choose More Number Formats…
-
- In the Format Cells window, select Number and then choose Custom.
- Type 0+000 in the Type field.
- Enter the distance between the first two stations in cell B5 (you’ll see it displayed as Road (Rd)).
- Fill the lower cells with the distances (keeping them uniform is recommended).
Step 2 – Measuring Distance between Each Section
- In this step, we’ll calculate the distance between each pair of adjacent sections.
- Enter the following formula in cell C5:
=B6-B5
- Press ENTER to calculate the distance between the first two sections.
- Fill the lower cells to get the distances between every two sections.
Step 3 – Calculating Cut and Fill Volume Using Excel IF Function
Background:
- We’re calculating the Cut and Fill volume using two methods: the Average Area End method and the Pyramid method.
- If any cut or fill area becomes 0 for a section, we consider that section as a pyramid.
- Here are the formulas for both methods:
- Average Area End Method:
- Pyramid Method:
- Average Area End Method:
Where:
-
-
- V = Volume
- A1 & A2 = Areas of the adjacent two sections
- d = distance between two sections
-
Implementation:
- Create the necessary columns in your Excel sheet.
- In cell F6, enter the following formula:
=IF(D5*D6>0,(D5+D6)*C6/2,(D6+D5)*C6/3)
-
- This formula uses the IF function to return the volume of the space occupied by the first two sections.
-
- Press ENTER to see the fill volume in cell F6.
- Use the Fill Handle to AutoFill the lower cells. This will show you the fill volumes for every two adjacent sections.
- In cell G6, enter the following formula:
=IF(E5*E6>0,(E5+E6)*C6/2,(E6+E5)*C6/3)
-
- This operation returns all the Cut Volumes for every two sections.
- To calculate the volumes between every two places, enter the formula:
=F6-G6
- To determine the overall volume of that place, select a cell and enter:
=SUM(H5:H11)
- The SUM function will give you the total volume.
You can now calculate the Cut and Fill volume of a place using Microsoft Excel.
Read More: How to Calculate Volume in Excel
Practice Section
Below, you’ll find the dataset from this article. Feel free to use it for practicing the methods on your own:
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!