An Introduction to Student's t-Distribution...

We know that we use the central limit theorem and the normal distribution to calculate probabilities where we assume that standard deviation for the population is given. This might be an ideal case as we might not have access to this value in practical situations.

Here is the point where t-distribution steps in. What we do is that we try to predict the population on the basis of sample drawn from it. And use its standard deviation for the actual population.

The equation that governs the process is:

$$T=\frac{\bar{x}-\mu }{s\sqrt{n}}$$

Example:

A chemical engineer clains that the population mean yield of a certain batch process is 500 gm/cu meter of the raw material. To check the claim he samples 25 batches each month. If the computed t-value falls between $\pm t_{0.005}$ then he is satisfied with the claim. What concentration should he draw from a sample that has mean of $\bar{x}=518$ and s=40 gm.

Manual Solution:

Form the table of t-distribution, $\pm t_{0.005}=1.711$ for n = 24 degrees of freedom. The probability is:

$$t=\frac{518-510 }{40\sqrt{25}}=2.18$$

when we look at the value of 2.25 in the body of the t-table under n=24 we found that it has chances of 0.02.

(to be continued...)

Using Excel To Solve System of Equations..

In Today's Post I will share with you a simple function that can be of tremendous help to you if you want to solve System of 3 Linear Equation. Such a System of Linear Equation is often represented like below: 

It might not be difficult to solve this if you have any high-end calculator for Texas or Casio or HP but in case you don't have one, you can use this sheet that i have setup. Here is a glance of how does this work:

Here i have added the co-efficient and the constant of the equation that facilitate the working of the excel function MDETERM(), since the function does not accept discontinuous ranges, we have to accommodate the sheet to overcome this hurdle. 

I am not going to add the manual workout of the problem but a snap shot of how this file save time:

you can see all done by just entering the data in few cells, rest is up-to the built-in function of excel 2007. Download this sheet and play with it..

A Premier of Process Costing based on Excel 2007

Well this is not an usual post based on some statistical functions, rather here i will discuss how to setup a Process Costing Sheet for yourself. If you have been employed as a Cost Accountant and you are just beginning your career, you might find a temple helpful in your work that can easily calculate the Process Cost. So here we go:

Steps: 

1. Setup a Sheet: 

Setup an Excel Sheet, with usual heading. These included Quantity Schedule, Cost Charged to the Department & Cost Accounted for by the Department. 

2. Develop Relations among Variables:

Yes, you need to develop the relations between the Variables. In Quantity Schedule we will be equating the Total Quantity Received against Quantity Transferred to the Next Department, Material Losses & Work In Process Inventory. You may Also want to add the Abnormal Losses in the sheet.

We also be calculating the Adjusted Amount of Finished Goods that is transferred to the next Department in order to account for the Work In Process Inventory.

The Example Question:

For December, the Production Control Department of Carola Chemical, reported the following data from Department 02:

Transferd in From Department 1........................... 55000 Liters
Tranfered out from Department 2......................... 39500 Liters
Work in Process Units (1/3rd Complete by Conv)........ 10500 Liters

All materials were put into process in Department 1

Unit Cost from Department 01 ............................ $ 1.8
Labour Cost in Department 02 ............................ 25720
Appalied Factory Overhead Department 02 ..............15480

Required: A Cost of Production Report for Department 02.

Solution:

Here is what is given you will need to do to get the solution:

The Solution is not possible without having some side-line calculation, the other part of the spread sheet will provide these values, see the following picture.

I have upload the Sample file here so that you can try it yourself. Hope that you will find it useful. Please provide your feedback. Thank you!!

Using Fonts to Create Symbols for Your Excel Dashboards

This is a comparatively small post and today I am not writing on Excel’s statistical Function. But a rather new topic. You have often seen that special symbols that very attractive, they are not pictures rather text!

Such symbols are created by using Fonts that convert text to symbols when applied with certain fonts. I will discuss only four that are by default present in Excel 2007 viz Symbols, Webdings, Wingdings, Wingdings 2, and Wingdings 3.

Following is an overview of the Fonts that are present by Default in Excel:

Font Type – “Symbols”:

This mostly consists of Greek Alphabets & Mathematical Symbols, you can use it to embed mathematical symbols in you paragraphs, but it is not a replacement to Equation Editor that is normally used to type Equations.

Font Type – “Webdings”:

