write an excel formula for calculation of base (width) and height of excavated volume…
- User
write an excel formula for calculation of base (width) and height of excavated volume when the length is fixed. The base for bottom 8 feet is fixed at x feet. The base at 8 feet height from the bottom has step of 1 feet on each side making base x+2 feet wide and above 8 feet the height to base ratio is 4:1 (for 1 feet height the base will increase 0.25 feet) till the height reaches the value of that I input. I need two separate formula that can calculate base (width) and height at any point if I provide the total height. These formulas will help me in calculating the area at any point. For example if Input 7 feet height, the width will be x feet and height will be 7. If I input 13.58 height from the top then the base at 13.58 height will be x+2+1.395=x+3.395. Also, the payment of excavated material goes like this, the rate is fixed for 0-7 feet from the top, after the excavation of 0-7, the next rate bracket is 7-15, afterwards the rate is fixed for any depth above 15. I know the length, I can tell the total depth (height), i will need to calculate base and height at every rate bracket i.e. 0-7,7-15,15 onwards.
Assistant
Okay, let's break down the geometry and create the Excel formulas.
The excavation profile from the bottom up is:
- 0 to 8 feet height: Base width is fixed at
xfeet. - At exactly 8 feet height: The base steps out by 1 foot on each side, making the width
x + 2feet. - Above 8 feet height: The side slopes outwards with a height:base ratio of 4:1. This means for every 1 foot increase in height, the total width increases by 0.25 feet (0.125 feet on each side). This slope starts from the
x + 2width at the 8-foot level.
We need two main formulas that depend on the
TargetHeightFromBottom. Let's assume:x(the fixed base width) is in cellB1.TotalHeight(the total depth of the excavation) is in cellB2.- The
TargetHeightFromBottomyou want to check is in cellD10.
Formula 1: Calculate Base Width at a specific Height from the Bottom
This formula checks if the target height is within the fixed base section (<= 8 ft) or the sloped section (> 8 ft) and calculates the width accordingly.
- If
TargetHeightFromBottomis less than 8: The width isx. - If
TargetHeightFromBottomis 8 or more: The width isx + 2(the step) plus the increase due to the slope above 8 feet. The height above 8 feet isTargetHeightFromBottom - 8. The width increase is(TargetHeightFromBottom - 8) * 0.25. Total width is(x + 2) + (TargetHeightFromBottom - 8) * 0.25.
Place this formula in a cell, for example,
D11:=IF(D10 < 8, $B$1, ($B$1 + 2) + (D10 - 8) * 0.25)D10is the cell with theTargetHeightFromBottom.$B$1is the cell with the value ofx(using$makes it an absolute reference, good practice for input cells).- The formula correctly handles the case where
D10is exactly 8, resulting in($B$1 + 2) + (8 - 8) * 0.25 = $B$1 + 2, which is the width at the step.
Formula 2: Calculate Height at a specific Height from the Bottom
This is much simpler. The "height at a specific point" from the bottom is simply that specific height value itself.
Place this formula in a cell, for example,
D12:=D10D10is the cell with theTargetHeightFromBottom.
Example Usage for Formulas 1 & 2:
Let's say
x = 5(inB1) and you want to find the width and height at 7 feet from the bottom (D10 = 7).- Formula 1 (in D11):
=IF(7 < 8, $B$1, ...)->=IF(TRUE, 5, ...)->5. Width = 5 feet. - Formula 2 (in D12):
=7. Height = 7 feet.
Now, let's say you want to find the width and height at 13.58 feet from the bottom (
D10 = 13.58). Assumex = 5.- Formula 1 (in D11):
=IF(13.58 < 8, $B$1, ($B$1 + 2) + (13.58 - 8) * 0.25)->=IF(FALSE, 5, (5 + 2) + (5.58) * 0.25)->7 + 1.395 = 8.395. Width = 8.395 feet. - Formula 2 (in D12):
=13.58. Height = 13.58 feet.
This matches your example (
x + 2 + 1.395 = 5 + 2 + 1.395 = 8.395).
Calculating Dimensions for Rate Brackets (Defined from the Top)
The rate brackets are defined from the top of the excavation. To use the formulas above which work from the bottom, we need to convert the bracket boundaries from "feet from top" to "feet from bottom" using the
TotalHeight.Let
TotalHeightbe in cellB2.- A depth of
dfeet from the top is equivalent toTotalHeight - dfeet from the bottom.
Let's set up a table to calculate the relevant heights (from the bottom) and widths for each bracket boundary. We'll use
B1forxandB2forTotalHeight.Description Rate Bracket (from Top) Corresponding Height (from Bottom) Width at this Height Formula (Based on cell in prev column) Width at this Height Value Notes Bracket 1: 0-7 ft Top This bracket covers depth from 0 to 7 ft from the surface. Surface (Top of 0-7) 0 ft from Top =$B$2=IF(C17<8, $B$1, ($B$1+2) + (C17-8)*0.25)(Calculated value) Use this height for the top boundary of this bracket. Bottom of 0-7 ft bracket 7 ft from Top =MAX(0, $B$2-7)=IF(C18<8, $B$1, ($B$1+2) + (C18-8)*0.25)(Calculated value) Use this height for the bottom boundary of this bracket. Height cannot be less than 0 ft from bottom. Bracket 2: 7-15 ft Top This bracket covers depth from 7 to 15 ft from the surface. Top of 7-15 ft bracket 7 ft from Top =MAX(0, $B$2-7)=IF(C20<8, $B$1, ($B$1+2) + (C20-8)*0.25)(Calculated value) Same height as "Bottom of 0-7 ft bracket". Bottom of 7-15 ft bracket 15 ft from Top =MAX(0, $B$2-15)=IF(C21<8, $B$1, ($B$1+2) + (C21-8)*0.25)(Calculated value) Use this height for the bottom boundary of this bracket. Height cannot be less than 0 ft from bottom. Bracket 3: >15 ft Top This bracket covers depth > 15 ft from the surface, down to the bottom. Top of >15 ft bracket 15 ft from Top =MAX(0, $B$2-15)=IF(C23<8, $B$1, ($B$1+2) + (C23-8)*0.25)(Calculated value) Same height as "Bottom of 7-15 ft bracket". Very Bottom of Excavation TotalHeight from Top =0=IF(C24<8, $B$1, ($B$1+2) + (C24-8)*0.25)(Calculated value - this will always be 1) This is the 0 ft height from the bottom. How to Use This Table:
- Enter
xinB1andTotalHeightinB2. - Set up the table structure in your sheet, starting the "Corresponding Height (from Bottom)" column in, say, cell
C17. - Enter the formulas from the "Corresponding Height (from Bottom)" column into your sheet.
- Enter the general width formula, referencing the cell in the same row from the "Corresponding Height (from Bottom)" column. For example, in cell
D17, enter=IF(C17<8, $B$1, ($B$1+2) + (C17-8)*0.25). - Drag the width formula down for all rows in the table.
This table will automatically calculate the heights from the bottom and the corresponding widths at the transition points for your rate brackets, based on the total depth of the excavation.
Important Notes for Rate Brackets:
- If the
TotalHeightis less than 7 feet, the "0-7 ft from Top" bracket will be shorter than 7 feet, and the other brackets won't exist. TheMAX(0, ...)in the formulas correctly handle the heights from the bottom in these cases. The actual height of the top bracket would beMIN(7, TotalHeight). The actual height of the middle bracket isMAX(0, TotalHeight-7) - MAX(0, TotalHeight-15). The actual height of the bottom bracket isMAX(0, TotalHeight-15). - To calculate the volume for each bracket, you would use the length (which is fixed, let's say it's in cell
B3), the height of the segment (calculated as the difference between the top and bottom heights for that segment from the bottom), and the widths at the top and bottom of the segment (calculated in your table). A common method is the Average End Area method:Volume = Segment Height * (Area at Top + Area at Bottom) / 2, whereArea = Length * Width. So,Volume Segment = Segment Height * (Length * Width_Top + Length * Width_Bottom) / 2 = Segment Height * Length * (Width_Top + Width_Bottom) / 2.
Let me know if you'd like help setting up the volume calculation for each bracket as well!
- 0 to 8 feet height: Base width is fixed at
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.