Meraz Al Nahian
The article will show you how to calculate cut and fill volume in Excel. If you are a survey engineer, it will be very important for you to measure the area of a place or the volume of an earth section. The measurement of the area is important in the real estate business too. Also, if you are a civil engineer and you have a project to construct a building, you will need to measure the volume of the foundation that the building needs. So, our dedication to this article is to help you in this field.
Download Practice Workbook
What Is Cut and Fill Volume?
First, we’ll be describing to you briefly what the Cut and Fill volume is. You know, the surface of the earth is not uniform everywhere. In a certain area, some portions might be in a high position, some portions can be low. If the upper surface remains above the lower surface, then the volume between the upper and lower surface is called the Cut Volume. If the lower surface is on the upper surface, then we call the volume between the upper and lower surface the Fill Volume. The summation of these volumes will give us the actual volume of that place.
3 Steps to Calculate Cut and Fill Volume in Excel
Suppose, we divided a place into some sections and each section has a distance of 30 meters between them. We also have survey data about the Cut & Fill area of those sections. The dataset of this article depicts this information.
Step-1: Formatting Data in Excel to Calculate Cut and Fill Volume
We will format our data in this section so that anyone can understand that this is a calculation of Cut and Fill volume.
- The distance between every station is 30 meters. It is convenient to keep the distance uniform. So if your reference point is 0 (zero), we can say that the first section is 0+030 meters long. To convert the number to this format, select a range that you need to store these distances and then go to the Number
- After that, select More Number Formats…
- In the Format Cells window, select Number >> Custom.
- Type 0+000 in the Type field.
- Next, type the distance between the first two stations in cell B5. You will see the distance in Road (Rd)
- Thereafter, fill the lower cells with the distances. You may use non-uniform values if you want, but we suggest you to keep them uniform.
Step-2: Measuring Distance between Each Section
In this section, we will simply calculate the distance between the two sections.
- Type the following formula in cell C5.
=B6-B5
- After that, press the ENTER button and you will see the distance between first two sections.
- Later, fill the lower cells.
Thus, you will get the distances between every two sections.
Step-3: Calculating Cut and Fill Volume Using Excel IF Function
We are calculating the Cut and Fill volume using the Average Area End and Pyramid methods. If any of the cut and fill areas becomes 0 for a section, then we can consider that section as a pyramid. The formula for the Average Area End method is given below.
And below here, you will see the formula for the Pyramid method.
Where, V = Volume
A1 & A2 = Areas of the adjacent two sections
d = distance between two sections
- Following this concept, make some necessary columns and type the following formula in cell F6.
=IF(D5*D6>0,(D5+D6)*C6/2,(D6+D5)*C6/3)
The formula here uses the IF Function and returns the volume of the space occupied by the first two sections.
- After that, press the ENTER key and you will see the fill volume in cell F6.
- Next, use the Fill Handle to AutoFill the lower cells.
This operation will show you all the fill volumes of every two sections.
- Now, type the following formula in cell G6, hit ENTER, and fill the lower cells of column G.
=IF(E5*E6>0,(E5+E6)*C6/2,(E6+E5)*C6/3)
This operation returns all the Cut Volumes of every two sections.
- Thereafter, use the following formula to calculate the volumes between every two places.
=F6-G6
- Next, to determine the exact volume of that place, select a cell to store it and type the following formula.
=SUM(H5:H11)
The formula uses the SUM Function which will show you the overall volume of that place.
Thus you can calculate the Cut and Fill volume of a place using Microsoft Excel.
Read More: How to Calculate Column Volume in Excel (with Quick Steps)
Practice Section
Here, we’re giving you the dataset of this article so that you can practice these methods on your own.
Conclusion
The bottom line is, you will learn a simple idea of how to calculate the Cut and Fill volume of a certain place. The method that I showed in my article to calculate it is pretty simple. You may need to use a complex procedure if you want accurate measurement. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