Mostly consists of Font-Sized Pictures that can be used in Texts. Example is a symbol showing Earth & Clouds.

Font Type – “Wingdings 2”:

Consists of Directional symbols (hands), clouds showing weather, numbers embedded in circle and squares, stars and crossed signs (plus sign) etc

Font Type – “Wingdings 3”:

Directional arrows, triangles, mainly consist of arrows in various variations.

Font Type – “Wingdings”:

Clock showing various times, arrows, stars and circles, numbers embedded in filled circle, various hand symbols, computer accessories and mail boxes etc.

The attached file constrains the characters that you can produce using fonts. 

Examples:

Well my posts are incomplete without example, so here are just few to tell you how to use it!

"Is weather cloudy outside??"

We know that we can create Clouds in our formulas using CHAR(213) to CHAR(217) of Webdings. See the example sheet and play with it>

"How was your performance in the Last Exam??"

Use we Wingdings-2's CHAR(60) and CHAR(61) to see a Thumb-Up and Thumb-Down Symbol

"Right, Left, Up or Down??"

Show Directions using Wingding-3's CHAR(33),CHAR(34),CHAR(35) and CHAR(36).

...and there are two more examples in the Example Sheet. You can download this file that contains details of the symbols that can be used in Excel plus examples. 

Hopefully you will like the post!! :)

Take care

Faseeh

Gamma & Exponential Distribution Explained...

In this post we will be discussing two Distribution that are close cousin of each others i.e. Gamma & Exponential Distributions. While most of the problems in every day life can be solved with Normal Distribution, not every thing can be solved with them. 

Gamma & Exponential Process are frequently found in the Engineering and Management sciences, particularly in Queuing Models and Simulation of Working of various Electrical and Mechanical components for their life (Reliability Analysis) in case of Exponential distribution. 

So First we go with a standard Definition of these two Distributions from Wikipeida: 

In probability theory and statistics, the Exponential distribution (a.k.a. negative exponential distribution) is a family of continuous probability distributions. It describes the time between events in a Poisson process, i.e. a process in which events occur continuously and independently at a constant average rate. It is the continuous analogue of thegeometric distribution

and...

In probability theory and statistics, the Gamma distribution is a two-parameter family of continuous probability distributions. There are three different parameterizations in common use:
1. With a shape parameter k and a scale parameter θ.
2. With a shape parameter α = k and an inverse scale parameter β = 1/θ, called a rate parameter.
3. With a shape parameter k and a mean parameter μ = k/β.

since i am not a statistician, so i will stop here leaving further theoretical part to your end! and move on to how we can solve the problems pertaining to these two distributions with MS Excel 2007.

Syntex of Gamma & Exponential Functions in Excel 2007:

= GAMMA.DIST(x,alpha,beta,cumulative)

X: Is the value at which you want to evaluate the distribution.

Alpha: A parameter to the distribution.
Beta: If beta = 1, GAMMA.DIST returns the standard gamma distribution.
Cumulative: A logical value that determines the form of the function. 

= GAMMA.INV(probability,alpha,beta)

Probability: The probability associated with the gamma distribution.
Alpha: A parameter to the distribution.
Beta: If beta = 1, GAMMA.INV returns the standard gamma distribution.

= EXPON.DIST(x,lambda,cumulative)
X: The value of the function.
Lambda: The parameter value.
Cumulative: A logical value that indicates which form of the function to provide.

Example: An engineer examines the edges of steel girders for hairline fractures. The girders are 10 m long, and it is discovered that they have an average of 42 fractures each. If a girder has 42 fractures, then there are 43 “gaps” between fractures or between the ends of the girder and the adjacent fractures. Find the probability that (a) The probability that a gap is less than 10 cm long. (b) The probability that a gap is longer than 30 cm.

Solution:  

According to this model, the length of a gap between any two adjacent fractures has an exponential distribution with λ = 4.3, the probability that a gap is less than 10 cm long is:

P(X ≤ 0.10) = F(0.10) = 1 − e−4.3×0.10 = 0.35

The probability that a gap is longer than 30 cm is:

P(X ≥ 0.30) = 1 − F(0.30) = e−4.3×0.30 = 0.28

Using Excel’s EXPONDIST() function, the solution can be reduced to:

1. =EXPONDIST(0.1,4.3,1) = 0.35

