Method to calculate interpolation step value in Excel

Summary

The following Microsoft Excel formula performs linear interpolation by calculating the interpolation step value

=(end-start)/(ROW(end)-ROW(start))

where end is the cell address of the larger number, and start is the cell address of the smaller number. 

Interpolation is a method used to determine a present or future value factor when the exact factor does not appear in either a present or future value table. Interpolation assumes that the change between two values is linear and that the margin of error is insignificant.

More information

To create a sample linear interpolation formula, follow these steps:

1.       Type the following values in a worksheet:

2.     A1: 9          B1: =(A7-A1)/(ROW(A7)-ROW(A1))

3.     A2: =A1+$B$1

4.     A3:

5.     A4:

6.     A5:

7.     A6:

8.     A7: 11

9.       Select cells A2:A6. On the Edit menu, point to Fill, and then click Down. The formula is filled down, and the following values are displayed in cells A2:A6:

10.   A2: 9.33333

11.   A3: 9.66667

12.   A4: 10.

13.   A5: 10.33333

14.   A6: 10.66667

NOTE: You must type the reference to the step value in cell B1 as an absolute reference (with dollar signs).

Lesson Note On Contours- Map preparation

Lesson Note On Contours- Map preparation

The art of determining relative altitudes of points on the surface of the earth of beneath the surface of earth is called LEVELLING.

A contour is defined as an imaginary line of constant elevation on the ground surface. It can also be defined as the line of intersection of a level surface with the ground surface. For example, the line of intersection of the water surface of a still lake or pond with the surrounding ground represents a contour line.

Indirect method of contouring:

In this method, the spot levels of selected guide points are taken with a level and their levels are computed. The horizontal positions of these points are measured or computed and the points are plotted on the plan. The contours are then drawn by a process called interpolation of contours from the levels of the guide points. The following are the indirect methods are commonly used for locating contours.

1.     Squares or Grid method

2.     Cross section method

Square or grid method:

In this method, the area to be surveyed is divided into a grid or series of squares. The grid size may vary from 5 m x 5 m to 25 m x 25 m depending upon the nature of the terrain, the contour interval required and the scale of the map desired. Also, the grids may not be of the same size throughout but may vary depending upon the requirement and field conditions. The grid corners are marked on the ground and spot levels of these comers are determined by leveling. The grid is plotted to the scale of the map and the spot levels of the grid corners are entered. The contours of desired values are then located by interpolation. Special care should be taken to give the spot levels to the salient features of the ground such as hilltops, deepest points of the depressions, and their measurements from respective corners of the grids, for correct depiction of the features. The method is used for large scale mapping and at average precision. 

Cross section method:

In these sections, a base line, centre line or profile line is considered.  Cross sections are taken perpendicular to this line at regular intervals.  After this points are marked along the cross sections at regular intervals.  A temporary bench mark is set up near the site.  Staff readings are taken along the base line and the cross sections. The readings are entered in the level book the base line and the cross sections should also be mentioned.  The RL of each of the points calculated.  Then the base line and cross sections are plotted to a suitable scale.  Subsequently the RLs of the respective points are noted on the map, after which the required contour line is drawn by interpolation

This method is suitable for route survey, when cross sections are taken transverse to the longitudinal section.

Method of interpolation of contours:

The process of locating the contours proportionately between the plotted points is termed interpolation.  Interpolation may be done by:

1.     Arithmetical calculation

2.     The graphical method

By arithmetical calculation

Let A and B be two corners of the squares.  The RL of A is 98.75 m, and that of B 100.75 m.  the horizontal distance between A and B is 10m.

Horizontal distance between A and B = 10m

Vertical difference A and B = 100.75-98.75=2m

Let a contour of 99.00 m be required.  Then,

Difference of level between A and 99.00m contour = 99.00-98.75=0.25m

Therefore, distance of 99.00 m contour line form A= 10/2 *0.25=1.25m

This calculated distance is plotted to the same scale in which the skeleton was plotted to obtain a point of RL of 99.00 m.

Similarly, the other points can be located.

By graphical method

On a sheet of tracing paper, a line AB is drawn and divided into equal parts.  AB is bisected at C and a perpendicular is drawn at this point.  A point O is selected on this perpendicular line and then radial lines are drawn from O to the divisions on AB.  After this lines 1-1, 2-2, 3-3….are drawn parallel to AB.  These lines serve as guide lines.  Boundary line and every fifth the line is marked with a thick or red line.

Suppose we have to interpolate a 2m contour between two points a and b of RLs 92.5 and 100.75m.

