How to calculate a Z dimension with Excel: 5 steps

How to calculate a Z dimension with Excel: 5 steps
How to calculate a Z dimension with Excel: 5 steps
Anonim

In statistics, it is sometimes useful to calculate what is called the Z score (or standard score) of an observation: it corresponds to the number of standard deviations that are above or below the mean of the observation. population. The calculation of this Z dimension can be done quite easily with the famous Microsoft Excel program. To calculate a Z score, you must first calculate the mean (μ { displaystyle \ mu}

\mu

) et l’écart type (σ{displaystyle \sigma }

{displaystyle \sigma }

) de cette même population. La formule de la cote Z est alors la suivante:

cote Z=(X−μ)σ{displaystyle {text{cote Z}}={frac {(X-\mu)}{sigma }}}

{displaystyle {text{cote Z}}={frac {(X-\mu)}{sigma }}}

, X{displaystyle X}

X

étant une valeur de la population.

Étapes

Calculate a Z Score in Excel Step 1

Step 1. Open your data in Excel

The Excel icon is a green half-open folder with a white "X" on the cover. If you already have a worksheet with your data open your file, otherwise create a blank workbook and enter your data.

Calculate a Z Score in Excel Step 2

Step 2. Type the formula for the mean in a blank cell

So you have a series of data in an Excel spreadsheet. You will first type in an empty cell the formula for the mean, namely:

= AVERAGE ("range of cells"), you will replace "range of cells" with the range of cells that contains your data.

So if your data is in column A (between A2 to A11) and you want the average to appear in cell D2, click on cell D2 and type = AVERAGE (A2: A11)

Calculate a Z Score in Excel Step 3

Step 3. Type the standard deviation formula in a blank cell

In the same way, you will type in an empty cell the formula for the standard deviation, that is

= STDEV.STANDARD ("range of cells"). Again, you will replace "range of cells" with the range of cells that contains your data.

  • Your data is still in cells A2 through A11 and you want the standard deviation value to appear in cell D4, click cell D4 and type = STDEV.STANDARD (A2: A11).
  • Depending on whether you are taking all of the data as a base or only part of it, you will use a different function. In the first case, you will use the function = STDEVP.PEARSON, in the second, = STDEVP.STANDARD. The old functions = STDEVP and = STDEVP are set to disappear (old versions of Excel).
Calculate a Z Score in Excel Step 4

Step 4. Find the Z score for one of the population values

The Z dimensions of the different values ​​should appear on the same row as the data, but in the next column. In the first cell, type the formula

= (value - $ mean) / $ standard deviation, value will be replaced by the reference of the cell where the data is located, mean by that which contains the mean and standard deviation by that which contains the standard deviation. Put the "$" symbol in front of the references to make them absolute. Thus, you can move the cells of the Z dimension wherever you want in the sheet.

  • Let's go back to our example. Data is in column A, Z dimensions will be in column B. In cell B2, type the formula

    = (A2- $ D $ 2) / $ D $ 4. The "$" symbol will allow you to move the cells of the Z dimension where you want in the sheet.

Calculate a Z Score in Excel Step 5

Step 5. Apply the formula to the entire column

You entered the formula once, but you want the Z dimensions of all the values ​​in the next column. Click on the cell that contains the formula for the Z dimension, then bring the cursor to the lower right corner, a cross appears. Press the mouse button and scroll down until you enter all the useful cells. Your formula was automatically copied.

Popular by topic