2. =1-EXPONDIST(0.3,4.3,1) = 0.28

Example: Suppose that the random variable X measures the length between one end of a girder and the fifth fracture along the girder, as shown in Figure 4.15. If the fracture locations are modeled by a Poisson process as discussed previously, X has a gamma distribution with parameters k = 5 and λ = 4.3. Find:

1. Probability that fifth fracture is at least 46 cm away from the end of the girder.

2. Probability that the fifth fracture is within 1 m of the end of the girder

Solution: 

Using Excel's GAMMADIST() function we can easily calculate this: 

1. =GAMMADIST(1,5,1/4.3,1) = 0.430

2. =GAMMADIST(0.458,5,1/4.3,1) = 0.050

You can download this file to play with it, its simple!! :)

You can use the GAMMAINV() to reverse the process. Hope you enjoy this post, please give me some feedback on contents of the Material. 

Thanks & Take Care

Hypothesis Testing for Means with Excel 2007

Hi Readers,

I am back with the topic. In this post we will be learning how to do a basic Hypothesis Testing Analysis for Mean of the Data. So first thing first, here are the definitions from Wikipedia:

Hypothesis Defined:

"A statistical hypothesis test is a method of making decisions using data from a scientific study. In statistics, a result is called statistically significant if it has been predicted as unlikely to have occurred by chance alone, according to a pre-determined threshold probability, the significance level."

What is Hypothesis Testing:

"Statistical hypothesis tests define a procedure which controls (fixes) the probability of incorrectly deciding that a default position (null hypothesis) is incorrect based on how likely it would be for a set of observations to occur if the null hypothesis were true."

So basically while examining a hypothesis we have either of the three Assuminptions

Null Hypothesis

$(H_{0})=\mu_{0}=\mu$

First Condition 

$\mu_{0}\neq \mu$

Second Condition 

$\mu_{0}\geq \mu$

Third Condition 

$\mu_{0}\leq \mu$

So Hypothesis is tested for either of the three alternatives, lets examine it on the basis of some examples:

Case I

A study of a sample of 196 bank accounts showed the average size of back accounts in Rs. 7542. From previous studies of bank accounts, it is known that the standard deviation is Rs. 2984. Test the hypothesis that µ = 8000 against the hypothesis that µ ≠ not equal to 8000. Use α = 0.01 level of significance.

Data: From the above example we can conclude the following data.

Mean	µ	8000
H1	≠	8000
Alpha	α	0.01
Std. Dev	σ	2984
Mean	X	7542
n		196
Tails		Two Tailed
Result		??

Manual Workout will first calculate the Standard Deviation Units

$$H_o=\mu=\mu_0=7542$$
$$H_o=\mu\neq\mu_0=7542$$
$$z=\frac{x-\mu}{\sigma\sqrt{n}}=\frac{8000-7542}{2984\sqrt{196}}=2.148$$

Conclusion: Since calculated value of Z falls with +/-2.58 we will Accept the Claim

Case II

A certain type of seed has always grown to a mean height of 850 inches. A sample of 30 seeds grown under new conditions has a mean height of 920 inches and a standard deviation of 80 inch. At the 1% significance level, test the hypothesis that the new conditions grow better plants.

Data:

Mean	µ	850
H1	>	850
Alpha	α	0.01
Std. Dev	σ	80
Mean	X	920
n		30
Tails		Two Tailed
Result		??

Solution:

$$H_o=\mu=\mu_0=850$$
$$H_o=\mu\neq\mu_0=850$$
$$z=\frac{x-\mu}{\sigma\sqrt{n}}=\frac{920-850}{80\sqrt{30}}=4.76$$

Conclusion: Since calculated value of Z greater then 2.33 we will Reject the Claim

Case III

An advertising company claimed that the average advertising budget for small firms is more than Rs. 45000 per year. To test its claim a random sample of 125 small firms showed an average of Rs. 42710 per year and standard deviation of Rs 13645 per year. Do we accept or reject the hypothesis at 0.05 level of significance.

Data:

Mean	µ	45000
H1	>	45000
Alpha	α	0.05
Std. Dev	σ	13645
Mean	X	42710
n		125
Tails		Two Tailed
Result		??

Solution:

$$H_o=\mu=\mu_0=45000$$

$$H_o=\mu>\mu_0=45000$$
$$z=\frac{42710-45000}{13645\sqrt{125}}=-1.87$$