Let us consider the lowest radial line OB to represent an RL of 90.00. So, every fifth line will represent 95,100,105, etc.  The tracing paper is moved over the plan until ‘a’ lies at 92.5 and ‘b’ at 100.25. Line ‘ab’ should be parallel to AB.  Now the points 94, 96, 98,100 are picked through to obtain the positions of the required contours.

Method of interpolation of contours:

The process of locating the contours proportionately between the plotted points is termed interpolation.  Interpolation may be done by:

1.     Arithmetical calculation

2.     The graphical method

By arithmetical calculation

Let A and B be two corners of the squares.  The RL of A is 98.75 m, and that of B 100.75 m.  the horizontal distance between A and B is 10m.

Horizontal distance between A and B = 10m

Vertical difference A and B = 100.75-98.75=2m

Let a contour of 99.00 m be required.  Then,

Difference of level between A and 99.00m contour = 99.00-98.75=0.25m

Therefore, distance of 99.00 m contour line form A= 10/2 *0.25=1.25m

This calculated distance is plotted to the same scale in which the skeleton was plotted to obtain a point of RL of 99.00 m.

Similarly, the other points can be located.

By graphical method

On a sheet of tracing paper, a line AB is drawn and divided into equal parts.  AB is bisected at C and a perpendicular is drawn at this point.  A point O is selected on this perpendicular line and then radial lines are drawn from O to the divisions on AB.  After this lines 1-1, 2-2, 3-3….are drawn parallel to AB.  These lines serve as guide lines.  Boundary line and every fifth the line is marked with a thick or red line.

Suppose we have to interpolate a 2m contour between two points a and b of RLs 92.5 and 100.75m.

Let us consider the lowest radial line OB to represent an RL of 90.00. So, every fifth line will represent 95,100,105, etc.  The tracing paper is moved over the plan until ‘a’ lies at 92.5 and ‘b’ at 100.25. Line ‘ab’ should be parallel to AB.  Now the points 94, 96, 98,100 are picked through to obtain the positions of the required contours.

CONCRETE MIX DESIGN PROCEDURE & EXAMPLE IS456

CONCRETE MIX DESIGN PROCEDURE & EXAMPLE IS456

Concrete mix design is the process of finding the proportions of concrete mix in terms of ratios of cement, sand and coarse aggregates. For e.g., a concrete mix of proportions 1:2:4 means that cement, fine and coarse aggregate are in the ratio 1:2:4 or the mix contains one part of cement, two parts of fine aggregate and four parts of coarse aggregate. The concrete mix design proportions are either by volume or by mass. The water-cement ratio is usually expressed in mass.

Requirements for concrete mix design:

The grade designation giving the characteristic strength requirement of concrete.

The type of cement influences the rate of development of compressive strength of concrete.

Maximum nominal size of aggregates to be used in concrete may be as large as possible within the limits prescribed by IS 456:2000.

The cement content is to be limited from shrinkage, cracking and creep.

The workability of concrete for satisfactory placing and compaction is related to the size and shape of section, quantity and spacing of reinforcement and technique used for transportation, placing and compaction.

Procedure for Concrete Mix Design as per IS 456 : 2000 :

1. Determine the mean target strength ft from the specified characteristic compressive strength at 28-day fck and the level of quality control.

f_t = f_ck + 1.65 S

where S is the standard deviation obtained from the Table of approximate contents given after the design mix.

2. Obtain the water cement ratio for the desired mean target using the empirical relationship between compressive strength and water cement ratio so chosen is checked against the limiting water cement ratio. The water cement ratio so chosen is checked against the limiting water cement ratio for the requirements of durability given in table and adopts the lower of the two values.

3. Estimate the amount of entrapped air for maximum nominal size of the aggregate from the table.

4. Select the water content, for the required workability and maximum size of aggregates (for aggregates in saturated surface dry condition) from table.

5. Determine the percentage of fine aggregate in total aggregate by absolute volume from table for the concrete using crushed coarse aggregate.

6. Adjust the values of water content and percentage of sand as provided in the table for any difference in workability, water cement ratio, grading of fine aggregate and for rounded aggregate the values are given in table.

7. Calculate the cement content form the water-cement ratio and the final water content as arrived after adjustment. Check the cement against the minimum cement content from the requirements of the durability, and greater of the two values is adopted.

8. From the quantities of water and cement per unit volume of concrete and the percentage of sand already determined in steps 6 and 7 above, calculate the content of coarse and fine aggregates per unit volume of concrete from the following relations:

where V = absolute volume of concrete = gross volume (1m³) minus the volume of entrapped air

Sc = specific gravity of cement

