Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash-preview-04-17· GoogleHugging Face LMArena Human Preference 140K

write an excel formula for calculation of base (width) and height of excavated volume…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-opus-4-20250514’s answer.
  1. 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.

  2. Gemini Assistant

    Okay, let's break down the geometry and create the Excel formulas.

    The excavation profile from the bottom up is:

    1. 0 to 8 feet height: Base width is fixed at x feet.
    2. At exactly 8 feet height: The base steps out by 1 foot on each side, making the width x + 2 feet.
    3. 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 + 2 width 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 cell B1.
    • TotalHeight (the total depth of the excavation) is in cell B2.
    • The TargetHeightFromBottom you want to check is in cell D10.

    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 TargetHeightFromBottom is less than 8: The width is x.
    • If TargetHeightFromBottom is 8 or more: The width is x + 2 (the step) plus the increase due to the slope above 8 feet. The height above 8 feet is TargetHeightFromBottom - 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)
    
    • D10 is the cell with the TargetHeightFromBottom.
    • $B$1 is the cell with the value of x (using $ makes it an absolute reference, good practice for input cells).
    • The formula correctly handles the case where D10 is 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:

    =D10
    
    • D10 is the cell with the TargetHeightFromBottom.

    Example Usage for Formulas 1 & 2:

    Let's say x = 5 (in B1) 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). Assume x = 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 TotalHeight be in cell B2.

    • A depth of d feet from the top is equivalent to TotalHeight - d feet 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 B1 for x and B2 for TotalHeight.

    DescriptionRate Bracket (from Top)Corresponding Height (from Bottom)Width at this Height Formula (Based on cell in prev column)Width at this Height ValueNotes
    Bracket 1: 0-7 ft TopThis 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 bracket7 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 TopThis bracket covers depth from 7 to 15 ft from the surface.
    Top of 7-15 ft bracket7 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 bracket15 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 TopThis bracket covers depth > 15 ft from the surface, down to the bottom.
    Top of >15 ft bracket15 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 ExcavationTotalHeight from Top=0=IF(C24<8, $B$1, ($B$1+2) + (C24-8)*0.25)(Calculated value - this will always be BB1)This is the 0 ft height from the bottom.

    How to Use This Table:

    1. Enter x in B1 and TotalHeight in B2.
    2. Set up the table structure in your sheet, starting the "Corresponding Height (from Bottom)" column in, say, cell C17.
    3. Enter the formulas from the "Corresponding Height (from Bottom)" column into your sheet.
    4. 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).
    5. 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 TotalHeight is less than 7 feet, the "0-7 ft from Top" bracket will be shorter than 7 feet, and the other brackets won't exist. The MAX(0, ...) in the formulas correctly handle the heights from the bottom in these cases. The actual height of the top bracket would be MIN(7, TotalHeight). The actual height of the middle bracket is MAX(0, TotalHeight-7) - MAX(0, TotalHeight-15). The actual height of the bottom bracket is MAX(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, where Area = 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!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.