Conclusion: Since calculated value of Z is less then -1.65 we will Reject the Claim

CALCULATION USING EXCEL FORMULA:

Now the center point of the post how can we do all this using excel formula it is simple

Case I

=IF(AND(((C5-C1)/(C4/SQRT(C6)))>NORMSINV(C3/2),((C5-C1)/(C4/SQRT(C6)))<(NORMSINV(C3/2)*-1)),"Accept Null Hypothesis","Reject Null Hypothesis")

Case II

=IF((C14-C10)/(C13/SQRT(C15))>ABS(NORMSINV(C12)),"Reject Null Hypothesis","Accept Null Hypothesis")

Case III

[Please see workbook for this formula]

So this concludes the post, hope that you will enjoy the post and give feed back. Download this sample workbook and Explore the procedure. 

With Regards,

Faseeh

What's Next..Comming Attractions

Hi Readers!! How are you.

Well it has been almost three weeks since i have posted some stuff on my blog so today i thought at-least pitch a preliminary post to what i intend to write and post on. I had been too too much occupied with my office stuff and study assignment so was unable to write something but here is my plan for the rest of March, 2013:

March's Week 02: 
Hypothesis Testing for Means:

Yes this is a topic i have almost completed and need some fine tuning. Hypothesis testing for mean of  data is an introductory level topic that i will posting by the end of the second week of March. It will include demonstration problem with manual and Excel based solution as usual so do't miss it. 


March's Week 03: 
More on Statistical Distribution, Exponential & Chi-Squared.

The next in the series of Statistical Distribution are Exponential and Chi-Squared. Both of them have plenty of applications in every day life, in every field so hopefully you are going to enjoy it.


March's Week 04: 
Introduction to Calculation of Reliability with SPSS!!

Yes i am going to write for SPSS as well. You might ask why i averted from Excel to SPSS. The answer is simple. I am undertaking course in Advance Quantitative Techniques in Analysis and will be glad to share the procedures and information with you guys, so to me that is a BONUS stuff from me. 

Stay tuned and give me some feed back!!!

Thanks & Take Care! 

Understanding Negative Binomial Distribution with Excel...

in this post we are going to understand the Negative Binomial Distribution. According to Wikipedia:

"In probability theory and statistics, the negative binomial distribution is a discrete probability distribution of the number of successes in a sequence of Bernoulli trials before a specified (non-random) number of failures (denoted r) occur. For example, if we define a "1" as failure, and all non-"1"s as successes, and we throw a die repeatedly until the third time “1” appears (r = three failures), then the probability distribution of the number of non-“1”s that had appeared will be negative binomial."

The distribution can be defined by the following equation:

$$*b(x,k,p)=p^k\cdot q^{x-k}\binom{x-1}{k-1}$$ 

Here $p$ is the prbability of sucess, $k$ is the kth trial and $x$ is the number of trails.

The Excel Function that can be used for calculation of Negative Binomial Distribution is NEGBINOMDIST() that has following syntex (from the Built-in help you can find more on it!)

NEGBINOMDIST(number_f,number_s,probability_s)

..where Number_f   is the number of failures, Number_s   is the threshold number of successes, Probability_s   is the probability of a success.

Now we will take an example as-usual and will see how the manual calculation for this distribution is performed and then will move to the use of alternate excel function.

Example:

In an NBA championship series, the teams which wins four games out of seven will be the winner. Suppose that team A has probability of 0.55 of winning over the team B and both team A & B face each other in the Championship games: 

(a) What is the probability that team A will win the series in Six Games?

(b) What is the probability that team A will win the series?

(c) If both teams faces each other in a regional playoff series and the winner is decided by winning three out o f the five games, what is the probability that team A will win a Playoff?

Solution: Using the negative binomial distribution with $x=6$, $k=4$, and $p=0.55$ we get:

For (a) $*b(6,4,0.55)=\binom{5}{3}(0.55)^4(1-0.55)^2 =0.1853$

For (b) *b(4,4,0.55)+*b(5,4,0.55)+*b(6,4,0.55)+*b(7,4,0.55)

Skipping the mathematical details we can conclude it to:

$=0.0915+0.1647+0.1853+0.1668=0.6083$

For (c) *b(3,3,0.55)+*b(4,3,0.55)+*b(5,3,0.55)=0.5931

