Some useful Excel formulas for civil engineers

yogendra borse

Acquiring skills increases value of person, so never stop to learn new skills and keep on refining acquired skills.

I have written many basic formulas with explanation in article Excel Basics article, here i am writing some formulas specially modified for civil engineering. One can observe how much useful these formulas can be, just read article Road Bar Charts with Auto Summary and output obtained from it.

Expressing Chaimages in Chainage Format in excel

When Chainages are expressed in meters (m):- 

like in case of road cross sections taken at interval of 10 m. then, desired output is as follows

0	0/000
12	0/012
1234	1/234
12345	12/345
123456	123/456

Formula to Do so is "=INT(B3/1000)&"/"&TEXT(B3-INT(B3/1000)*1000,"000")"

When Chainages are expressed in Kilometers (km):- 

for desired output is as follows

0.00	0/000
1.2	1/200
12.34	12/340
123.45	123/450
1234.56	1234/560

Formula to Do so is "=INT(B10)&"/"&TEXT((B10-INT(B10))*1000,"000")"

Subtraction of Chainages 

Lets say we have set of chainages and we want to subtract those chainages, if chainages are expressed in 0/00, 10/00 in this form then in excel we can substitute "/" with "." then 0/00 becomes 0.00 and 10/00 becomes 10.00 then we can do subtraction, in excel formula as follows.

Subtraction of Chainages
0/00	13/00	13
23/348	90/758	67.41
101/659	258/368	156.709

formula in excel to do so is

=SUBSTITUTE(C17,"/",".")-SUBSTITUTE(B17,"/",".")

Interpolating values 

Lets say we have set of levels at different chainages as follows

Chainages	Levels
0	391.9
5.39	392.9
12.23	393.7
16.29	394.8
21.18	395.5
24.68	396.4
30.59	397.1
35.90	398.3
41.59	399.4
48.81	400.5
55.24	401.7
63.44	402.7
70.17	403.6
78.92	404.2
94.14	405.2
101.90	407.5

Formula to do so is "=INDEX($E$2:$E$600, MATCH(F3, $D$2:$D$600, 1)) + (F3 - INDEX($D$2:$D$600, MATCH(F3, $D$2:$D$600, 1))) * (INDEX ($E$2:$E$600, MATCH(F3, $D$2:$D$600, 1) + 1) - INDEX($E$2:$E$600, MATCH(F3, $D$2:$D$600, 1))) / (INDEX($D$2:$D$600, MATCH(F3, $D$2:$D$600, 1) + 1) - INDEX($D$2:$D$600, MATCH(F3, $D$2:$D$600, 1)))"

in above formula, D colomn contains chainages, E column contains Levels. F3 is the Value of chainage for which you need to calculate interpolated level as shown in below picture.

Creating Abstract of Multiple sheets 

Generally in project branch (PB), headwise data is kept so we need to summarise as abstract, shhown as follows

 

or as 

Example :- =COUNTA(INDIRECT("'"&A3&"'!G5:G1000")) counts number of bridges data filled in Peth Subdivision, As A3 cell contains name of sheet meant to be collect data from peth subdivision bridges.

Get Sheet name automatically

it can be done on google sheets using following code

function sheetnames() 

{

  var out = new Array()

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )

  return out 

}

it can be done on Excel using following code

Sub preparingindex()

        Dim i As Integer

        Dim sheetname As String

        Dim numofsheets As Integer

        Dim wSheet As Worksheet

        

        numofsheets = Worksheets.Count

        Sheet1.Activate

        Range("A:A").Select

        Selection.Clear

        Range("a1").Select

        For i = 1 To numofsheets

        ActiveCell.Offset(1, 0).Activate

        ActiveCell.Value = Worksheets(i).Name

        Set wSheet = Worksheets(i)

        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name

        Next i

        

    

End Sub

and by giving formula as "=sheetnames()" list of all sheets in documents appears.

then we can use indirect formula to count, sum by various conditions, for more info read Road Bar Charts with Auto Summary and Excel Basics article.