How to Calculate Cut and Fill Volume in Excel (3 Steps)

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.

how to calculate cut and fill volume in excel

  • 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.

how to calculate cut and fill volume in excel

  • 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).

how to calculate cut and fill volume in excel


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.

how to calculate cut and fill volume in excel

  • 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:
      how to calculate cut and fill volume in excel
    • Pyramid 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.

how to calculate cut and fill volume in excel

    • 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.

how to calculate cut and fill volume in excel

  • 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

how to calculate cut and fill volume in excel

  • To determine the overall volume of that place, select a cell and enter:

=SUM(H5:H11)

how to calculate cut and fill volume in excel

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!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo