Muhammad Imdad Ullah

Currently working as Assistant Professor of Statistics in Ghazi University, Dera Ghazi Khan. Completed my Ph.D. in Statistics from the Department of Statistics, Bahauddin Zakariya University, Multan, Pakistan. l like Applied Statistics, Mathematics, and Statistical Computing. Statistical and Mathematical software used is SAS, STATA, Python, GRETL, EVIEWS, R, SPSS, VBA in MS-Excel. Like to use type-setting LaTeX for composing Articles, thesis, etc.

Creating Formulas in MS Excel and Changing Data

by

Changing the data

Before Creating formulas in MS Excel, you need numeric data in different columns or rows of the Excel sheet. Suppose you want to enter a few numbers in a column. Before entering these numbers, you should confirm the cell reference where you need to enter the data. Let’s start by entering numbers in Microsoft Excel‘s cells $A1$ and $A2$. For this purpose follow the steps given below

  1. Click on the cell $A1$
  2. Type 3 from the keyboard
  3. Press the ENTER or DOWN ARROW key on the keyboard. You will be in Cell A2
  4. Now type say 2 from the keyboard and press the ENTER key

Suppose you want to add these numbers in Cell $C1$. You need to write a formula in cell $C1$. After writing the correct formula the content of Cell $C1$ will immediately change to adding two numbers typed in $A1$ and $A2$ and used in $C1$ as formula content.

excel-data-and-formula

Creating Formulas in MS Excel

In this section, we will learn about Creating Formulas in MS Excel. In Excel, each formula begins with an equal sign ($=$), see the picture below

Creating Formulas in MS Excel

Therefore, when creating formulas in Excel, ALWAYS start by typing the equal sign. The equal sign is typed in the Cell where you want the answer to appear. Like the image above, follow these steps

  1. Click on cell $C1$ with ARROW keys from the keyboard or with the mouse pointer.
  2. Type the equal sign in cell $C1$.
excel-data-and-formula

After typing the equal sign in step 2, you have two choices for adding cell references to the spreadsheet formula. Note that cell reference is the name of the cell you want to use in the formula. $A1$ and $A2$ are cell references of numbers 3 and 2, respectively.

  1. You can type these references in or,
  2. You can use an Excel feature called Pointing
excel-data-and-formula

Pointing allows you to click with your mouse on the cell that contains the data or approach a cell reference using the keyboard ARROW keys containing your data to add. This will add cell reference to the formula.

After typing an equal sign in cell C3 in step 2:

  1. Click on cell $A1$ with the mouse pointer to enter the cell reference into the formula
  2. Type a plus (+) sign. You can also use other operators such as for multiplication use you have to use the * symbol, for division/symbol, and for subtraction use $–$, etc.
  3. Click on cell $A2$ with the mouse pointer to enter the cell reference into the formula
  4. Press the ENTER key on the keyboard

The answer 5 should appear in cell $C1$.

Note if you have more than one row or column of data then you need to perform calculations on each row or column cell. It is often possible to copy the first formula to other cells. The easiest way is to copy formulas with the file handle.

See also Creating Formulas in Microsoft Excel

Learn about Primary and Secondary Data

R Programming and Data Structure in R

Stationary Stochastic Process (2016)

by

Stationary Stochastic Process

A stationary stochastic process is said to be stationary if its mean and variance are constant over time and the value of the covariance between the two time periods depends only on a distance or gap or lag between the two time periods and not the actual time at which the covariance is computed. Such a stochastic process is also known as weak stationary, covariance stationary, second-order stationary, or wide-sense stochastic process.

In other words, a sequence of random variables {$y_t$} is covariance stationary if there is no trend, and if the covariance does not change over time.

Stationary Stochastic Process

Strictly Stationary (Covariance Stationary)

A time series is strictly stationary if all the moments of its probability distribution are invariance over time but not for the first two (mean and variance).

Let $y_t$ be a stochastic time series with

$E(y_t) = \mu $    $\Rightarrow$ Mean
$V(y_t) = E(y_t -\mu)^2=\sigma^2 $  $\Rightarrow$ Variance
$\gamma_k = E[(y_t-\mu)(y_{t+k}-\mu)]$  $\Rightarrow$ Covariance = $Cov(y_t, y_{t-k})$

$\gamma_k$ is covariance or autocovariance at lag $k$.

If $k=0$ then $Var(y_t)=\sigma^2$ i.e. $Cov(y_t)=Var(y_t)=\sigma^2$

If $k=1$ then we have covariance between two adjacent values of $y$.

If $y_t$ is to be stationary, the mean, variance, and autocovariance of $y_{t+m}$ (shift or origin of $y=m$) must be the same as those of $y_t$. OR

If a time series is stationary, its mean, variance, and autocovariance remain the same no matter at what point we measure them, i.e., they are time-invariant.

Non-Stationary Time Series

A time series having a time-varying mean or a time-varying variance or both is called a non-stationary time series.

Purely Random/ White Noise Process

A stochastic process having zero mean and constant variance ($\sigma^2$) and serially uncorrelated is called a purely random/ white noise process.

If it is independent also then such a process is called strictly white noise.

White noise denoted by $\mu_t$ as $\mu_t \sim N(0, \sigma^2)$ i.e. $\mu_t$ is independently and identically distributed as a normal distribution with zero mean and constant variance.