W = Mass of water per cubic meter of concrete, kg

C = mass of cement per cubic meter of concrete, kg

p = ratio of fine aggregate to total aggregate by absolute volume

fa, Ca = total masses of fine and coarse aggregates, per cubic meter of concrete, respectively, kg, and

S_fa, S_ca = specific gravity of saturated surface dry fine and coarse aggregates, respectively

9. Determine the concrete mix proportions for the first trial mix.

10. Prepare the concrete using the calculated proportions and cast three cubes of 150 mm size and test them wet after 28-days moist curing and check for the strength.

11. Prepare trial mixes with suitable adjustments till the final mix proportions are arrived at.

CONCRETE MIX DESIGN EXAMPLE – M50 GRADE CONCRETE

Grade Designation = M-50
Type of cement = O.P.C-43 grade
Brand of cement = Vikram ( Grasim )
Admixture = Sika [Sikament 170 ( H ) ]
Fine Aggregate = Zone-II

Sp. Gravity
Cement = 3.15
Fine Aggregate = 2.61
Coarse Aggregate (20mm) = 2.65
Coarse Aggregate (10mm) = 2.66

Minimum Cement (As per contract) =400 kg / m³
Maximum water cement ratio (As per contract) = 0.45

Concrete Mix Design Calculation: –

1. Target Mean Strength = 50 + ( 5 X 1.65 ) = 58.25 Mpa

2. Selection of water cement ratio:

Assume water cement ratio = 0.35

3. Calculation of water content:

Approximate water content for 20mm max. Size of aggregate = 180 kg /m³ (As per Table No. 5 , IS : 10262 ). As plasticizer is proposed we can reduce water content by 20%.

Now water content = 180 X 0.8 = 144 kg /m³

4. Calculation of cement content:

Water cement ratio = 0.35
Water content per m³ of concrete = 144 kg
Cement content = 144/0.35 = 411.4 kg / m³
Say cement content = 412 kg / m³ (As per contract Minimum cement content 400 kg / m³ )
Hence O.K.

5. Calculation of Sand & Coarse Aggregate Quantities:

Volume of concrete = 1 m³
Volume of cement = 412 / ( 3.15 X 1000 ) = 0.1308 m³
Volume of water = 144 / ( 1 X 1000 ) = 0.1440 m³
Volume of Admixture = 4.994 / (1.145 X 1000 ) = 0.0043 m³
Total weight of other materials except coarse aggregate = 0.1308 + 0.1440 +0.0043 = 0.2791 m³

Volume of coarse and fine aggregate = 1 – 0.2791 = 0.7209 m³
Volume of F.A. = 0.7209 X 0.33 = 0.2379 m³ (Assuming 33% by volume of total aggregate )

Volume of C.A. = 0.7209 – 0.2379 = 0.4830 m³

Therefore weight of F.A. = 0.2379 X 2.61 X 1000 = 620.919 kg/ m³

Say weight of F.A. = 621 kg/ m³

Therefore weight of C.A. = 0.4830 X 2.655 X 1000 = 1282.365 kg/ m³

Say weight of C.A. = 1284 kg/ m³

Considering 20 mm: 10mm = 0.55: 0.45
20mm = 706 kg .
10mm = 578 kg .
Hence Mix details per m³
Increasing cement, water, admixture by 2.5% for this trial

Cement = 412 X 1.025 = 422 kg
Water = 144 X 1.025 = 147.6 kg
Fine aggregate = 621 kg
Coarse aggregate 20 mm = 706 kg
Coarse aggregate 10 mm = 578 kg
Admixture = 1.2 % by weight of cement = 5.064 kg.

Water: cement: F.A.: C.A. = 0.35: 1: 1.472: 3.043

Observations from Concrete Mix Design: –

A. Mix was cohesive and homogeneous.
B. Slump = 120 mm
C. No. of cube casted = 9 Nos.
7 days average compressive strength = 52.07 MPa.
28 days average compressive strength = 62.52 MPa which is greater than 58.25MPa
Hence the mix accepted.

Percentage strength of concrete at various ages:

The strength of concrete increases with age. Table shows the strength of concrete different ages in comparison with the strength at 28 days.

Age	Strength per cent
1 day	16%
3 days	40%
7 days	65%
14 days	90%
28 days	99%

Read More on Concrete Mix Design:

Lesson Note On Methods of Grid Levelling

Lesson Note On Methods of Grid Levelling

The art of determining relative altitudes of points on the surface of the earth of beneath the surface of earth is called LEVELLING.

