Breaking News: Grepper is joining You.com. Read the official announcement!

statistics in spreadsheet

Innocent Iguana answered on March 20, 2024 Popularity 3/10 Helpfulness 7/10

answer statistics in spreadsheet

statistics in spreadsheet

Comment

- Mean, median, mode, population variance, population standard daviation, quartile, percentile : `AVERAGE()`, `MEDIAN()`, `MODE()`, `VARP()`, `STDEVP()`, `QUARTILE(range, 1_to_4)`, `PERCENTILE(range, 0.50)`
- weighted average: `= AVERAGE.WEIGHTED(data_row_range, weight_distribution_range)` (eg: `= AVERAGE.WEIGHTED(B2:C2, $F$2:$G$2)`)
- Random value : `=RANDBETWEEN(lower_val, upper_val)`
- Correlation between 2 columns : `CORREL()`
- z-score : `=STANDARDIZE(B2,MEAN_CELL,STDEV_CELL)`
- skewness (within +2/-2 is normal ) : `=skew(A2:100)`    [ >0 indicate more values above the mean, below otherwise]
- symmetric (within +2/-2 is normal ) : `=kurt(A2:100)`    [ >0 indicate values "peaked" / "concentrated" around mean, flatter otherwise]
- get slope and intercept : `=LINEST(A1:B100,B1:B100)`, `SLOPE()`, `INTERCEPT()`
- Linear model forecast: `= FORECAST(X_val, Y_RANGE, X_RANGE)`
- Linear model performance metric = Mean absolute deviation : `=AVERAGE(ABS(prediction - observation))`
- Hypothesis testing with t-test (samples are less than 30, and mean, std is unknown): `=T.TEST(A1:A100,B1:B100, tails, type)`
    - tails: both direction is 2-tailed, single direction is 1-tailed
    - types: 
        - type-1 = same observations, different time  (paired t-test)  
        - type-2 = different observations, same variance 
        - type-3 = different observations, different variance
- Hypothesis testing with z-test (bigger number of samples, population mean, std is known): `=Z.TEST(A1:A100, test_stat, stdev)`
- Hypothesis testing with chi-squared-test (2 different independent groups, samples>5): `CHITEST(observed_range,expected_range)`
    - Requires expected frequency table (a pivot table)
    - Formula for expected frequecy : `total_row1 * total_column1 / grand_total`. (do for each row and each column)
- Quick plot to asses probability: `=SPARKLINE(cell, options {"charttype" : ("bar", "line", "column"); "max" : (upper_limit)})`
    - example: `=SPARKLINE(J7,{"charttype","bar";"max",1})`
- Formula of adjusted risk data to see feasibility of predictions by adding variation: `=MAX(0,(random_val_multiplier * risk) + risk)`

Popularity 3/10 Helpfulness 7/10 Language whatever

Source: Grepper

Tags: spreadsheet statistics whatever

Link to this answer
Share Copy Link

Contributed on Mar 20 2024

Innocent Iguana

0 Answers Avg Quality 2/10

statistics in spreadsheet

Contents

More Related Answers

statistics in spreadsheet

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.