Create your own Functions in Excel

 Now this is a very cool thing which Microsoft introduced in Excel. For any calculation that you do on recurring basis, now no need to do it again and again, create your own function for that; and what else, you do not even need any 'coding' for that.

All of it is possible with this new function Lambda.

Let me show you a very simple example

Suppose, we have to calculate a 3.08% of Terminal Charges on sales done online. Now, instead of doing this calculation again and again, won't it be better that we should be able to write =TerminalCharge(cell) and we get the value.

Before we start creating function, let us understand the syntax:

Syntax:

=LAMBDA([parameter1, parameter2, …,] calculation)

parameters here are the cells which you want user to select, you can allow user to select as many cells as your want depending on situation

calculation is the calculation that you design for deciding what should happen with those cells.

LAMBDA is created in Name manager so that it appear in drop down list of functions.

Assuming our data will be as below:

We Will go in Formulas Tab --> Name Manager , Click on New Button

Now whatever we write in Name, that will become name of the function

Scope : it should be workbook, as the function can be used on all sheets of this workbook; however not outside in any other workbook.

Comments : is optional, whatever we write in comments, becomes the explanation of function.

Refers To :  Here we write our function, =LAMBDA(SalesValue,SalesValue*3.08%)

explanation: SalesValue is the parameter we have designed, which allows user to select cell here, that selected cell will now on be called as SalesValue. On that 3.08% charges are being calculated.

Now onwards, in this file we will be able to use it as =TerminalCharges(SalesValue)

And then we may drag it for others, like any other Excel Function.

Split data in multiple workbooks based on a column

Here, I could find this as the fastest method to split data in multiple workbooks, based on a column values. Irrespective of no. of rows or columns, this will always work.. Wow!!

Sub split()

Dim LastRow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long

Dim ws As Worksheet, r As Range, iCol As Integer, t As Date, Prefix As String

Dim sh As Worksheet, Master As String

Dim wb As Workbook

On Error Resume Next

Set r = Range("C1")    ''Here you can change C to any column, basis which you want to split

On Error GoTo 0

If r Is Nothing Then Exit Sub

iCol = r.Column

t = Now

Application.ScreenUpdating = False

With ActiveSheet

    Master = .Name

    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

    .Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, _

        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    iStart = 2

    For i = 2 To LastRow

        If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then

            iEnd = i

            

            Workbooks.Add

         

            Set wb = ActiveWorkbook

            wb.SaveAs Filename:=ThisWorkbook.Path & "\" & .Cells(iStart, iCol).Value & ".xls" 

            On Error Resume Next

           

            On Error GoTo 0

            Set ws = wb.Worksheets(1)

            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value

            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")

            iStart = iEnd + 1

            ActiveWorkbook.Close True

        End If

    Next i

End With

Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox "Completed in " & Format(Now - t, "hh:mm:ss.00"), vbInformation

     Application.ScreenUpdating = True

End Sub

Calculating Date difference excluding specific dates

So someone asked me, what if we need difference between two given dates excluding some specific dates in between the range.

Scenario : Need to find number of months between any two given dates , however in calculating difference it should not consider month of April.

Solution: To solve this we would be using two formulas :- Datedif( ) and Sumproduct ( ).

This solution will be dynamic enough that later if required we can calculate number of days or number of years between any two given dates and excluding some specific dates.

Let us start with learning the syntax of Datedif(). This function generally does not appear in the DropDown list while writing, however we can directly start putting the function and it will work.

Syntax :- =Datedif(Start_Date, End_Date, Unit)

          Here, unit stand for type of information required. Values that can be used for unit is as below:

             

Unit	Returns
"Y"	The number of complete years in the period.
"M"	The number of complete months in the period.
"D"	The number of days in the period.
"MD"	The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM"	The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD"	The difference between the days of start_date and end_date. The years of the dates are ignored.

Now in our Excel Sheet we can write the start date in a cell and similarly end date in another cell. Also, in a separate column, we can create a list of all the dates that need to be excluded. For example in our scenario, dates from 1/Apr/2015 to 30/Apr/2015 can be written in a column.

Refer below given picture:

Now since start date is in B2 cell and end date in B3 and difference is needed in terms of months, thus the formula will be:

=Datediff(B2,B3,"M")

Now to put the condition of not considering the dates in month of April, i.e. dates given in column D, we can combine Sumproduct with Datedif. Thus, the formula will be:

=DATEDIF(B2,B3,"M")-(SUMPRODUCT((D2:D31>=B2)*(D2:D31<=B3)))

However, since the difference is in terms of months, we need to divide SumProduct with 30. Thus, the final formula will be:

=DATEDIF(B2,B3,"M")-(SUMPRODUCT((D2:D31>=B2)*(D2:D31<=B3)))/30

Now had this been in terms of Years, we would have used "Y" instead of "M" in Datedif and divide Sumproduct with 365 instead of 30.

Similarly, in case of Days, we would have used "D" in Datedif and no division needed in Sumproduct.

Using Offset function for Calculation

Scenario: Given is the monthly data of a firm, we need to find out YTD and make it dynamic so that whichever month is selected by user, YTD of that month should appear. Which means data of all the previous months, including the selected month should be added up.

Solution : To create this we are going to use Data Validations for enabling user to select the month; Match function for finding column number which contains selected month. Sum for adding up the data and more importantly Offset function.

Syntax for Offset: =Offset(reference,row,cols,[height],[width])

Explanation :  

