Category Archives: MS EXCEL Macros and VBA

How to create the Bell Curve in EXCEL

To create a Bell Curve represent a normal distribution also known as a Gaussian distribution.

To do that you need four pieces of information

1. Data for which you want to plot the bell curve.
2. Normal Distribution
3. Average
4. Standard Distribution

 

Create Bell Curve in Excel

 

Step1

Put the  following labels  in column A,B,C and D

Data    Distribution  Average   Standard Deviations in the 4 columns as shown in the screenshot

Step2

Use the formula for calculating the Average as shown below. Remember that the average is just one value we simply copy it to all the cells in Column C for convenience.

 

07-May-16 5-51-02 PM

 

Step 3

Calculate the standard Deviation in Column D

use the formula for calculating the Standard Deviation as shown below. Remember that the Standard Deviation is just one value, and we simply copy it to all the cells in column D for convenience

07-May-16 10-24-51 PM

 

Step 4

Now we will calculate the Normal Distribution in column B and we would be using the values in column C(Average) and in column D(Standard Deviation) . Copy the formula to all the cells in B.

 

07-May-16 10-28-20 PM

 

Step 5

Now we have all four columns with required information

Column A – has the actual data for which we want to draw the graph, eg. weight of patience, price of a product

Column B – Normal Distribution values for each cell

Column C – has the average value  copied to all the cells in column C

Column D – has the Standard Deviation value copied to all the cells in column D

 

Draw the graph by selecting all the values in column B ( Normal Distribution values )

07-May-16 10-35-04 PM

 

 

We have our bell shaped curve ready .

 

Multiple IFs in EXCEL

How to use multiple IF conditions in MS EXCEL

 

A typical IF condition is as below

=if(logical_test, Value_If_True, Value_If_False)

 

=  IF(A1=1,”label for True condition”,”Label for False Condition”)

eg .IF(A1=1,”YES”,”NO”)  – this will show “YES” if the contents of cell A1 is 1  otherwise it will show NO in the cell where you put this formula.

 

Now look at the more complicated scenario as shown below.

Consider the following conditions, if the option is 1, 2 or 3 then you would like to display “YES” and if it is not in 1,2, or 3 then you would want to display a “NO”

You can achieve it by using nested or multiple IF conditions.

 

you can use a formula like  the following

=IF(B2=1,"Yes",IF(B2=2,"Yes",IF(B2="3","Yes","No")))

Basically you are replacing the “No” or the FALSE portion of the formula and extending it by giving another IF condition.

So  if B2 =1 then show YES, if B2  <> 1 then try to evaluate if B2 =2 , if B2 is 2 then show a YES, but if B2  is not 2 then try to evaluate if B2 =3 , if B2 is 3 then show YES otherwise show NO.

You can keep on nesting multiple IFs by replacing the “FALSE” portion of the IF command and then putting another IF condition instead.

 

 

MultipleIFs