How to use curly braces in non-array formula in Excel

Curly Braces are generally used in array formulas in Excel. Array formulas are entered by pressing CONTROL+SHIFT+ENTER and Excel automatically inserts curly-braces in formula. Array formulas are useful when one has to get results from multiple set of values into a single cell or range of values.

Array Formula:

For example, Consider Range A1 through A3 has text "No", "Yes" and "None". If we have to get the value of maximum length of string in cell B1, then, This can be done by Array Entering (CONTROL+SHIFT+ENTER) the below formula in cell B1.

                                                       {=MAX(LEN(A1:A3")}

which returns a value 4.

Non-Array formula with curly-braces{}

However curly-braces can be used in non-array formulas by manually inserting it. Below is an example of using curly-braces in "VLOOKUP" without array entering the formula.

                           =VLOOKUP("C",{"A",1;"B",2;"C",3},2,0)

which returns a value 3.
:-

Read More

How to remove comments only from filtered cells in Excel

Consider a column which has number one to five. Cell with odd number has a comment "ODD" and even number as "EVEN".

If you have to delete comments only from even number then, 

- Filter the even numbers in column (i.e. 2 and 4)

- Then, hit the keystroke (Alt + :) this will highlight only the filtered cells

- Then, right click and delete comment from the selected cells in context menu

To apply formats for filtered cells, select all filtered cells and use comments in paste special option to paste only the comments as shown below.

.

Read More

How to activate other Microsoft application using VBA

If you are working on a project that involves interaction with multiple MS Office application, then VBA has a method to invoke other office applications within Excel. For example,

Below code invokes or activates Word application.

Application.ActivateMicrosoftApp (xlMicrosoftWord)

Similarly, replace xlMicrosoftWord to xlMicrosoftMail to activate Outlook. So one can create keyboard shortcut to frequently used application and activate those applications without any manual effort.

However, to invoke other application not listed or supported by above method use Shell command. For example to start notepad application, use the code: Shell "NotePad.exe"

Read More

Static variable in VBA

A static variable is a local variable which retain its value even after the execution of procedure. Static variable can be declared by placing static  keyword before the declaration.

For example,

static username as string
static counter as integer

In below example static variable "m" retain its value everyime a program is executed

Sub StVar()

Static m As Integer
m = m + 1
MsgBox (m)

End Sub

Note: To reset a static variable to initial value close the workbook or hit the reset button in VBA editor.

Read More

The Difference between Cumulative Distribution Function (CDF) and Probability Density Function (PDF)

Cumulative Distribution Function (CDF) vs Probability Distribution Function (PDF)

The Cumulative Distribution Function (CDF) of a random variable 'X' is the probability that the variable value is less than or equal to 'X'. It is the cumulative of all possible values between two defined ranges.On the other hand, Probability Distribution Function (PDF) is the probability of random variable 'X' equal to certain value. In other words it is a derivative of CDF.

Let us understand this with the example of Normal Distribution data. Normal Distribution curve is a bell shaped curve and is symmetric about its mean. its value extends from -ve infinity to +ve infinity. The curve extends indefinitely in both direction.

Consider a Normally Distributed Data with Mean = 494 and SD = 100. Let us calculate the  probability of random variable X between its mean and 500. Excel has NORMAL.DIST() function which returns CDF and PDF for a Normal Distribution. It takes four arguments namely; X, MEAN, SD and CUMULATIVE. The 4th argument CUMULATIVE is Boolean and if set to TRUE then the function returns CDF and if set to FALSE, the function returns PDF.

CDF = "NORM.DIST(500,494,100,TRUE)" = 0.5239 or  52.39%

PDF = "NORM.DIST(500,494,100,FALSE)" = 0.00 or  0%

In the above calculation CDF of 52.39% is a probability of X is from -infinity to 500. To get the probability of X between MEAN (494) and 500, subtract 50% from CDF, Hence the probability of X between MEAN and 500 is 2.39%.

Read More