Creating a Unit Calculator with Google Sheets for Structural Steel

Are you tired of manually calculating structural steel measurements for billing purposes? Look no further! In this tutorial, we’ll show you how to create a unit calculator using drop-downs and an XLOOKUP function in Google Sheets. This tool will save you time and effort by swiftly computing the weight of structural steel based on your measurements. Plus, it’s completely customizable to fit your needs. Let’s dive in!

Structural Steel Unit Calculator Layout

To get started, let’s create the layout for the unit calculator:

1. In cells A1 to E1, set the row headers (field labels) as follows:

   • Designation
   • Section/Size
   • Unit
   • Length
   • Weight

2. In cells A2 to A4, enter the material descriptions: Beam, Channel, and Angle.

3. In cells C2 to C4, input “Mtr.” for the unit.

Now that we have the layout set up, we can move on to the next step: incorporating the formulas and sample data.

Sample Data: Section/Size and Unit Weight

Don’t worry about the section sizes of beams, channels, and angles. We’ve got you covered! Click on the button below to access a sample sheet with all the details, including the unit calculator itself.

Sample Sheet

In the sample sheet, you’ll find:

• Column G: Sizes of beams (Indian Standard Medium-weight Beams) from ISMB 100 to ISMB 600. Column H displays the corresponding unit weights.
• Column I: Sizes of channels (Indian Standard Medium-weight Channels) from ISMC 75 to ISMC 400. Column J displays the corresponding unit weights.
• Column K: Sizes of angles (Indian Standard Angles) from ISA 20X20X3 to ISA 200X200X25. Column L displays the corresponding unit weights.

Now, let’s set up the data validation drop-downs for the unit calculator.

The Data Validation Settings for the Unit Calculator

To make the unit calculator more user-friendly, we’ll create data validation drop-down menus for selecting the material sizes. Follow these steps:

1. In cell B2, click on Insert > Drop-down > Criteria > Drop-down from a range.

2. Enter G1:G in the field below.

3. Click Done.

4. Close the data validation sidebar panel by clicking the “x” button on the top right corner.

Repeat the above steps for cells B3 and B4, but use I1:I and K1:K as the validation fields, respectively.

Great! We’re almost there. Now, let’s incorporate the XLOOKUP formula to calculate the weight of the materials.

The XLOOKUP Formula for the Unit Calculator in Google Sheets

In cell E2, enter the following formula:

=ArrayFormula(XLOOKUP(B2:B4, VSTACK(G1:G, I1:I, K1:K), VSTACK(H1:H, J1:J, L1:L),)*D2:D4)

This formula will initially return 0 in cells E2:E4. To get the weight of the materials, select the material sizes in cells B2:B4 and enter the length in cells D2:D4.

Here’s a breakdown of the formula:

• B2:B4: The search keys (material sizes).
• VSTACK(G1:G, I1:I, K1:K): The lookup range that vertically appends all material sizes.
• VSTACK(H1:H, J1:J, L1:L): The result range that vertically appends all corresponding unit weights.

The XLOOKUP function searches for the search keys in the lookup range and returns the corresponding unit weights from the result range. The formula then multiplies the result by the length of the material in D2:D4 to calculate the weight.

Congratulations! You’ve successfully created the unit calculator. Now, simply change the length, and watch the weight of the corresponding item change before your eyes!

We hope this tutorial helps you streamline your structural steel calculations. If you have any questions or need further assistance, don’t hesitate to reach out to us at Crawlan.com. Happy calculating!