Parameter	Description
Reference	Starting cells
Rows	Number of rows to be moved from starting point
Cols	Number of columns to be moved from starting point
[Height]	Optional Parameter; Total number of rows to be included from start point
[Width]	Optional Parameter; Total number of columns to be included from start point

Data Example:

The Data Sheet

Considering the data shown above, Let say we want to calculate Total sales up till the selected month.In cell O1 Data Validation is used so as to enable user to select the month.

Now to find how many months are selected we can use Match function in cell P1. In Match, we can select the O1 cell as Lookup value, the headers in data as the Array and 0 for exact match. And so, the formula will be:

=MATCH(O1,C2:N2,0)

So if June is selected, answer of Match function will be 6, as June is written in the 6th cell of the selected range in Match.

Now to calculate YTD, we can use Offset. In Offset, our starting cell is the first cell of Jan data i.e. C3. This is because that is from where we will start adding. In this case we do not have to move any number of rows or columns. Our starting point is always same thus we can put 0 (Zero) for rows and Cols parameter.

Similarly the height is also Zero as for one sales person, we have to consider data written in one row. Thus, the data does not stretch Row- Wise. 

However, the Width is needed as we need to include multiple cells in one row at a time. So if Jun is selected, we would need total of 6 cells (From Jan to Jun) in a single row but multiple columns. This height we have pre-Calculated using Match function in cell P1.

And so our Offset Function Should be : =OFFSET(C3,0,0,1,$P$1)

Now whichever range is selected by Offset needs to be totalled up in order to calculate YTD. Thus, we can combine Offset with Sum function.     =SUM(OFFSET(C3,0,0,1,$P$1))

Image for reference

Now if we drag the function, we will get YTD for all the Sales People and making it dynamic for whichever month is selected by user.

Download the Excel data file for reference from below given link

https://drive.google.com/file/d/0By0JZ7H_KuKjYlhweVZhUm5UYmM/view?usp=sharing

Resolving Circular Referencing

There are cases when a formula is dependent on the resultant cell of itself and we really need to keep circular reference. However the challenge is that using circular reference, excel converts the formula result to zero. Let's see how to resolve such a scenario.

To begin with. For let us understand the meaning of circular reference...

Circular Reference : When a formula refer back to its own cell either directly or indirectly, it is called as circular referencing.

Example : Let's take a simple example, a person invest an amount of 1 Lac in a Fixed Deposit for one year at an interest rate of 9%. So at the end of one year he gets an interest of Rs. 9000/-. This can be calculated in excel using IPMT function.

FD Plan
FD Amount	100000
Interest Rate	9%
Years	1
Interest Amount	₹ 9,000.00

The formula used to calculate Interest Amount is = IPMT(Interest Rate,1,Years,FD Amount)

              * Here 1 stand for the period for which interest is calculated

              * FD Amount is taken as negative value

Let's say there is an auto-renewal process and we need to add the interest amount back into the principal for the calculation of next year. This Excel will not allow prompting it as a circular reference error. 

Solution : To avoid such prompt message and to continue with the loop based calculations, we can enable the option of "Enable Iterative Calculations".

To enable this we need to follow the below mentioned path (Excel 2013)

File Tab --> Options --> Formulas --> Enable Iterative calculations

And we can now easily use circular referencing.

Sort Data along with pictures

Hi !

A yet another common problem that people face in their data is why pictures do not get sorted when we sort the data. Specially in product industry, where each and every depiction is via pictures, work become really tedious when it comes to task like sorting and filtering data.
What practically happens is data gets sorted while the placement of pictures gets distorted.

So here's a simple solution:

Before performing the task of Sort/Filter we need to ensure following things in our data:

1. Cells are not merged
2. Every individual picture appropriately fit in exactly one cell
3. For pictures, the property is set to "Move but don't size with cells". To do this you may right-click the picture , select "Size and Properties" option and change the property under "Properties" Tab. Below is the screenshot for reference.

            

Size and Properties window for picture

Now, try using filters or sort your data based on any field and you will notice pictures moving along with it.

Below is a capture of sample data file with ideal settings:

 

So, this is how the column that contains pictures should look like.

Hope this helps !!All the best !! 

Segregating data written in multiple lines in a single cell

We have been pressing Alt + Enter key to write in new line in same cell. However what about the vice versa case. If I get a list of countries all written in same cell; Of course I would not be able able to use any formula on that unless they are written separately, i.e each county name should be written individually in separate cells. So what is the solution?

Lets take a case study

Lets say I have copied a list of countries from a pdf file, which when pasted in Excel is pasted as shown below:

Now the challenge is I have to put all the countries in a validation list. Thus, for that my first task is they should be in separate cells, else they will be treated as single value.

Solution:

We are gonna use Text-To-Column wizard

So this is how we do

Step 1 : Select the cell that contains the names of countries (Cell B3 above).

Step 2 : Go to "Data" tab in the ribbon and select "Text-To-Columns".

Step 3 : Select the option as "Delimited" on the first page of the ribbon.

Step 4 : Select "Other" as the option in screen  2 of the wizard and

             press Ctrl + J on your keyboard.

             Notice the county names now shown in separate column 

             in the preview section of the screen.

             Now click Next.

Step 5 : Select a destination cell on screen 3 of the wizard and click finish.

You will notice the country names divided in cells.

Explanation:

Pressing Ctrl + J on the keyboard is the identifier of new line in Excel. We can use this in Find and Replace too. So suppose I want to add a hyphen ("-") at the end of each countries name. For this I can go in Replace dialogue box; Find Ctrl + J and Replace with - (Hyphen).