Skipping the mathematical details we can conclude it to:

$=0.1664+0.2246+0.2021=0.5931$

Alternate Solution:

Here we will see how these solutions can be obtained using NEGBINOMDIST() function:


(a) =Negbinomdist(2,4,0.55)=0.1853

(b) The probability that team A wins the series is the sum of the probabilities that matches it lost will be between zero to three (if it looses fourth match it can't win the series so the formula would be:

=Negbinomdist(3,4,0.55)+Negbinomdist(2,4,0.55)+Negbinomdist(1,4,0.55)
+Negbinomdist(0,4,0.55)

=0.1667+0.1853+0.1647+0.0915=0.6083

(c) Just like (b) the probability of a playoff for Team A is that is loosses match between 0 & 2 so the the formula will be:

=Negbinomdist(2,3,0.55)+Negbinomdist(1,3,0.55)+Negbinomdist(0,3,0.55)

=0.2021+0.2246+0.1664=0.5931 Ans.

So, that was all from me for this post, i hope you have understood the usage of Excel's built in function to facilitate the calculations. Please keep reading and give feedback to improve the block!! Take care!!

Understanding Binomial Distribution with Excel 2007

In this post we will try to understand how the Excel built-in Binomial distribution function can be used for calculating Binomial probabilities. Traditionally a binomial random variable is described by the following formula: 
$$b(x,n,p)=\binom{n}{x}p^x q^{n-x}$$

Where $p$ is the probability of success and $q$ is the probability of failure and can be related by the relation $p+q=1$, $n$ is the number of trails and $x$ is the value at which we want to evaluate the binomial probability .

The Excel 2007 has a built-in function of BINOMDIST() that takes arguments of $x,n,p$ and logical arguments for cumulative or point density estimate and return the value of Binomial Variable.

The Syntex of the function is :BINOMDIST(number_s,trials,probability_s,cum) 

Now we will see how the calculation that are done manually can be skipped if you use BINOMDIST() function, lets consider this example:

Find the probability of obtaining exactly three 2's if an ordinary dice is tossed 5 times.

Solution: The probability of success is this case is $p=\frac{1}{6},n=5,x=3$ gives:
$$b(3,5,\frac{1}{6})=\binom{5}{3}(\frac{1}{6})^{3}(\frac{5}{6})^{2}=\frac{5!}{3!2!}\cdot\frac{5!}{6!} = 0.032$$
Using Excel: The same can be calculated by  the formula: 

=Binomdist(3,5,0.1667,False)=0.032

Example # 02: The probabilities that a patient recovers from a rare blood disease is 0.4. if 15 people are known to have contracted this disease, what is the chance that (a) at lease 10 will survive (b) from 3 to 8 will survive and (c) exactly 5 will survive?

Solution: 

(a) For Probability $that at lease 10 will survive$:

$$P(x\geq 10) = 1-P(x\leq 10) = 1 - \sum_{x=0}^{9}b(x,15,0.4) = 1-0.9662 = 0.0338$$
(b) For Probability $that from 3 to 8 will survive$
$$P(3\leq x\leq8) = \sum_{x=3}^{8}b(x,15,0.4) = \sum_{x=0}^{8}b(x,15,0.4)-\sum_{x=2}^{8}b(x,15,0.4)$$
$$=0.9050-0.0271 = 0.8779$$
(c) For Probability $that exactly 5 will survive$
$$P(x = 5) = b(x,15,0.4) = \sum_{x=0}^{8}b(x,15,0.4)-\sum_{x=0}^{4}b(x,15,0.4)$$
$$=0.4032-0.02173 = 0.1859$$
Using Excel:

(a) =1-Binomdist(9,15,0.4,TRUE) = 0.0338
(b) =Binomdist(8,15,0.4,TRUE)-Binomdist(2,15,0.4,TRUE) = 0.8779
(c) =Binomdist(5,15,0.4,TRUE)-Binomdist(4,15,0.4,TRUE) = 0.1859

hence we can see how easily we can estimate the binomial probabilities using Excel 2007, I hope that you will like this post. Please give your feedback to improve the blog.

Normal Distribution with Excel - Applications & Examples

In my last post I discussed how to use the normal distribution function already added in MS Excel. In this post I will continue with the examples and we will how we use them. In this post i will not discuss the manual solution of the problem but only the Excel Based solution.

The following problems are taken from Introduction to Statistics By Walpole, 13th Edition Chapter 07, Page 197-200.

I wil elaborate how you can use excel formula to calculate the probabilites.

4. A soft-drink machine is regulated so that it discharges an average of 200 milliliters per cup. If the amount of drink is normally distributed with a standard deviation equal to 15 milliliters: 

(a) What fraction of the cups will contain more than 224 milliliters?
(b) What is the probability that a cup contains between 191 and 209 milliliters?
(c) How many cups will likely overflow if 230-mililiters cups are used for the next 1000 drinks.
(d) Below what value do we get the smallest 25% of the drinks? 

Solution:

Here $\mu$ = 200 mililiters and $\sigma$ = 15 mililiters

(a) =$1-Normdist(224,200,15,1) = 0.0548
(b) =Normdist(209,200,15,1)-Normdist(191,200,15,1) = 0.451
(c) =(1-Normdist(230,200,15,1))*1000 = 22.75
(d) =Norminv(0.25,200,15) = 189.89

9. The heights of 1000 students are normally distributed with a mean of 174.5 cm and a std. deviation of 6.9 cm. Assuming that he heights recorded are recorded to the nearest half of a centimeter, how many of these students would you expect to have heights,

(a) Less than 160.0 cm?
(b) Between 171.5 and 182.0 cm inclusive?
(c) Equal to 175.0 cm?
(d) Greater than or equal to 188.0 cm?

Solution:

Here $\mu$ = 174.5 cm mililiters and $\sigma$ = 6.9 cm

(a) =1000*Normdist(160,174.5,6.9,1)
(b) =(Normdist(182.5-0.5,174.5,6.9,1)-Normdist(171.5-0.5,174.5,6.9,1))*1000
(c) =(Normdist(175+0.5,174.5,6.9,1)-Normdist(175-0.5,174.5,6.9,1))*1000
(d) =(1-Normdist(188-0.5,174.5,6.9,1))*1000

Note: In (b), (c) and (d) we substracted and added 0.5 from values of $x$ to make them inclusive. We are converting point estimate into an intervel estimate through this operation.

16. The average life of a certain type of small motor in 10 years, with a stad. Deviation of 02 years. The manufacturer replaces free all motors that fail while under guarantee. If he is willing to replace only 3% of the motors that fail, how long a guarantee should he offer? Assume that the lives of the motors follow a normal distribution.

Solution:

Here $\mu$ = 10 years and $\sigma$ = 2 years and $\phi$ = 0.03 

=Norminv(0.03,10,2) = 6.238 years

The statistical concepts are self-explanatory. I hope that you are finding these posts helpful certain way.  Hope to listen your feedback soon. Take Care

Understanding Normal Distribution with Excel 2007

Normal Distribution is most commonly used distribution. In this post we try to understand how the manual calculation of Normal distribution problems can be solved using Excel's statistical functions. Excel has following four function that are related to the distribution. 

The Normal Distribution is defined by the following equation.

$$f(x)=\frac{1}{\sigma\sqrt2\pi}e^{-\frac{(x-\mu)^2}{2\sigma^2}}$$
 
Excel 2007 Provide us with following four function related to this distribution:

1. NORMSDIST()
2. NORMSINV()
3. NORMDIST()
4. NORMINV()

Firstly we will understand what each of four stand for:

1. NORMSDIST()

This function can be used to find the value of the Z-Variable as we normally see in the Table of Z-Values. For example the value of area under the Z-Cuve for Z=0.51 is 0.6950. The Same can be found using NORMSDIST(0.51). This assume mean ($\mu$) of zero and standard deviation ($\sigma$) of 1.

2. NORMSINV()

This function is used to find the Z-Value when we have area available to us. we can try with the value of area find in the above paragraph like NORMSINV(0.6950) that will be evaluated to 0.51. This assume mean of zero and standard deviation of 1.

3. NORMDIST()

This function takes the value of mean, st. deviation and an argument for cumulative or mass density function. If you put in one it will calculate cmulative probability and otherwise will give mass density function. 


4. NORMINV()

This function return the value of Z when you have area of the curve given in the data.

Example of Usage:

Following example elaborate the use of function. For $\mu$ = 50, $\sigma$ = 10 find the probability that x will lie between 45 and 62.

Using manual calculation we will do this:
$$Z_1=\frac{x-\mu}\sigma =  \frac{45-50}{10}=-0.5$$ and 

$$Z_2=\frac{x-\mu}\sigma =  \frac{62-50}{10}=1.2$$
Therefore the probability is ...

$$P(45<x<62)=P(-0.5<z<1.2)$$
$$=P(z<1.2)-P(z<-0.5)=0.8849-0.3085=0.5764$$
Now Using Excel 2007 formulas:
 
$$P(45<x<62)=Normdist(62,50,10,1)-Normdist(45,50,10,1)=0.5764$$
Example: Give a normal distribution with $\mu$ = 300 and $\sigma$ = 50 find the probability that $x$ assumes a value greater then 362.

Manual Working:$$Z=\frac{x-\mu}\sigma =  \frac{362-300}{50}=1.24$$ and 

$$P(x>362) = P(z>1.24) = 1-P(z>1.24)= 1-0.8925 = 0.1075$$
The Same result can be obtained using $=1-Normdist(362,300,50,1)$
 
Example No. 02 Given a normal distribution of $\mu$ = 40 and $\sigma$ = 6 find the value of x that has 45% of area below it.

The usual manual calculation for this will involve looking up value of $\phi =0.45$ in the Z-Table and substituting it in formula :$$-0.13=\frac{x-40}{6}=-0.13\times6+40\Rightarrow x=39.22$$ In this question we have been given area of the curve and we have been asked about the Z-Value itself. We will use the last function of the series that is Norminv() to calculate the value
$$P(x<Z)=0.45=Norminv(0.45,40,6)=39.24603$$This ends the tutorial for Normal Distribution, I hope that you will like it. Please give feedback to Improve the blog.

Thanks.

Understanding Hyper-geometric Distribution with excel:

Assume that a box contains 10 Plugs of which 6 are good and the rest defective. An operator picks 5 Plugs at random from the 10, and is interested in the number of good Plugs picked. 

Let X denote the number of good Plugs picked. If in a Population of size N contains S successes and (N - S ) failures, and a random sample of size n is drawn from the pool, the number of successes X in the sample follows then Hyper-geomatirc Random Variable can be defined as:

$$Hypergeomatric (x,N,n,k) = \frac{\binom{k}{x}\binom{N-k}{n-x}}{\binom{N}{n}}$$

Lets understand it with an example, for the case described in above paragraph, where N = 10, S = 6 , n = 5 , x = 2.

The Hyper-geomatric Distribution can be use to calculate the chances of getting 02 defective plugs in the sample by this:
 
$$Hypergeomatric (x,N,n,k) = \frac{\binom{5}{2}\binom{10-5}{6-2}}{\binom{10}{6}}$$ 
..This will give you exact probability of 02 Plugs.

For at most 02 Plugs i.e. at max 02 plugs will be defected:

$$Hypergeomatric (x,N,n,k) = \sum_{x=0}^{x=2}\frac{\binom{k}{x}\binom{N-k}{n-x}}{\binom{N}{n}}$$ 
 For atleast 02 Plugs i.e. at max 02 plugs will be defected:
 
$$Hypergeomatric (x,N,n,k) = 1-\sum_{x=0}^{x=2}\frac{\binom{k}{x}\binom{N-k}{n-x}}{\binom{N}{n}}$$

All this can easily be computed using HYPGEOMDIST function of Excel. This takes follwoing arguments:   

HYPGEOMDIST(sample_s,number_sample,population_s,number_population)


Now using the Excel's Hypergeomatric Function the computation is easily done. Substituteing Values will give you the same result as you have calculated through the conventional formulas.

Hope you like this post!!

Adding Mathametical Expression with Latex

Alhumdolillah, it is quite easy now to write mathematical equations on this blog. I have successfully added ability to write Mathematical expression on the post body as well as the comments of this blog. I have also added a Tool to create equation readily and then use those codes.

Procedure:

The Procedure is simply:

1. On the Top side of the blog you can see "Create Equation & Equation Launcher.

2. Create Equation using the Symbols and you can redialy see the results there as well.

3. Copy the code (Press Ctrl+A & Ctrl+C)

4. Paste it in you comments here you should note two things:

a. If you want to place the codes within text or sentence you should enclose it within dollar sign.

b. If you want to place it as separate text you should enclose it in double dollar sign like.


Example: Within Line Equation:

This is the equation states that $\sqrt{3x-1}+(1+x)^2$ I needed

Example: Not Within Line Equation:

This is the equation $$\frac{x^n-1}{x-1} = \sum_{k=0}^{n-1}x^k$$

Latex Added to My Blog!!

Well this is unusal as i just added the $\LaTeX\$ to my Blog!!!

$$\frac{x^n-1}{x-1} = \sum_{k=0}^{n-1}x^k$$

$$x^2$$

$$x^21 \ne x^{21}$$

$$\overline{x+\overline{y}} = \overline{x}+y$$

$$
\left(
\begin{array}{ccc}
a_{11}&\cdots&a_{1n}\\
\vdots&\ddots&\vdots\\
a_{m1}&\cdots&a_{mn}
\end{array}
\right)
$$

$$
\begin{eqnarray*}
1+2+\ldots+n &=& \frac{1}{2}((1+2+\ldots+n)+(n+\ldots+2+1))\\
&=& \frac{1}{2}\underbrace{(n+1)+(n+1)+\ldots+(n+1)}_{\mbox{$n$ copies}}\\
&=& \frac{n(n+1)}{2}\\
\end{eqnarray*}
$$

Enjoy

Averaging N-Largest Or Smallest Numbers in an Array Containing Blanks

Averaging number is easy in excel, we can use AVERAGE() and AVERAGEA() to achieve the task but it becomes tricky if you want to calculate it for Array with Blank (that are supposed to return zeros) & even more if you want to do it for first N Largest (or Smallest) numbers. In this post i will be discussing both the First ten "Largest & Smallest" Number only by using below formula:

For Largest:

=SUM(LOOKUP(LARGE(IF(ISBLANK(B1:B23)=FALSE,ROW(1:23)),ROW(1:10)),ROW(1:23),IF(ISBLANK(B1:B23)=FALSE,B1:B23)))/10

The Formula is an array formula (i.e. need to be execuated with Ctrl+Shift+Enter), the data is Organized in Cells A1:B23. The conditions that formula should obey are that:

1. The formula should average the "Latest" ten values in the Column B.
2. That the formula when averaging should not include any empty/blank cells, if so it should move to the next cell in the Array.
 
Downloading this sheet will facilitate the working on your side.

The ISBLANK() is actually responsible for Evaluating that the Cells in Range B1:B23 are not blank, if NOT, they will return a Series of Numbers generated from 01 to 23 being produced by ROW(1:23), the same is used by LARGE() and the Values are evaluated for being amongst top ten or not thus the situation becomes:

=LARGE({1,2,3,4,5,6,False,....,22,23},{1,2,3,...,9,10})

Here LARGE() simply ignore FALSE, and return following as the result: {23,22,21,…,16,12,11}. The Second Part of the LOOKUP() execute to give an array of values that will be used as the Lookup_Range with non-blank cells viz {1,2,3,4,5,6,False,....,22,23}. Thus the situation become like this:

=LOOKUP({23,22,21,…,16,12,11},{23,22,21,…,3,2,1},{1,2,1,2,...2,3,4})

..When LOOKUP() is evaluated the Result is an array of numbers that are the largest then, all being non-blanks and looks like this:

=SUM({4,3,2,1,1,3,2,1,4,3})/10

The value thus divided by 10 gives the Average that was required.

For Smallest:

We will be using following formula:

=SUM(LOOKUP(SMALL(IF(ISBLANK(B2:B24)=FALSE,ROW(1:23)),ROW(1:10)),ROW(1:23),IF(ISBLANK(B2:B24)=FALSE,B2:B24)))/10


The working of the formula is same except that in-place of LARGE() we have used SMALL(), the ISBLANK() function will check for the Non-Blanks cells as usual and then SMALL() will create an array of that like: {1,2,...,6,False,False,...,23}. The First Ten Smallest Amongst these will be: {1,2,3,4,5,6,9,10,11,12}.

The rest of the process is same as that for LARGE() part of the article. This array will be matched against the entire range of {1,2,..,23} and the third part of the LOOKUP() will be set to give the corresponding Array, like below:

=SUM({1,2,1,2,3,4,1,2,3,4})/10

when divided by 10 will give you the Average of the Ten Smallest Non-Blank cells in the Array.

Hope you will like this post, please comment to give feedback!!! Thanks.