This tutorial explains how to create a structural steel unit calculator using drop-downs and an XLOOKUP in Google Sheets. This tool is invaluable for generating measurement sheets for billing purposes.

If your Schedule of Rates (SOR) provides structural steel rates per kilogram instead of per meter, you can input your measurements in meters and the formula will swiftly compute the weight in kilograms.

This simple structural steel unit calculator includes three items: beams, channels, and angles, each available in various sizes. You can customize and expand the calculator by adding more items or replacing existing ones.

The sample data used in this example reflects the unit weight of structural steel based on Indian Standards. Please verify the weights for accuracy before incorporating them into your calculations.

You may need to adjust this unit weight based on the standards applicable in your country.

Note: This tutorial specifically guides you through creating a unit calculator for structural steel, which is different from a general unit converter. If your needs involve converting weight, volume, temperature, etc., Google Sheets offers a dedicated function called CONVERT for such conversions.

Structural Steel Unit Calculator in Google Sheets: How to Create One

Let’s start by creating the layout for the structural steel unit calculator. Once we’re done, we can proceed to the sample data.

Structural Steel Unit Calculator Layout

• In cells A1 to E1, set the row headers (field labels) as follows:
  • Designation
  • Section/Size
  • Unit
  • Length
  • Weight
• Enter the material descriptions —Beam, Channel, and Angle—in cells A2 to A4.
• In cells C2 to C4, input “Mtr.” for the unit.

With this setup complete, you’re ready to proceed to the next step: incorporating the formulas and sample data to establish the structural steel unit calculator.

Sample Data: Section/Size and Unit Weight

You don’t need to worry about the section sizes of the mentioned materials, including beams, channels, and angles. I’ve provided a sample sheet below with all the details, including the unit calculator itself.

Click on the button below to preview and make a copy of it:

Sample Sheet

You can find the following details in the sample sheet:

• Column G contains all the sizes of beams (Indian Standard Medium-weight Beams), ranging from ISMB 100 to ISMB 600. The next column, column H, contains the corresponding unit weights.
• Column I includes all the sizes of channels (Indian Standard Medium-weight Channels), with column J displaying the corresponding unit weights. Channel sizes range from ISMC 75 to ISMC 400.
• Column K lists all the sizes of angles (Indian Standard Angles), and column L displays the corresponding unit weights. Angle sizes range from ISA 20X20X3 to ISA 200X200X25.

The next step in creating the unit calculator involves setting up a few data validation drop-downs. I’ll explain how to do that in the following section.

The Data Validation Settings for the Unit Calculator

We need to create three data validation drop-down menus in the cell range B2:B4 to select the material sizes.

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 of the panel.

Repeat the above steps for cells B3 and B4. The only difference is that you need to enter I1:I and K1:K in the validation fields where we entered G1:G earlier.

Let’s move on to the next step in creating the structural steel unit calculator, which involves using an XLOOKUP formula.

The XLOOKUP Formula for the Unit Calculator in Google Sheets

You need to use the following formula in cell E2:

=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 E2:E4. You need to select material sizes in B2:B4 and enter the length in D2:D4 to get the weight of the materials.

Formula Explanation

Syntax of the XLOOKUP Function:

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Where:

• search_key: B2:B4
• lookup_range: VSTACK(G1:G, I1:I, K1:K). The VSTACK vertically appends all material sizes one by one.
• result_range: VSTACK(H1:H, J1:J, L1:L). The VSTACK vertically appends all corresponding unit weights one by one.

XLOOKUP searches the search keys in the lookup_range and returns the corresponding unit weights from the result_range.

Then the formula multiplies the result by the length of the material in D2:D4, which will be the weight of the material. If you want unit weight, simply put 1 in D2:D4.

You have finished creating the unit calculator. Change the length, and observe the weight of the corresponding item change!