Friday, June 1, 2012

Excel VBA code to concatenate with custom characters

Sometimes it is necessary to join the contents of cell with custom characters like +,-,:,|, etc. This can be achived by using excel inbuilt function CONCATENATE. The problem with this function is you will have to specify the character every time. The function listed below will help to simplify this problem.


The function can take multiple arguments just like =SUM() function.


Example:



=CONCATENATE_WITH_CUSTOM_CHARACTER("+", a1:c1,b8)



the result: all the cell values of a1:c1 and b8 will will be displayed seperated by "+" sign



Function CONCATENATE_WITH_CUSTOM_CHARACTER(SEPERATOR, ParamArray args() As Variant) ' this function can take multiple parameters and return each cell value seperated by "+" Dim i, j Dim out For i = LBound(args) To UBound(args) If IsNull(args(i)) = False Then 'check null value If IsArray(args(i)) = False Then 'check if arg is array 'no array do usual If out = "" Then out = args(i) ElseIf args(i) = "" Then out = out Else out = out & SEPERATOR & args(i) End If Else ' arg is an array, do for array For j = 1 To args(i).Count If out = "" Then out = args(i)(j) ElseIf args(i)(j) = "" Then out = out Else out = out & SEPERATOR & args(i)(j) End If Next j End If ' array check end End If 'null check end Next i CONCATENATE_WITH_CUSTOM_CHARACTER = out End Function

Saturday, May 26, 2012

Solving Laplace equation in MS Excel for two dimensional solution

Laplace equation is second order derivative of the form shown below.


laplace.pngwhere phi is a potential function.


This equation is used to describe the behavior of electric, gravitational, and fluid potentials. In the study of heat conduction, the Laplace equation is the steady-state heat equation. This equation also describes seepage underneath the dam.


Direct solution of this equation is very tedious( and uninteresting). Numerical technique gives another approach to solve this equation. In numerical method, the solution space is converted in discrete number of points (a two dimensional array). Any point 'p' inside the solution matrix is the average of its surrounding points (4 points if 4 direction is considerer, 6 points if diagonal elements are included). The known points will be the potential(Phi value) at the boundary. Iteration has to done until desired accuracy is obtained. (similar to playing Minesweeper)


Excel sheet is nothing but a huge matrix.Thus providing ideal place to play with matrix. To solve Laplace equation, first of all each cell should be given a fixed width, i.e. say each cell will be 0.5m. After that, draw boundary, by putting the known phi value at the boundary. Any cells within the boundary will now given a formula which says =average(all surrounding cells). By doing so you will create cyclic function which will run automatically until desired level of accuracy is met. You must enable iterative calculation and set the desired accuracy in excel in its option menu.


I will demonstrate this with an example of seepage problem underneath the dam(green) as shown in figure. The dam has a pile in the upstream toe. There is seepage treatment 25m u/s and 30m d/s of dam. The base width of dam is 10m. The pile is 7m deep sheet pile (where seepage=0). The u/s water head is 5m and d/s water head is 1m. We are interested in distribution of water head inside the soil ( shown in yellow). As 2 dimensional Laplace equation will describe the flow field in this problem, we will solve it in excel sheet. Let us suppose at 25m below the dam foundation there is impermeable layer (another impermeable boundary).seepage under dam.png


Each cell is considered to be1.0m wide. Red cells denotes the boundary. The pile is also hydraulic boundary. The green is dam (done to show similarity with picture). Now a formula is written in any cell stating it as average of 4 surrounding cells (for e.g I10=AVERAGE(I9,H10,I11,J10)) and copy the cell in yellow zone. The boundary condition at u/s is phi=5m water head and at d/s is phi=1m of water head. After you copy formula to each of yellow cell, excel starts to calculate. For this iterative calculation has to be turned on (check Excel>options>formula). The higher the number of iteration, more accurate the result. Sometimes you may have to hit "Calculate sheet" button multiple times to get the result. A chart will show equipotential lines. A sample is shown in figure below. The excel sheet can be downloaded here.


potential.png

Wednesday, March 7, 2012

Falling coin and growing company

When mother is healthy, only then, child can grow. If child dont want to keep his mother healthy, surely health of mother will detoriate and the child will fall down. We must all bear in mind that we must think Big and grow the company we work with. Weather you are working with a one man company or a corporate house, your growth will acclerate, only if the company is growing itself. The company cannot grow without your geneune effort. And you cant sell your CV without working in a growing company. Its two sides of the same coin. And both side should spin together to rotate, otherwise both sides will fall down. Together!