Some useful Excel formulas for civil engineers

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 the 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 the article Road Bar Charts with Auto Summary and check the output obtained from it.

Expressing Chainages 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, the desired output is as follows:

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

When Chainages are expressed in meters (m):-

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:

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

Let's say we have a set of chainages and we want to subtract them. If chainages are expressed in the form 0/00, 10/00, etc., we can substitute "/" with ".". For example, 0/00 becomes 0.00 and 10/00 becomes 10.00. Then subtraction can be done using standard Excel formulas.

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

Formula to Convert Chainage for Subtraction in Excel

Formula in Excel to do so is:

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

Interpolating Values

Let's say we have a 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 interpolate values in Excel:

=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)))

1. Linear Interpolation Using MATCH and INDEX

Suppose column D contains chainages and column E contains corresponding levels. To calculate the interpolated level for a specific chainage in F3, we use the formula:

=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)))

Explanation:

• MATCH(F3, D$2:D$600, 1) finds the largest chainage ≤ F3.
• INDEX(E$2:E$600, ...) returns the corresponding level for that chainage.
• The formula implements the standard linear interpolation formula:
  y = y0 + (x - x0) * (y1 - y0) / (x1 - x0)

2. Creating Abstract from Multiple Sheets

In project branches, data is often stored headwise across multiple sheets. We can summarize it into an abstract table.

Example formula to count number of entries from a specific sheet:

=COUNTA(INDIRECT("'" & A3 & "'!G5:G1000"))

• A3 contains the sheet name.
• INDIRECT converts text into a reference to dynamically fetch data.

3. Get Sheet Names Automatically

In Google Sheets:

function sheetnames() {
    var out = [];
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i = 0; i < sheets.length; i++) {
        out.push([sheets[i].getName()]);
    }
    return out;
}

In Excel VBA:

Sub preparingindex()
    Dim i As Integer, wSheet As Worksheet
    Sheet1.Range("A:A").Clear
    For i = 1 To Worksheets.Count
        Set wSheet = Worksheets(i)
        Sheet1.Cells(i, 1).Value = wSheet.Name
        Sheet1.Hyperlinks.Add Anchor:=Sheet1.Cells(i, 1), Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
    Next i
End Sub

By using =sheetnames() in Google Sheets, you can list all sheets dynamically. Then formulas like INDIRECT or COUNTA can summarize data across multiple sheets.

For more examples on auto summaries and Excel basics, see:

• Road Bar Charts with Auto Summary
• Excel Basics