# ieCivil | Hyonkows

That is IT & Electronics for Civilization

## Pages

The Web-log of an Engineer For The Digital Civilization :-)

## Wednesday, 26 October 2016

### Switch Case or Multiple If Else Nesting in Excel | HYONKOWS

Switch Case in Excel

Under many circumstances there is a need of if else or logical switch to select a particular mode of operation during calculations especially if you're a nerd at using excel and taking it to its limits.
One of my friend had a concept which he wanted to apply on excel but was lagging with the technique of implementation, after a lot of  R&D I finally ended up with a Jugaad in which I used HLOOKUP function of excel to drive case switches.

### How was it Done?

Basically the video below explains it well, though I'd like to explain the technique:

Assuming you've seen the video, here are the details of the logic I used!

• As the HLOOKUP wasn't working well for the formulas I made the columns of the formulas in the table itself and then just added the HLOOKUP as the switch to toggle the Output. All the four cases are calculated but only one case is preceed towards the output.
• Now while making the array to be selected it is to be noted that the table that you select should have the same formatting, in my case as I'd selected the table headers it got up all messed up.
• Now as I'm using HLOOKUP so my 1st row would be 45, 90, 180, 360 row and and so on. So to automate the row selection procedure I started numbering from 2 onwards and mentioned it as the reference in the syntax for the HLOOKUP operation.

#### Its better to Hide up formulas that you used! so just select the columns and hide those.

You can still manupulate with the cases by just entering the value and I'll put the corresponding value in the cell

## How to use the HLOOKUP formula?

Hope this post was usefull

Comment your experience in the section below!
Share:

## Wednesday, 19 October 2016

### Geometry Of Truss | First Web App Or Simply Jugad!

This post consist of an excel sheet which I used in my SD1 termwork, I tried using some freely available resources and embedded a part of my excel sheet into this blog to make it run like a software.

The following I-frame Consist of a form which is just to fetch up the inputs which is further processed by Google Spreadsheets...

After putting a lot of time studying a few scripts.
I finally Present my First Jugad in Web development

Please fill in the following form with the available data and click on the link at the end to download the results, please be quick in this process as the link resets every 1 min.

Output(there may be lag):

Please note: I'm not an IT personnel but an enthusiastic Civil Engineer, any inconveniences in the technicalities and working of this site is regretted!

Hope this is of some help...

Recommended Video:

Share:

## Tuesday, 11 October 2016

### Making A Contour Plan On AutoCAD | Application of Polar Tracking

Drawing a sheet of the data obtained from the field is an extremely tedious task but it can't be ignored or left as it is a part of term work in the Second Year of engineering Under Pune University.

Well leaving the odds behind, the entire project  is interesting and fun in itself. The calculations are damn tough but using excel and a few formulas it’s just a matter of data entry, the computer does the work for you.

If you want to learn or download the spreadsheet that I used for this project, you may read my article about contour calculations on excel by clicking here.

Coming Back to the point:
AutoCAD is a handy tool to plot this time consuming and error prone sheet comfortably. In this post, I shall be sharing our experience and how we accomplished this task of eating an elephant in an extremely limited time and with minimum efforts.

• We made two teams as there were to stations that were approximately 26 meters apart.
• As there were about 360 readings observed from each team, both the teams using different computers plotted each of the stations complete reading.
• Once both the readings are on AutoCAD we took both the parts on PC and then compiled it into one single file.
While plotting the readings on AutoCAD the only command of maximum use to us and the only life saver was the "Polar Tracking" feature.
The following video demonstrates how to use polar tracking on AutoCAD

Once you've learned about polar tracking all you just need to use the line command and insert the distances from the point of observation

Like for an instance you could just take the lenght like if its 20m and RL at that point is 102 so to plot this you need to use the line command l and then lock in the angle using the polar tracking feature and then type the distance 20 and then enter after that you may place a marker like a point or a small circle with a text representing the RL of that point. In this way just by the same method complete all the entries.
This could be an extremely boring and I would be very happy to learn about new or alternative techniques, please comment below!
I've made a short video giving glimpse of the entire procedure which is as follows:

Finally using two Softwares some complex logic and a few cups of tea I could complete the entire project sheet not to forget my companions who worked a lot with me.

After printing the AutoCAD model into PDF It looked something like below:

Clearly visible we used two stations on a mountain....

Hope this piece of text and the motion graphics were of some use, please comment below...

Share:

## Friday, 7 October 2016

### Eliminate the Need of Planimeter! | How to measure area of a toposheet

A planimeter, also known as a platometer, is a measuring instrument used to determine the area of an arbitrary two-dimensional shape.

Using a planimeter could be extremely tedious could associate your work to a lot of errors, so under such circumstances AutoCAD or any other CAD software could be of great aid. All you need is a scanner to scan the plan or geometry and convert it into its soft copy in PDF (portable document format). Once this task is accomplished calculating and manipulating area and geometry is an extremely easy task.

In the Current Context I shall be demonstrating this technique with an assignment we executed in our Engineering!

As under the subject of HWRE we had to estimate precipitation of a catchment from a toposheet

Following is the an example of a toposheet:

 Example of Toposheet

What we did is traced out the catchment area from the toposheet and scanned it to convert it into its PDF soft copy.

 Original Scanned Copy Of Tracing Paper
Once You have the softcopy of the tracing paper, import it into AutoCAD using the PDF underlay feature of AutoCAD

The following Video Explains the PDF underlay feature in 2 minutes...

Once you've imported the files in AutoCAD just take the polyline command and start tracing the geometry of the PDF underlay.

 Traced By Using Poly Line
After tracing the Geometry you need to scale it.
Scaling the Geometry is a bit of a task and requires some calculations.
• Take a dimension you know for instance in my case the distance between P3 and P4 was 6.5cm so first scale the geometry in such a way that you get the same dimension when measured by dimension command (DAL)
• Once the above step is completed you need to move scale the geometry to the actual scale it was designed for in my case the scale on the plan was 1:50000 so I scaled the figure by a multiplication factor of 50000
• Now your figure is to the scale but it still needs a bit of modification.
• The distances you measure won't amount to the realistic value so you need to again scale the geometry by using the scale command and a scale factor of 1/1000.
The following Video Explains the Scale command in AutoCAD

Now you just need to use a few AutoCAD commands and mark the polygons and Isohyets using the line, polyline and other drafting commands.

Now by using the boundary (bo) command create boundaries of the area of influence of the Rainguage stations and after that just select the boundary line and then type li (list command). This command will display the area and perimeter enclosed by the polyline.

For doing this I recommend working on Layers as it will enhance the effect and ease in drafting.

The following Video Explains how to calculate area  in AutoCAD

Finally you can compute the areas observed along the rainfall data and perform the precipitation analysis as follows

Following is an example of how I calculated the precipitation using SpreadSheets

Hope this was certainly useful...
Subscribe to our Youtube channel and please comment on your experience with this post and the method!
Share:

## Tuesday, 4 October 2016

### HEC-RAS | Tutorial And Outputs

HEC-RAS is a computer program that models the hydraulics of water flow through natural rivers and other channels. Prior to the recent update to Version 5.0 the program was one-dimensional, meaning that there is no direct modeling of the hydraulic effect of cross section shape changes, bends, and other two- and three-dimensional aspects of flow. The release of Version 5.0 introduced two-dimensional modeling of flow as well as sediment transfer modeling capabilities. The program was developed by the US Department of Defense, Army Corps of Engineers in order to manage the rivers, harbors, and other public works under their jurisdiction; it has found wide acceptance by many others since its public release in 1995.

The following video provides a fast tutorial for new users...

The following are the outputs of example we attempted on HEC-RAS

Share: