In MS Excel and Google Sheets, the NORM.S.INV (Excel) and NORM.S.TERS (Google Sheets) functions are fundamental for performing statistical analyses. These functions calculate the z-value (measured in terms of standard deviations) that corresponds to a specified cumulative probability in a standard normal distribution. They have practical applications in diverse fields such as finance, engineering, and social sciences.

Syntax and Examples

The general syntax for these functions is as follows:

• Excel: NORM.S.INV(probability)
• Google Sheets: NORM.S.TERS(probability)

Here, “probability” is the cumulative distribution function value (a number between 0 and 1). The function returns the z-value corresponding to this probability.

 Example Usage: In Excel: =NORM.S.INV(0.95) In Google Sheets: =NORM.S.TERS(0.95) These examples calculate the z-value corresponding to a 95% probability in the standard normal distribution. 

Applications of the Function

Investment Risk Analysis

Investors use this function to understand the distribution of potential losses in their portfolios. For instance, an investor might want to determine that there is a 5% chance that the portfolio’s performance could be worse. In this case, the z-value can be calculated using the NORM.S.INV function and incorporated into the risk management model.

 Example: In Excel: =NORM.S.INV(0.05) This example computes the Z score representing the worst 5% performance of the portfolio. 

Hypothesis Testing in Social Sciences

Social scientists perform hypothesis tests to measure the significance of observed effects in sample data. The NORM.S.INV function is particularly useful for determining critical values when conducting z-tests. Suppose a researcher is conducting a test at a 5% significance level; then, the critical z-value for a two-tailed test is calculated as follows:

 Example: In Excel: =NORM.S.INV(1-0.025)  In Google Sheets: =NORM.S.TERS(1-0.025)  These examples compute the z-value for a critical value of 2.5% (for the positive tail) which can be used as a critical value in t-tests. 

This function plays a critical role in advanced statistical analyses and offers a broad spectrum of uses within MS Excel and Google Sheets.

Daha fazla bilgi: https://support.microsoft.com/tr-tr/office/norm-s-ters-işlevi-d6d556b4-ab7f-49cd-b526-5a20918452b1