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.

Progressive Pricing Explained

Progressive Pricing Explained (Using FIFO Approach):

Today I will explain how to use a Progressive Pricing formula to Calculate the Value of certain goods purchased at different price level.

Lets consider this problem: You are a Purchase Manager how purchase Chocolates from different suppliers. In your inventory is present a stock of 15000 Kg of Chocolates that is issued to the factory on FIFO basis. FIFO means that the stock that is purchased first will be issued first. Now you want to calculate how much worth chocolate has been issued to the factory.

A manual practice will required you to multiply the issue with the stock issued for each price level. The process is not hectic if the number of suppliers are small, but what if it goes to multiple dozen?? The process can be simplified if you use spreadsheet for the purpose.

Example: Consider following Table. It contains the Suppliers, Items, Qtty,  Rate & Cum. The first four columns are self explanatory, the last column is the Accumulate sum of the total chocolates present in the stock. This column will facilitate the working of the formula we are going to discuss.

Download this Excel Sheet  and it will facilitate the learning process.


In Cell H1 i have entered the Qtty of the Chocolates issued and in G5 entered the following Formula:

=IF($H$2-E5<1,IF($H$2+C5-SUM($C$5:C5)<1,0,$H$2+C5-SUM($C$5:C5)),C5)
 

 



The first part of the outer IF() check whether the stock under consideration is exhaustive to full fill the demand. In out case, it is 2000 against the qtty issued (14751) so the argument evaluates to False, Since it evaluates to false, the second condition of the IF() is the result that we find in the cell G5.

The Same process continues till we reach Cell G10. In Cell G10 the stock of chocolates is 3000 Kg while we have already issued 12000 Kg from previous suppliers (The 12000 Kg is evident from the Cumulative Qtty  Column). We will be issuing not all 3000 Kg but just 2751 to make it to 14751 Kg.

=IF($H$2-E10<1,IF($H$2+C10-SUM($C$5:C10)<1,0,$H$2+C10-SUM($C$5:C10)),C10)

At this point the first part of the outer IF() will return True when it evaluates $H$2-E10<1, here it will be less then 1, triggering the True portion of the First IF(). The Second IF() will ensure that we get only 2751 out of 3000 this way:

 $H$2+C10-SUM($C$5:C10)<1   =  14751 +  3000 - 15000 = 17751 - 15000 = 2751

Since the statement is False, the second of the the IF() will be evaluated to give you 2751 in G10, had it been True, we would have got Zero in it, just like we get it in Cell G11.

The Formula in Column H5 Simply Multiply the quantity with the respective rate to give it to you the total amount of chocolate issued

I will try to make another post explain a single-formula for the whole process. Hopefully in that approach, we will not be needing this table at all.

Fell free to give feedback on this post.

Welcome 2013: The Cycle Starts...

Hi All,

Here is a mixed thought, this is the first post of the year 2013 and i am empty minded. I planned to write something on using Statistics with Excel 2007 but by now i have not been able to do any thing. Mostly due to my dis-foucus on writing and concentration on learning the VBA. 

I have been handy at writing the codes that suite my requirement but had never been a great programmer. unlike programming i have mastered quite a bit how formulas works, but not always, as they, work so it is quite needed sometime to knew ABC of visual basic. I am on it, and hopefully will learn it very quickly. 

I will try to come up with some useful thing as soon as possible, by then enjoy reading my last few posts :)

Take Care,
Faseeh

Counting Multiple Occurances of A Text within a String Over a Range...

What if you have a list of string and you want to compile a list of them contain certain word and you want to count a certain text that appears multiple times with a string. Lets examine the following list:

My name is faseeh faseeh
My name is not faseeh
My Name is faraz
My Name is fawwad
My Name is farooq


Each of the them contains "My Name", two of them contains "faseeh", all five contains "is". Now the question is that how will we find the string contains "Faseeh"? 


The COUNTIF() with wildcard can calculate the frequency of the text but it will not count for the text that appears twice within these string.

The following formula does the trick, Enter and Press Control+Shift+Enter, and drag down.

=IFERROR(INDEX($A$1:$A$5,SMALL(IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0),ROW($A$1:$A$5)),ROW(C1)),0),"---")

The Search() Function:

IF(IFERROR(SEARCH($B$1,$A$1:$A$5,1)>=1,0)

The Search function looks up for the Lookup_Criteria, which is present in B1 (i.e Faseeh) in the Array A1:A5, if the search is gives VALUE# Error, the IFERROR() formula replaces the error with zero. This in retrun is feed to the IF() that return ROW() # for the Trues. 

=IFERROR(INDEX($A$1:$A$5,SMALL({1,2,FALSE,FALSE,FALSE,ROW(C1)),0),"---")

The Small() Function: 

The SMALL() function returns the first smallest value in the array, the result is feed to the second argument of INDEX() function that is a row number, with column offset equals to zero, if the result is an error the outside IFERROR() formula wraps it into and gives you "---". 

Hope this post helps you once again.

The SUMPRODUCT() function...

Introduction:

The SUMPRODUCT() function is a very important function when it comes to validiate multiple conditions and sum a certain range that satifies the given criteria. In this post i will explain and highlight some of the very commonly encountered situtation where SUMPORDUCT() function can be used.

The Syntex:

The MS Excel 2007 describes it as:

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   are 2 to 255 arrays whose components you want to multiply and then add.

With Remarks that:
1. The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. 
2. SUMPRODUCT treats array entries that are not numeric as if they were zeros

Senario # 01: 

The basic usage of SUMPRODUCT() is when we try to multiple different ranges of equal diemntions and want to get their sum. The attached sample file has tab Senario # 01 that describes the process so it will easy if you download it:

Product Price Quaintity

A 10 50
B 15 55
C 20 40
D 25 35

For the given example that consitute the Range from A1:C5, we enter following formula in Cell C6 to get the PRODUCT+SUM of Price & Quaintity:

=SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   

If you select the cell C6 and go to Tab Formula > Evaluate Formula it will take you from follwing steps:

Step 01: =SUMPRODUCT($B$2:$B$5,$C$2:$C$5)   
Step 02: =SUMPRODUCT((10,15,20,25,),(50,55,40,35))
Step 03: =SUMPRODUCT((500,825,800,875))
Step 04: =3000

Thus an additional column that had been need to multiply the Price and Quaintity has been avoided and we get the same result.

Senario # 02:

The SUMPRODUCT() function can be used to verifiy multiple conditions. Contining with the same example of Product, Price & Quaintity, we add some further details to elaborate this type of usage as well. Please switch to the second sheet named "Senario # 02" to understand this example:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35
B Feb, 12 35 15
B Mar, 12 40 20
C Apr, 12 45 27

First Situation: Summing the Quaintity for a particular month:

In such a case we will setup a cell to input the "Month" for which we want to sum the sales. The formula in this case will look like:

=SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))

For this formula to work we will enter in $C$11 the desired month eg "Jan,12", the formula will work like this:

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",=$C$11)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12","Feb, 12","Mar, 12","Apr, 12",="Jan, 12"}))*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,False,False,False}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({50,False,False,35,False,False,False})
Step 06: =85

Second Sitatuion: If we want to get "Total Cost" then we will multiply it with ($C$2:$C$8), the formula will look like then

=SUMPRODUCT(($B$2:$B$8=$C$11)*($C$2:$C$8)*($D$2:$D$8))

Step 01: =SUMPRODUCT(($B$2:$B$8=$C$11)*(D2:D8))
Step 02: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",=$C$11)*($C$2:$C$8)*(D2:D8))
Step 03: =SUMPRODUCT(({"Jan, 12","Feb, 12","Mar, 12","Jan, 12",...,"Apr, 12",="Jan, 12"}))*($C$2:$C$8)*(D2:D8))
Step 04: =SUMPRODUCT({True,False,False,True,...,False}*{10,15,20,25,35,40,45}*{50,55,40,35,15,20,27})
Step 05: =SUMPRODUCT({500,False,False,875,False,False,False})
Step 02: =1375   

Third Situation: Consider the thrid sheet where we have to check for Month as well as week, the Month is present in columns while week numbers are present as the header row of the table. We have incorporated an option in the workshee that will look into the range of the weeks specified through input cell C7:C8, the month is specified in C9. The table looks like this:

Product Month Price 1 2 3 4 5
A Jan, 12 10 40 10 30 30 50
B Feb, 12 15 50 20 10 30 20
C Mar, 12 20 40 10 30 10 20

And the formula is: 

=SUMPRODUCT(($B$3:$B$5=$C$9)*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))

Step 01: =SUMPRODUCT({"Jan, 12","Feb, 12","Mar, 12"}="Jan, 12"*($C$3:$C$5)*($D$2:$H$2>=$C$7)*($D$2:$H$2<=$C$8)*($D$3:$H$5))
Step 02: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*($D$3:$H$5))
Step 03: =SUMPRODUCT({True,False,False}*{10,15,20}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 04: =SUMPRODUCT({10,0,0}*{1,2,3,4,5>=1}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 05: =SUMPRODUCT({True,True,True,True,True}*{1,2,3,4,5<=2}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 06: =SUMPRODUCT({10,10,10,10,10,0,0,0,0,0,0,0,0,0,0}*{True,True,Flase,Flase,Flase}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 07: =SUMPRODUCT({10,10,0,0,0,0,0,0,0,0,0,0,0,0,0}*{40,10,30,30,50,.....20,40,10,30,10,20})
Step 08: =SUMPRODUCT({400,100,0,0,0,0,0,0,0,0,0,..,0,0,0,0})
Step 09: =500

Fourth Situtation:  We can use "<>" operator to select everything else then a particular criteria, see the fourth sheet for this example: 

The table looks like following:

Product Month Price Quaintity
A Jan, 12 10 50
B Feb, 12 15 55
C Mar, 12 20 40
A Jan, 12 25 35

Lets assume that we can to sum the Total Cost for every thing else then "A" we will replace the equal sign "=" with "<>":

=SUMPRODUCT(($A$2:$A$5<>"A")*($C$2:$C$5)*($D$2:$D$5))

The formula will check the first condition and will give these result: 

Step 01: =SUMPRODUCT({"A","B","C","A"<>"A"}*($C$2:$C$5)*($D$2:$D$5))
Step 02: =SUMPRODUCT({False,True,True,False}*{10,15,20,25}*{50,55,40,35})
Step 03: =SUMPRODUCT({0,825,800,0})
Step 04: =1625

Conclusion: The SUMPRODUCT() function gives you flexibiility to check for multiple criterias and sum a range, in caes of unique value, it can also retrive one that meets your critieria, but being an array formula it works slower then SUMIF().