A contour is defined as an imaginary line of constant elevation on the ground surface. It can also be defined as the line of intersection of a level surface with the ground surface. For example, the line of intersection of the water surface of a still lake or pond with the surrounding ground represents a contour line. 

To determine the positions of the contours on a plan the leveling methods are used to find the relative positions of points on the surface of the ground with reference to certain benchmarks at regular intervals. This technique of making observations and plotting on a plan is called as grid leveling.

Contour Interval:  

The vertical distance between any two consecutive contours  is known as a contour interval. For example, if the various   consecutive contours are 100m, 98m, 96 m etc., then the contour interval is 2m. This interval depends upon,

·         The nature of the ground

·         The scale of the map and

·         The purpose of  survey

Contour intervals for flat country are generally small, eg. 0.25m, 0.5m,  0.75 m etc. For a steep slope in hilly area is greater, eg. 5m, 10m,  15 m etc.

Again ,for a small-scale map, the interval may be of 1m,2m,3m etc. and for large scale map,it may be of 0.25m,0.50m,0.75m etc.

It should be remembered that the contour interval for a particular map is constant. 

Contouring:

The process of locating these contour lines on the surface of the earth is known as contouring.  

Methods of contouring:

The method of establishing / plotting contours in a plan or map is known as contouring. It requires planimetric position of the points and drawing of contours from elevations of the plotted points. Contouring involves providing of vertical control for location of points on the contours and horizontal control for planimetric plotting of points. Thus, contouring depends upon the instruments used (to determine the horizontal as well as vertical position of points). In general, the field methods of contouring may be divided into two classes:

1.     Direct methods

2.     Indirect methods

Direct Method:

In the direct method, the contour to be plotted is actually traced on the ground. Points which happen to fall on a desired contour are only surveyed, plotted and finally joined to obtain the particular contour. This method is slow and tedious and thus used for large scale maps, small contour interval and at high degree of precision. 

Vertical control : 

In this method, a benchmark is required in the project area. The level is set up on any commanding position and back sight is taken on the bench mark. Let the back sight reading on the bench mark be 1.485 m. If the reduced level of the bench mark is 100 m, the height of instrument would be 100 + 1.485 = 101.485 m.

To locate the contour of 100.5 m value, the staff man is directed to occupy the position on the ground where the staff reading is 101.485 -100.500 = 0.985 m. Mark all such positions on the ground where the staff reading would be 0.985 m by inserting pegs. Similarly locate the points where the staff reading would be 101.485 -101 = 0.485 m for 101m contour.

The contour of 101.5 m cannot be set from this setting of the instrument because the height of instrument for this setting of the instrument is only 101.485 m. Therefore, locating contours of higher value, the instrument has to be shifted to some other suitable position.

Establish a forward station on a firm ground and take fore sight on it. This point acts as a point of known elevation, for shifting the position of the instrument to another position, from where the work proceeds in the similar manner till the entire area is contoured.

Horizontal control :

The horizontal control is generally provided by method of plane table surveying or locating the positions of points.

Indirect method of contouring:

In this method, the spot levels of selected guide points are taken with a level and their levels are computed. The horizontal positions of these points are measured or computed and the points are plotted on the plan. The contours are then drawn by a process called interpolation of contours from the levels of the guide points. The following are the indirect methods are commonly used for locating contours.

1.     Squares or Grid method

2.     Cross section method

Square or grid method:

In this method, the area to be surveyed is divided into a grid or series of squares. The grid size may vary from 5 m x 5 m to 25 m x 25 m depending upon the nature of the terrain, the contour interval required and the scale of the map desired. Also, the grids may not be of the same size throughout but may vary depending upon the requirement and field conditions. The grid corners are marked on the ground and spot levels of these comers are determined by leveling. The grid is plotted to the scale of the map and the spot levels of the grid corners are entered. The contours of desired values are then located by interpolation. Special care should be taken to give the spot levels to the salient features of the ground such as hilltops, deepest points of the depressions, and their measurements from respective corners of the grids, for correct depiction of the features. The method is used for large scale mapping and at average precision. 

Cross section method:

In these sections, a base line, centre line or profile line is considered.  Cross sections are taken perpendicular to this line at regular intervals.  After this points are marked along the cross sections at regular intervals.  A temporary bench mark is set up near the site.  Staff readings are taken along the base line and the cross sections. The readings are entered in the level book the base line and the cross sections should also be mentioned.  The RL of each of the points calculated.  Then the base line and cross sections are plotted to a suitable scale.  Subsequently the RLs of the respective points are noted on the map, after which the required contour line is drawn by interpolation

This method is suitable for route survey, when cross sections are taken transverse to the longitudinal section.