We can create unit calculator using Google Doc spreadsheet for our day to day spreadsheet purposes. It can ease our job. You can create many types of unit calculators for your purpose. Right now I have some data of structural steel unit weight. I am using this data to create a structural steel unit weight calculator on the Google Doc Spreadsheet. You can apply the same method for creating any other unit calculators.
We used the data validation feature and sumif function for creating our unit calculator. If you find any problem to understand the tutorial, you can reach me by posting your queries below in the comments.
How to Create Unit Calculator Using Google Doc Spreadsheet?
Enter the below data in a new spreadsheet. Name the file “strl unit calculator”. Here after in this tutorial, I will use this file name to mention the sheet wherever required.
When you type the data keep the cell address same as per the below screenshot.
Now we need some data. We are preparing the structural steel unit weight calculation tool. I have some real looking data for you. It may take your few minutes to type. So I have prepared a spreadsheet named “data for unit calculator” for you to copy the content.
You can follow this link to copy the file. I’ve applied a force copy technique to make this spreadsheet file in copy mode. So that you can copy the file for your use without altering my original. Open the file and copy the content spread across the data range in G1:L72. You should paste this content in same ranges in your file “strl unit calculator”.
Now you should set the below data validation rules in Cell B2, B3, and B4 to create a drop down list. Data validation you can access from the Data menu.
The below is the data validation rule for Cell B2.
In Cell B3 just change the criteria range to “Sheet1!I1:I11”. Similarly in Cell B4 change the range to “Sheet1!K1:K72”.
The drop down menu for unit calculator is ready. Now to the finishing step. Here you should apply the sumif function in Cell E2, E3 and E4. Find the formula below one by one.
Important Note: Do not copy the formula from here. Just type it and remove the quotes.
In Cell E5 type the formula “=D5*7.85″ because it’s a single item.
You have finished creating the calculator. Change the length and see the weight of the corresponding item changes!
The sample data I’ve used in this file contain structural steel unit weight but based on Indian Standard. This may also wary. If you use it in real purpose, change this unit weight according to your country’s standard.
This unit calculator can be used to prepare measurement sheets for billing in mechanical as well as civil engineering works.