A stationary time series is important because if a time series is non-stationary, we can study its behavior only for the time period under consideration. Each set of time series data will, therefore, be for a particular episode. As a consequence, it is not possible to generalize it to other time periods. Therefore, for forecasting, such (non-stochastic) time series may be of little practical value. Our interest is in stationary time series.

R Frequently Asked Questions

A Comprehensive Guide to Binomial Distribution (2016)

by

In this post, we will learn about Binomial Distribution and its basics.

A statistical experiment having successive independent trials having two possible outcomes (such as success and failure; true and false; yes and no; right and wrong etc.) and probability of success is equal for each trial, while this kind of experiment is repeated a fixed number of times (say $n$ times) is called Binomial Experiment, Each trial of this Binomial experiment is known as Bernoulli trial (a trial which is a single performance of an experiment), for example.

Properties of the Binomial Experiment

  1. Each trial of the Binomial Experiment can be classified as a success or failure.
  2. The probability of success for each trial of the experiment is equal.
  3. Successive trials are independent, that is, the occurrence of one outcome in an experiment does not affect the occurrence of the other.
  4. The experiment is repeated a fixed number of times.

Binomial Distribution

Let $X$ be a discrete random variable, which denotes the number of successes of a Binomial Experiment (we call this binomial random variable). The random variable assumes isolated values as $X=0,1,2,\cdots,n$. The probability distribution of the binomial random variables is termed binomial distribution. It is a discrete probability distribution.

Binomial Probability Mass Function

The probability function of the binomial distribution is also called binomial probability mass function and can be denoted by $b(x, n, p)$, that is, a binomial distribution of random variable $X$ with $n$ (given number of trials) and $p$ (probability of success) as parameters. If $p$ is the probability of success (alternatively $q=1-p$ is probability of failure such that $p+q=1$) then probability of exactly $x$ success can be found from the following formula,

\begin{align}
b(x, n, p) &= P(X=x)\\
&=\binom{n}{x} p^x q^{n-x}, \quad x=0,1,2, \cdots, n
\end{align}

where $p$ is the probability of success of a single trial, $q$ is the probability of failure and $n$ is the number of independent trials.

The formula gives the probability for each possible combination of $n$ and $p$ of a binomial random variable $X$. Note that it does not give $P(X <0)$ and $P(X>n)$. The binomial distribution is suitable when $n$ is small and is applied when sampling done is with replacement.

\[b(x, n, p) = \binom{n}{x} p^x q^{n-x}, \quad x=0,1,2,\cdots,n,\]

is called Binomial distribution because its successive terms are the same as that of binomial expansion of

Binomial Distribution

\begin{align}
(q+p)^n=\binom{0}{0} p^0 q^{n-0}+\binom{n}{1} p^1 q^{n-1}+\cdots+\binom{n}{n-1} p^n q^{n-(n-1)}+\binom{n}{n} p^n q^{n-n}
\end{align}

$\binom{n}{0}, \binom{n}{1}, \binom{n}{2},\cdots, \binom{n}{n-1}, \binom{n}{n}$ are called Binomial coefficients.

Note that it is necessary to describe the limit of the random variable otherwise, it will be only the mathematical equation, not the probability distribution.

https://itfeature.com statistics help

Take Online MCQ tests on Probability Distributions

Online MCQs Quiz Website

Generate Binomial Random Numbers in R Language

Writing Excel Formulas (2016)

by

Writing Excel formulas is a little different than the way it is done in mathematics class. All Excel formulas start with an equal sign (=), that is, the equal sign always goes in that cell where you want the answer to appear from the formula. Therefore, the equal sign informs Excel that this is a formula not just a name or number. Let us start with writing Excel Formulas.

The Excel formula looks like

= 3 + 2

rather than

3+2 =

Writing Excel Formulas and Cell References in MS Excel

The example of a formula has one drawback. If you want to change the number being calculated (3, and 2), you need to edit it or re-write the formula. A better way is to write formulas in such a way that you can change the numbers without changing or re-writing the formulas themselves. To do this, cell references are used, which tells Excel that data/ numbers are located in a cell. Therefore a cell’s location/ reference in the spreadsheet is referred to as its cell reference.

To find a cell reference, simply click the cell of which you need cell reference, and from NAME BOX (shown in the figure below), see the text, such as F2.

Writing Excel formulas 1

F2 represents the cell in the $F$ column (horizontal position) and row 2 (vertical position). It means cell reference can also be found by reading the column heading (at the topmost position) of the cells and row number (at the leftmost position). Therefore, cell reference is a combination of the column letter and row number such as A1, B2, Z5, and A106, etc. For the previous formula example, instead of writing = 3 + 2 in cell suppose (C1), follow this way of cell reference and formula writing:

In cell A1 write 3, and in cell B2 write 2. In the C1 cell write the formula such as,

= A1 + A2
Excel Formula 2

Note that there is no gap between A & 1 and A & 2, they are simply A1 and A2. See the diagram for a clear understanding.

Updating/ Writing Excel Formulas

Upon wrong cell references in the Excel formula, the results from the formula will be automatically updated, whenever the data value in relevant cells is changed. For example, if you want to change data in cell A1 to 8 instead of 3, you only need to change the content of A1. The result of the formula in cell C1 will automatically be updated after the updation of the data value in A1 or B1.

Note that the formula will not change because the cell references are being used instead of data values or numbers.

Data Analysis in R Language

Read more about Creating Formulas in MS Excel and Operator Order of Precedence