Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MEDIAN
- MIN
- MINA
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
CORREL
The CORREL function returns the correlation between two sets using linear regression analysis.
CORREL(y-values, x-values)
y-values: The collection containing the y (dependent) values. Each value can be a number value, date/time value or duration value. All values must be of the same value type.
x-values: The collection containing the x (independent) values. Each value can be a number value, date/time value or duration value. All values must be of the same value type.
Notes
Both collections must have the same dimensions.
If string values or boolean values are included in the collections, they are ignored.
Example |
|---|
Suppose that you kept a record of the periodic changes in the price you paid for gas and also the average temperature setting of your thermostat during the period covered by the specified price. Given the following table: |
A | B | |
|---|---|---|
1 | Price | Setting |
2 | 4.50 | 64 |
3 | 4.20 | 65 |
4 | 3.91 | 65 |
5 | 3.22 | 66 |
6 | 3.09 | 66 |
7 | 3.15 | 66 |
8 | 2.98 | 68 |
9 | 2.56 | 70 |
10 | 2.60 | 70 |
11 | 2.20 | 72 |
=CORREL(B2:B11,A2:A11) evaluates to approximately -0.907629573252938, indicating a close correlation (as prices rose, the thermostat was lowered). Correlation is a measure of how closely two variables (in this case, the price of heating oil and the setting of the thermostat) change together. A correlation of –1 (decreasing slope) or 1 (increasing slope) indicates perfect correlation. A correlation of 0 indicates the data sets are uncorrelated. |
Example — Survey results |
|---|
To see an example of this and several other statistical functions applied to the results of a survey, see the COUNTIF function. |