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.
$$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
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.
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!!
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$$
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
$$\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.
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.
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
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.
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().
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().
Creating a Search Styled List in Excel...
What if you have a list of string and you want to compile a list of them contain certain word. The feature is very common when we search for certain file in windows explorer through Search Option.
Lets examine the following list:
My name is 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 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 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.
Looking up & Matching Repeated Values...
Look at the following table, how will you find the Lowest two values and the corresponding Person & Region? The Question had been less trickier if the 5% value had not been repeated twice. In this post i will show you how to retrieve the values that have duplicate appearance in the tables.
Person Region Score
Engineer 2 South 5%
Engineer 3 North 5%
Engineer 8 South 15%
Engineer 1 North 20%
Engineer 4 South 25%
Engineer 5 North 30%
Engineer 10 South 35%
Engineer 7 North 40%
Engineer 6 South 50%
Engineer 9 North 65%
Using the following formula does the task very well. Lets take it up and split to understand how does it works!
=INDEX($A$3:$A$12,MATCH(SMALL(IF($B$3:$B$12=$F$2,($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001),ROW(A1)),($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001,0),0)
=VLOOKUP(F4,$A$3:$D$12,3,FALSE)
Today we will discuss a technique that is useful in getting smallest of values while testing for certain conditions. The usually used formula for getting nth smalles value is SMALL() that works fine for simple tables where we are just interested in the number itself. But when it comes to find the nth smallest number and then finding something against that number then it becomes a challange because there could be multiple enteries of a same value.
Lets me explain it with this table:
If i try to found the first three smallest values for the above table we can safely conclude them to be 5%, 5% & 15%. But when we try to fetch the corresponding "Person" against these values using MATCH() function, the result is "Engineer2", "Engineer2" and "Engineer8". So why this. This is because MATCH() always looks for the first match that in encounter swhile looking for a values. so it does not distinguishes between 5% of Engineer2 & 5% of Engineer3!!
The solution to this problem is to make each of these 5% different from each other so that when MATCH() goes for a lookup, it differentiate between the First & the Second 5% respectively. In order to make them different we use following formula.
The IF() in the above formula IF($B$3:$B$12=$F$2, ($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001) checks for every value in B3:B12 for whether it is "South" or not, if it is "South", the second argument of the function will pass a value that will have added value of ROW()*0.0000001. This results in addition of 0.0000002, 0.0000003, 0.0000004,...., 0.0000010, 0.0000011, 0.0000012 to the each of the %age values thus two 5s are now differentiate as they are 0.0500002 & 0.0500003 respectively, the logic is applied to the entire range. Note that not all the values in the resulting array will be numbers, only those that are upto the creiteria will be values, rest will be FALSE.
The SMALL() function examines the resulting values [SMALL(0.0500002,0.0500003,0.1500004,0.2000005,...,0.6500011,0.0000012)]
and picks up the first smallest value.
Now this value needs to be matched with an array of similarly generated values so that we can find its location. the same formula is feed to MATCH()'s second argument so that it can lookup for the values. Once these values are found, INDEX() finds out the corresponding value from Column A to reach the solution.
Now this values can be VLOOKUP()-ed in the table to get the corresponding values-a thing that has other wise been impossible.
I hope that you will enjoy the post...am waiting for feedback
Counting Strings Containing Certain Text
Some times we want to calculate how many strings contains certain text. Lets consider this example text:
shahjahankhanalibahadur
shahjahanalibahadurkhan
jahankhanalibahadurshah
shahjahankhanbahadur
jahanalibahadur
I want to find how many of above five contains the text "ali" the formula is simple!! We can use COUNTIF() with a little variation that allows us to "look into each of them for "ali" and then give count of them". Here is the formula:
=COUNTIF(A1:A5,"*"&"ali"&"*")
The result will be 04.
The same thing can be done using SEARCH() with following Array Formula:
=SUM(IFERROR(SEARCH(C1,A1:A5)>=1,0)*1)
This formula seraches for "ALI and gives an array of true & falses and errors, where it find or does not find the match. In order to avoid error message, the IFERROR() function is used to replace them with zeros. When these are multiplied with 1, it assure that all TRUE are replaced by one and then they are summed up by SUM() to give us 04 as a result.
Here is the Sample File
shahjahankhanalibahadur
shahjahanalibahadurkhan
jahankhanalibahadurshah
shahjahankhanbahadur
jahanalibahadur
I want to find how many of above five contains the text "ali" the formula is simple!! We can use COUNTIF() with a little variation that allows us to "look into each of them for "ali" and then give count of them". Here is the formula:
=COUNTIF(A1:A5,"*"&"ali"&"*")
The result will be 04.
The same thing can be done using SEARCH() with following Array Formula:
=SUM(IFERROR(SEARCH(C1,A1:A5)>=1,0)*1)
This formula seraches for "ALI and gives an array of true & falses and errors, where it find or does not find the match. In order to avoid error message, the IFERROR() function is used to replace them with zeros. When these are multiplied with 1, it assure that all TRUE are replaced by one and then they are summed up by SUM() to give us 04 as a result.
Here is the Sample File
Triming a Text without a workbreak
Sometimes it is desired to trim a sentence to a given length so that i can be accommodated into a a cell. Lets say we have a sentence but we want to restrict its length to 80 characters. A solution could be to use LEFT() function that will take Text & Text Length as arguments and will furnish a 80 character long Text String but what if the 80th character ends up in middle of a word? Lets understand it with an example, take the following sentence:
"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRAIN THAT WILL TAKE 08 HRS TO REACH BOSTON."
This sentence has 121 characters including spaces. If i use LEFT() to find the first 80 characters, it will return following text:
"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRA"
You see that the sentence does ends "naturally" rather it is terminated in middle of a word. The ideal solution would have been either to include the complete word "TRAIN" or stop before the word starts. In case we want to stop before the word "TRAIN" started (that should be the case because we restricted to limit of 80 characters) the length of the sentence would have been 76 characters instated of 80. (The " TR" portion being excluded}.
The following formula assured that "Word at the end of the sentence is not got chewed up by our formula"
=IF(LEN(A2)<=80,A2,LEFT(A2,MATCH(80,IF(MID(A2,ROW($A$1:$A$250),1)=" ",ROW($A$1:$A$250)))-1))
Here is the Sample Workbook
"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRAIN THAT WILL TAKE 08 HRS TO REACH BOSTON."
This sentence has 121 characters including spaces. If i use LEFT() to find the first 80 characters, it will return following text:
"MY NAME IF FASEEH AND I AM LEAVING FOR TEXAS. I WILL BE TRAVELING BY A LOCAL TRA"
You see that the sentence does ends "naturally" rather it is terminated in middle of a word. The ideal solution would have been either to include the complete word "TRAIN" or stop before the word starts. In case we want to stop before the word "TRAIN" started (that should be the case because we restricted to limit of 80 characters) the length of the sentence would have been 76 characters instated of 80. (The " TR" portion being excluded}.
The following formula assured that "Word at the end of the sentence is not got chewed up by our formula"
=IF(LEN(A2)<=80,A2,LEFT(A2,MATCH(80,IF(MID(A2,ROW($A$1:$A$250),1)=" ",ROW($A$1:$A$250)))-1))
Here is the Sample Workbook
Using MS Excel Filters & Subtotal Feature
in Maintenance Planning & Budgeting.
Excel
2007’s Filters can be used to plan your maintenance activity and hence
maintenance budget. Filters provide us with option to filter in between two
instances of time thus yield relevant data that you can use further.
The
Filter Option in Excel 2007 can be accessed by going to Data Tab > Filter or Data Tab
> Advanced Filters; however for this post we will keep ourselves
restricted to Filter, that is more appropriate for our use.
We first
need to setup a database in the shape of a list since filter work best with
data in this shape. The Header row contains titles like S.No. , Machine,
Item/Description, Worked Date, Life, Next Due, Rate, Quantity, and Total.
S.No will be used to keep data
in chronological order as you enter it. It can be used for sorting a getting to
the original shape of the list after it has been sorted for some other
criteria. Machines, Item/Description, Worked Date are
self-descriptive. Life should be
in Months. Next Due is calculated by using EDATE() function that adds
months to a certain date giving us next due date, Rate & Quantity are
quoted as it is giving us Total in the last column.
Setting Up the List:
The list
can be easily setup and is available in the Sample File.
Using EDATE() Function:
The
function has following syntax: EDATE (start_date,
months)
The Start Date is the Worked Date, a month is Life
in months, and the formula is entered in column for Next Due.
Getting Data in between Two Dates:
Let’s
assume that Today is 1st of July and we want to find the maintenance
activities that are due this month (July, 2012). This will help us manage the
inventory required and plan our activities more effectively.
Go the Data > Filter > Date Filters > This
Month
In fact the last step of this can be
changes for Weeks, Years, Quarters, Day-Before, Today & Day-After, and In
Between Any Two Dates. So you can plan for the maintenance activities
in next quarter way ahead of it, you can have
detailed schedule using Months, Weeks even Days.
Example:
Let’s consider an example. Download
the attached sheet. This sheet enlists few machines with data against each of
them and details, along with quantity and price. We will use filter the data to
see:
> What are the activities that are due this month, next
month or this quarter?
> What has been the Maintenance Budget for this month,
Next Month, This quarter etc?
Scrolling and following the procedure reveals that there is
no maintenance activity / replacement activity is schdudeld this month, neither
for the following month. However we needed some replacements in the last days
of the first quarter of the year. See the following picture:
When we select the Totals Column we can see the Total as Auto-Sum
or alternatively we could use subtotals for this purpose.
Go the Data > Subtotals
… To see the dialogue box shown in the picture below and add
subtotals for each machine under column Totals.
Conclusion:
There are variations to this method, you can add more detail
to the workbook adding more levels of detail to make thing more practical like
Department, Lines, Machines, Assemblies, Individual Parts etc to pin point
things.
Creating Gartner Hype Chart in Excel 2007
PLOTTING THE BASIC CURVE:
Since there is no specific equation available to plot the chart therefore I tried to create it in two parts:
1. First portion that resembles the Left half of a Normal Distribution.
2. Drawing a line using Curve from Shapes and then plotting points that follow the initially drawn curve.
(The second half of the curve resembles something like an exponential function being plotted.)
The first step to plot this curve was to get a “Line Chart” on the sheet so that whatever we plot we can see how it looks like on the graph.
1. Go to Insert>Chart>Scatter Plot and Press OK.
2. Once inserted, select the Chart, Right click and select the data present B3:B105 to be plotted as X-Value and A1:A105 as Y-Value.
3. Select the Curve, remove the Markers, and increase the line width to 2.25, Line color to Blue.
This sets up the Basic Curve, not exactly as it was in the referred picture, but a partly acceptable approximation of original curve.
[Note: In order to plot the normal curve, the excel function NORMSDIST() has been used that has the Syntex:
NORMSDIST = (X, Mean ,Standard_Deviation , Cumulatve)
…has been used. With Mean (X) ranging from -4 to +1.2 (at a decrement/ increment of 0.2 on each step), we get the first half, the right side of the curve. We have got our first 27 points for the curve through this process but we haven’t plotted it on the same scale (-4 to +1.2) instead on 1~27. The final Excel Formula look like this:
=NORMDIST(X , 0 ,1 , FALSE)
…where X is -4.0, -3.8, -3.6 to 0 and finally 1.2
The second half of the curve is plotted empirically using error & trail method. Once you get this curve, Copy this data & Paste Special > Values to get rid of the formulas and proceed with formatting of the curve.]
PLOTTING THE "YEARS TO MAINSTREAM ADAPTATION":
The points that are plotted on the basic curve have been divided into 04 categories i.e.:
1. Less than 2 Years (Triangular marker with Yellow Fill)
2. 02 to 05 Years (Round marker with without any Fill)
3. 05 to 10 Years (Round marker with Light Blue Fill)
4. More than 10 Years (Round marker with Dark Blue Fill)
5. Obsolete before Plateau (Not Plotted)
We setup a table that contains points for each of the four; the Values of X & the Corresponding values of Y to be found by using a VLOOKUP() and a cell where we can enter Reason.
VLOOKUP() has following syntax:
VLOOKUP = (Lookup_Value, Table_Array, Col_Index_num, Range_Lookup)
Here…
Lookup_Value: Is the value of X (1.0 to 21.40)
Table_Array: Is the corresponding values of Y from Column B (B3:B105) in Sheet1
Col_Index_num: Is 02 since we want to look into the second column
…hence the final formula becomes =VLOOKUP(X-Values, $A$3:$B$97, 2)
Using this formula we get all the points for all the four categories that are to be plotted on the graph.
In order to plot these four series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series.
Now in order for series to appear as what they are in the original picture, select each of the series and:
1. Right click, select Format Data Series > Line Color > No Line
2. While remaining in the same dialogue box, select Marker Option > Built In > Type to select the corresponding type (Triangular or Circular) & Marker Fill > Solid Fill > Color to select the appropriate color for the series.
LABELING THE DATA:
Once we got the four series on our chart, we will have to “Name” each point as mentioned in Original picture. These Names must be entered in the space provided under “Reason” in the tables for each category.
In order to do this, we have two options:
1. We can either do it manually or
2. We can use an add-in that will do it for us.
Since there are many points to be mentioned on the graph, I preferred using add-in instead of manual work, but I will mention here both for the convenience of readers.
LABELING DATA MANUALLY:
1. Select a single point on the chart; so that only one point is select (not entire series).
2. Point the cursor to the formula bar and link it through a formula to cell containing the desired label. (see this picture)
3. Once linked all the points you are done with labeling.
LABELING DATA THROUGH AN ADD-IN:
An add-in that makes labeling a lot easier is available from Rob Bovey, Application Professionals. The add-in looks like this when installed.
It’s a free ware so fell free to download it and install (until you are using it on commercial basis). The add-in could help you by either “Add Labels” or “Manual Labeler”. I opted to use the “Add Labels” that will still make this easier for me, the following dialogue box is shown:
…keeping chart selected, select “Data Series” and Label Range (that is present under “Reason”) this will be finished. Repeat the task for all four data series. You have got labels to your four series. Adjust the position of the labels so that they denote overlap.
ADDING VERTICAL LINES:
The vertical lines present can be plotted as “Scatter graph with line” plotted on secondary axis of the chart. The five lines actually are ten points, two for each of them, plotted on the same scale as that of the primary chart, but on secondary axis and connected through a line…
In order to plot these five series, select the chart, Right Click & select “Select Data”, Add a New Series and point to the values of X & Y to add that series to the chart. Same steps will work for rest of the three series.
Both primary & secondary axis should have one scale, in this case Values on X-Axis are going from 0 to 25 and that of Secondary Axis varies between 0 to 0.45. Once you are finished with these five lines, you may hide the axis.
LABELING VERTICAL LINES:
Using the previously mentioned add-in we can label discontinues ranges as well. In this case, the point that needs to be labeled is actually the point touching the X-Axis, the lower end of the line. So select lower point of all fove lines and use “Manual Labeler” to add labels to it. See this pic:
The “manual labeler “ will ask you to select the series and the point to be labeled, once selected and pressed apply, the label will be placed on the point. You have to do it for all five points on X-Axis
(I used manual labeler here because labels are not contagious to each other, had they been, “Add Labeler” could have sufficed our need.)
THE FINAL STEP:
Increase the font size of the last five points so that they appear highlighted. Move the last line to the right most side so that you can only see the label but not the line. Place this chart on a separate sheet and freeze pan so that if any one scrolls the sheet, the chart retains its position. That all!
That’s all from me. Thank You.
Subscribe to:
Posts (Atom)
The Google Sheet Assignment - Splitting and Transposing Cell based on Delimiter - Part 1
I recently had opportunity to workout a formula with google sheets, where i realized how powerful google sheet formulas are. The problem ...
-
PLOTTING THE BASIC CURVE: Since there is no specific equation available to plot the chart therefore I tried to create it in two p... -
We know that we use the central limit theorem and the normal distribution to calculate probabilities where we assume that standard deviati...
-
My today’s post is about Survival Analysis with Kaplan-Meier Method. This post uses an example for a popular multivariate analysis tex...