Sunday, 4 September 2022

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.











No comments:

Post a Comment