Apache OpenOffice Wiki - User contributions [en]

User:Mwaller

2010-10-04T08:19:54Z

Mwaller:

Mwaller stands for [mailto:mwaller@openoffice.org Martina Waller]. I'm a technical writer at Oracle in Hamburg, Germany with a focus on end user documentation.

I studied linguistics for Romance Languages in Hamburg, then trained and qualified as a Technical Writer at tecteam in Dortmund. After several years with a service provider for technical documentation I worked some time as a translator and layouter before I came to Sun (now Oracle) and OpenOffice.org.

User:Regina/MyDrafts

2010-05-25T07:57:23Z

Mwaller:

''' Accuracy '''

This article is about situations, where you think, that Calc calculates wrong. You might have found a bug, and then you should write an issue. But look here first; perhaps the unexpected results have reasons, you haven't been aware of.

== Precision in Calc ==

Internally, Calc uses for its calculations floating point numbers in double precision defined in IEEE 754 standard.

In default settings Calc rounds the displayed values to two decimals. You can get at most figures shown using the scientific format with format code <tt>0.00000000000000E+000</tt>.

But although you can force Calc to show 15 decimal digits, these might not be all accurate. The following sections list some of the problems.

ToDo: Explain setting "precision as shown".

== Converting between Number Systems ==

Most non integer numbers have infinite decimal places in binary format, which have to be rounded somewhere.
For example, the number 0.1 cannot be written exactly in binary format. Because of this rounding it can happen, that two mathematical different numbers have the same (rounded) internal binary value.

The other way round, the decimal values are rounded to at most 15 figures. So you will see no difference between =SQRT(2) and =SQRT(2)+9E-15, but EXP(SQRT(2)) and EXP(SQRT(2)+9E-15) differs in the last shown figure.

== No Symbolic π ==

From mathematics you know <tt>sin(π)= 0</tt> and you know that <tt>tan(π/2)</tt> is undefined. But you cannot get these results in Calc, because the value π is always treated as rounded floating point number. It makes no difference using <tt>PI()</tt> or <tt>RADIANS(180)</tt>. Calc cannot evaluate π symbolically as computer algebra systems do. That is no special limitation of Calc, but other often used spreadsheet applications work only numerically, too.

{| class="wikitable"
|-
! !! A !! B !! C
|-
! 1
| 1.63317787283838E+016 || =TAN(PI()/2) ||
|-
! 2
| 1.22460635382238E-016 || =SIN(RADIANS(180)) ||
|-
! 3
| || ||
|}

== No Fractional Arithmetic ==

ToDo: Explain why 1/7 + 1/3 results in 1/2, using format code # ?/?

== Cancellation ==

If you subtract two non integer numbers, which have nearly the same value, the result has less significant figures then the initial values.

{| class="wikitable"
|-
! !! A !! B !! C
|-
! 1
| 9.99411764795882E-001 || =0.999411764795882 ||
|-
! 2
| 9.99411764705882E-001 || =1699/1700 ||
|-
! 3
| 8.99997854020285E-011 || =A1-A2 ||
|-
! 4
| 8.99996470588235E-011 || ||
|}

Cell A4 shows the correct result of <math>\scriptstyle 0.999411764795882-1699/1700</math>, calculated with a computer algebra system with high precision.

{| class="wikitable"
|-
! !! A !! B !! C
|-
! 1
| 0.99999876543210000000 || ||
|-
! 2
| 0.00000123456790002141 || =1-A1 ||
|-
! 3
| || ||
|-
! 4
| || ||
|}

Calculating manually gives 1−0.99999876543210000000 = 0.00000123456790000000

== Sensitivity ==

Trigonometric functions are very sensitive for huge input values. That means, the results changes noticeable, if the input varies with one or two bit in the internal representation.

Example of formula input
{| class="wikitable"
|-
! !! A !! B !! C
|-
! 1
| =2^44-0.004 || =sin(A1) ||
|-
! 2
| =2^44 || =sin(A2) ||
|-
! 3
| =2^44+0.004 || =sin(A3) ||
|-
! 4
| || ||
|}

Results shown in 15 figures precision
{| class="wikitable"
|-
! !! A !! B !! C
|-
! 1
| 17592186044416.00
|| 0.386569623644289
||
|-
! 2
| 17592186044416.00
|| 0.390169244205272
||
|-
! 3
| 17592186044416.00
|| 0.393762911263629
||
|-
! 4
| || ||
|}

The correct value rounded to 15 figures precision would be sin(2^44) = 0.390169223351877

The trigonometric functions are reliable for integral arguments up to 2^27 with at least 13 figures accuracy and for fractional arguments up to 2^27 with at least 8 figures. For larger values the accuracy decreases to about 6 figures accuracy for integral arguments and 2 figures accuracy for fractional values reaching 2^44. Huge arguments in the area above 2^44 might be totally wrong. If you need trigonometric functions for huge arguments, a spreadsheet is the wrong application for you.

The trigonometric functions are similar sensitive for arguments near their roots, but for those arguments the absolute result is near zero. So you will not notice the error, if you work with rounding to 15 or less decimal places.

Documentation/SL/How Tos/Calc: funkcija ZTEST

2010-05-17T13:22:07Z

Mwaller:

__NOTOC__

== ZTEST ==
Vrne rezultat z-preizkusa.

=== Skladnja: ===
<tt>'''ZTEST(podatki; μ; σ)'''</tt>
: <tt>'''podatki'''</tt> is a range or array containing a random sample from a population (population assumed to have a normal distribution).

: <tt>'''μ'''</tt> je (znana) srednja vrednost populacije.

: <tt>'''σ'''</tt> je znani standardni odklon celotne populacije. Če tega argumenta ni, bo ocenjen iz vzorčnih podatkov z <tt>'''STDEV(podatki)'''</tt>.

: <tt>'''ZTEST'''</tt> izračuna z-statistiko:
:: [[Image:Calc_z_formula.png]]

: kjer je ''m'' je sample srednja vrednost in ''n'' število v vzorcu. Če sta srednja vrednost in standardni odklon populacije znana, z-statistika forms a standard normal distribution - that is, a normal distribution s srednjo vrednostjo=0 in standardnim odklonom=1.

: <tt>'''ZTEST'''</tt> vrne '''one-sided''' cumulative probability - the area under the standard normal curve to the right of the z value (shaded blue here):
<center>[[Image:Calc_ztest_graph2.png]] [[Image:Calc_ztest_graph1.png]] </center>

=== Example: ===
<tt>'''ZTEST(A2:A20; 9; 2)'''</tt>
: returns the result of a z-test on a sample A2:A20 drawn from a population with known mean 9 and known standard deviation 2.

=== Issues: ===
* Excel has referred to this as both a one-tailed and a two-tailed test. Neither is correct - the test is '''one-sided''' as described above. For example when z = -1.5 part of the left tail and all of the right tail are included. This is an unconventional measure, but can be used.
* Calc has produced incorrect results for the 3 parameter version of this function. See [http://www.openoffice.org/issues/show_bug.cgi?id=90759 issue 90759].
* As with all statistics, a good understanding is needed for reliable results.

{{Documentation/SeeAlso|
* [[Documentation/SL/How_Tos/Calc: funkcija TTEST|TTEST]]

* [[Documentation/SL/How_Tos/Calc: Statistične funkcije|Statistične funkcije]]

* [[Documentation/SL/How_Tos/Calc: Funkcije po abecedi|Funkcije po abecedi]]
* [[Documentation/SL/How_Tos/Calc: Funkcije po kategorijah|Funkcije po kategorijah]]}}
[[Category: SL/Documentation/Reference/Calc]]

Documentation/How Tos/Calc: VALUE function

2010-05-17T13:13:26Z

Mwaller:

__NOTOC__

== VALUE ==
Returns a number, given a text representation.

=== Syntax: ===
<tt>'''VALUE(text)'''</tt>
: converts <tt>'''text'''</tt> to a number. <tt>'''text'''</tt> may represent: a number, including fractions, exponential notation and (locale dependent) decimal point and thousands separator; a percentage; or a date or time (the datetime serial number is returned).

=== Example: ===
<tt>'''VALUE("12")'''</tt>
: returns <tt>'''12'''</tt> as a number.

<tt>'''VALUE("1,234.56")'''</tt>
: returns <tt>'''1234.56'''</tt> if your locale recognises the thousands separator ',' and decimal point '.'.

<tt>'''VALUE("1 1/2")'''</tt>
: returns <tt>'''1.5'''</tt> (fraction).

<tt>'''VALUE("1E3")'''</tt>
: returns <tt>'''1000'''</tt> (exponential notation).

<tt>'''VALUE("50%")'''</tt>
: returns <tt>'''0.5'''</tt> (percentage).

<tt>'''VALUE("2009-04-03")'''</tt>
: returns the datetime serial number for 3rd April 2009.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: TEXT function|TEXT]]
* [[Documentation/How_Tos/Calc: T function|T]]
* [[Documentation/How_Tos/Calc: N function|N]]
* [[Documentation/How_Tos/Calc: DATEVALUE function|DATEVALUE]]
* [[Documentation/How_Tos/Calc: TIMEVALUE function|TIMEVALUE]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: UPPER function

2010-05-17T13:12:51Z

Mwaller:

__NOTOC__

== UPPER ==
Converts a text string to uppercase.

=== Syntax: ===
<tt>'''UPPER(text)'''</tt>
: returns <tt>'''text'''</tt> with all characters converted to upper case.

=== Example: ===
<tt>'''UPPER("Good Morning")'''</tt>
: returns <tt>'''GOOD MORNING'''</tt>

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LOWER function|LOWER]]
* [[Documentation/How_Tos/Calc: PROPER function|PROPER]]
* [[Documentation/How_Tos/Calc: TRIM function|TRIM]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: TRIM function

2010-05-17T13:12:20Z

Mwaller:

__NOTOC__

== TRIM ==
removes excess spaces from a text string.

=== Syntax: ===
<tt>'''TRIM(text)'''</tt>
: returns <tt>'''text'''</tt> with any leading or trailing spaces removed, and with any multiple spaces replaced with a single space.

=== Example: ===
<tt>'''TRIM("    Good    Morning   ")'''</tt>
: returns <tt>'''Good Morning'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CLEAN function|CLEAN]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: PROPER function|PROPER]]
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: TEXT function

2010-05-17T13:11:46Z

Mwaller:

__NOTOC__

== TEXT ==
Converts a number into text according to a given format.

=== Syntax: ===
<tt>'''TEXT(number; format)'''</tt>
: returns <tt>'''number'''</tt> converted to text, according to the format code specified by <tt>'''format'''</tt>.

: Example format codes are found in the '''Format - Cells...''' dialogue, in the box labelled Format Code. These depend on your locale.

: This function should therefore '''not''' be regarded as portable.

=== Example: ===
<tt>'''TEXT(12.34567;"###.##")'''</tt>
: returns the text <tt>'''12.35'''</tt>, if in your locale <tt>'''###.##'''</tt> is a format code meaning "up to three integer digits and at most two decimal digits".

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: T function|T]]
* [[Documentation/How_Tos/Calc: VALUE function|VALUE]]
* [[Documentation/How_Tos/Calc: N function|N]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: T function

2010-05-17T13:11:08Z

Mwaller:

__NOTOC__

== T ==
Returns the given text, or an empty text string if the target is not text.

=== Syntax: ===
<tt>'''T(value)'''</tt>
: if <tt>'''value'''</tt> is text, it is returned.

: if <tt>'''value'''</tt> is a number or a logical value, an empty text string <tt>'''""'''</tt> is returned.

=== Example: ===
<tt>'''T("red car")'''</tt>
: returns <tt>'''red car'''</tt>.

<tt>'''T(123.4)'''</tt>
: returns an empty text string.

<tt>'''ISTEXT(T(123.4))'''</tt>
: returns <tt>'''TRUE'''</tt>.

<tt>'''T(NA())'''</tt>
: returns the <tt>'''#N/A'''</tt> error. Errors are propagated.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: TEXT function|TEXT]]
* [[Documentation/How_Tos/Calc: N function|N]]
* [[Documentation/How_Tos/Calc: VALUE function|VALUE]]

* [[Documentation/How_Tos/Calc: ISTEXT function|ISTEXT]]
* [[Documentation/How_Tos/Calc: NA function|NA]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: SUBSTITUTE function

2010-05-17T13:10:36Z

Mwaller:

__NOTOC__

== SUBSTITUTE ==
Substitutes new text for old text in a text string.

=== Syntax: ===
<tt>'''SUBSTITUTE(originaltext; oldtext; newtext; which)'''</tt>
: In <tt>'''originaltext'''</tt>, removes <tt>'''oldtext'''</tt>, inserts <tt>'''newtext'''</tt> in its place, and returns the result. <tt>'''oldtext'''</tt> and <tt>'''newtext'''</tt> can have different lengths.

: <tt>'''which'''</tt> (optional) is a number which specifies which occurrence of <tt>'''oldtext'''</tt> to replace (counting from the left). If omitted, all occurrences are replaced.

=== Example: ===
<tt>'''SUBSTITUTE("castle"; "stl"; "v")'''</tt>
: returns <tt>'''cave'''</tt>.

<tt>'''SUBSTITUTE("a red red rose"; "red"; "white")'''</tt>
: returns <tt>'''a white white rose'''</tt>. All occurrences of <tt>'''red'''</tt> are replaced if <tt>'''which'''</tt> is omitted.

<tt>'''SUBSTITUTE("a red red rose"; "red"; "and white"; 2)'''</tt>
: returns <tt>'''a red and white rose'''</tt>. The second occurrence of <tt>'''red'''</tt> is replaced.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: SEARCH function

2010-05-17T13:10:02Z

Mwaller:

__NOTOC__

== SEARCH ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''SEARCH(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is '''not''' case-sensitive.

: The search will use regular expressions, if they are enabled ('''Tools - Options - OpenOffice.org Calc - Calculate''').

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for Search criteria = and <>must apply to whole cells has no effect.

=== Example: ===
<tt>'''SEARCH("yo"; "Yoyo")'''</tt>
: returns <tt>'''1'''</tt>. The search is case-insensitive.

<tt>'''SEARCH("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''SEARCH("t.n"; "often")'''</tt>
: returns <tt>'''3'''</tt>, if regular expressions are enabled. The "." stands for any single character in a regular expression, so <tt>'''"t.n"'''</tt> matches <tt>'''"ten"'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: ROMAN function

2010-05-17T13:09:23Z

Mwaller:

__NOTOC__

== ROMAN ==
Returns a Roman numeral (eg XIV), as text, given a number.

=== Syntax: ===
<tt>'''ROMAN(number; mode)'''</tt>
: <tt>'''number'''</tt> is a number in the range 1-3999 that is to be converted into a Roman numeral.
: <tt>'''mode'''</tt> (optional, in the range 0-4) indicates the degree of simplification. The higher the value, the greater is the simplification of the Roman number.

=== Example: ===
<tt>'''ROMAN(999)'''</tt>
: returns <tt>'''CMXCIX'''</tt>.

<tt>'''ROMAN(999; 0)'''</tt>
: returns <tt>'''CMXCIX'''</tt>.

<tt>'''ROMAN(999; 1)'''</tt>
: returns <tt>'''LMVLIV'''</tt>.

<tt>'''ROMAN(999; 2)'''</tt>
: returns <tt>'''XMIX'''</tt>.

<tt>'''ROMAN(999; 3)'''</tt>
: returns <tt>'''VMIV'''</tt>.

<tt>'''ROMAN(999; 4)'''</tt>
: returns <tt>'''IM'''</tt>.

=== Issues: ===
This function is currently shown in the Text category of the Function Wizard.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: ARABIC function|ARABIC]]

* [[Documentation/How_Tos/Calc: Number Conversion functions|Number Conversion functions]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Number Conversion functions]]
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: RIGHT function

2010-05-17T13:08:28Z

Mwaller:

__NOTOC__

== RIGHT ==
Returns text from the right side of a text string.

=== Syntax: ===
<tt>'''RIGHT(text; number)'''</tt>
: returns <tt>'''number'''</tt> characters from the right side of the text <tt>'''text'''</tt>.

: <tt>'''number'''</tt> defaults to <tt>'''1'''</tt> if omitted.

=== Example: ===
<tt>'''RIGHT("output"; 3)'''</tt>
: returns <tt>'''put'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LEFT function|LEFT]]
* [[Documentation/How_Tos/Calc: MID function|MID]]
* [[Documentation/How_Tos/Calc: LEN function|LEN]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: REPT function

2010-05-17T13:07:46Z

Mwaller:

__NOTOC__

== REPT ==
returns a text string comprised of copies of another text string.

=== Syntax: ===
<tt>'''REPT(text; number)'''</tt>
: returns <tt>'''number'''</tt> copies of <tt>'''text'''</tt>, joined together.

=== Example: ===
* <tt>'''REPT("ha"; 3)'''</tt>
:: returns <tt>'''hahaha'''</tt>.
* <tt>'''REPT(ROUND(RAND()*10); 3)'''</tt>
:: returns <tt>'''nnn'''</tt> where <tt>'''n'''</tt> is a random digit repeated three times, while:
::: <tt>'''CONCATENATE(ROUND(RAND()*10); ROUND(RAND()*10); ROUND(RAND()*10))'''</tt>
:: returns <tt>'''xyz'''</tt> where <tt>'''x'''</tt>, <tt>'''y'''</tt>, <tt>'''z'''</tt> are three randomly independent digits.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CONCATENATE function|CONCATENATE]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: REPLACE function

2010-05-17T13:07:06Z

Mwaller:

__NOTOC__

== REPLACE ==
Replaces part of a text string with a different text string.

=== Syntax: ===
<tt>'''REPLACE(originaltext; startposition; length; newtext)'''</tt>
: in <tt>'''originaltext'''</tt>, removes <tt>'''length'''</tt> characters beginning at character <tt>'''startposition'''</tt>, replaces them with <tt>'''newtext'''</tt>, and returns the result.

: <tt>'''startposition'''</tt> and <tt>'''length'''</tt> must be <tt>'''1'''</tt> or more.

=== Example: ===
<tt>'''REPLACE("mouse"; 2; 3; "ic")'''</tt>
: returns <tt>'''mice'''</tt>. Beginning at character position 2, 3 characters (<tt>'''ous'''</tt>) are removed and replaced by <tt>'''ic'''</tt>.

=== Issues: ===
* Excel and the forthcoming ODFF standard allow <tt>'''length'''</tt> to be zero.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: PROPER function

2010-05-17T13:06:26Z

Mwaller:

__NOTOC__

== PROPER ==
returns text with words in lowercase after a capitalised first letter.

=== Syntax: ===
<tt>'''PROPER(text)'''</tt>
: returns <tt>'''text'''</tt> with the first character of each word capitalised, and other characters in lower case.

=== Example: ===
<tt>'''PROPER("gooD morNINg")'''</tt>
: returns <tt>'''Good Morning'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LOWER function|LOWER]]
* [[Documentation/How_Tos/Calc: UPPER function|UPPER]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: MID function

2010-05-17T13:05:56Z

Mwaller:

__NOTOC__

== MID ==
Returns text from the middle of a text string.

=== Syntax: ===
<tt>'''MID(text; start; number)'''</tt>
: returns <tt>'''number'''</tt> characters from the text <tt>'''text'''</tt>, starting at position <tt>'''start'''</tt>.

=== Example: ===
<tt>'''MID("output"; 3; 2)'''</tt>
: returns <tt>'''tp'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LEFT function|LEFT]]
* [[Documentation/How_Tos/Calc: RIGHT function|RIGHT]]
* [[Documentation/How_Tos/Calc: LEN function|LEN]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: LOWER function

2010-05-17T13:05:22Z

Mwaller:

__NOTOC__

== LOWER ==
Converts a text string to lowercase.

=== Syntax: ===
<tt>'''LOWER(text)'''</tt>
: returns <tt>'''text'''</tt> with all characters converted to lower case.

=== Example: ===
<tt>'''LOWER("Good MORNING")'''</tt>
: returns <tt>'''good morning'''</tt>

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: UPPER function|UPPER]]
* [[Documentation/How_Tos/Calc: PROPER function|PROPER]]
* [[Documentation/How_Tos/Calc: TRIM function|TRIM]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: LEN function

2010-05-17T13:04:58Z

Mwaller:

__NOTOC__

== LEN ==
Returns the length of a text string.

=== Syntax: ===
<tt>'''LEN(text)'''</tt>
: returns the number of characters in the text <tt>'''text'''</tt>, including spaces.

=== Example: ===
<tt>'''LEN("red car")'''</tt>
: returns <tt>'''7'''</tt>.

<tt>'''LEN(123.4)'''</tt>
: returns <tt>'''5'''</tt> (numbers are converted to text).

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LEFT function|LEFT]]
* [[Documentation/How_Tos/Calc: RIGHT function|RIGHT]]
* [[Documentation/How_Tos/Calc: MID function|MID]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: LEFT function

2010-05-17T13:04:22Z

Mwaller:

__NOTOC__

== LEFT ==
Returns text from the left side of a text string.

=== Syntax: ===
<tt>'''LEFT(text; number)'''</tt>
: returns <tt>'''number'''</tt> characters from the left side of the text <tt>'''text'''</tt>.

: <tt>'''number'''</tt> defaults to <tt>'''1'''</tt> if omitted.

=== Example: ===
<tt>'''LEFT("output"; 3)'''</tt>
: returns <tt>'''out'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: RIGHT function|RIGHT]]
* [[Documentation/How_Tos/Calc: MID function|MID]]
* [[Documentation/How_Tos/Calc: LEN function|LEN]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIXED function

2010-05-17T13:03:53Z

Mwaller:

__NOTOC__

== FIXED ==
Returns a number as text with a specified format.

=== Syntax: ===
<tt>'''FIXED(number; decimals; omitseparators)'''</tt>
: returns text representing <tt>'''number'''</tt> with <tt>'''decimals'''</tt> decimal places. If <tt>'''omitseparators'''</tt> (optional) is <tt>'''TRUE'''</tt> thousands separators will be omitted. In the absence of <tt>'''omitseparators'''</tt> thousands separators are included.

: The thousands separator character used is dependent on your locale settings; see '''Tools - Options - Language Settings - Languages'''.

=== Example: ===
<tt>'''FIXED(1234567.89; 3)'''</tt>
: returns <tt>'''1,234,567.890'''</tt> as text, if your locale uses a comma as the thousands separator.

<tt>'''FIXED(1234567.89; 3; TRUE)'''</tt>
: returns <tt>'''1234567.890'''</tt> as text.

=== Issues: ===
* Because it depends on locale settings, <tt>'''FIXED'''</tt> should not be regarded as portable.

* The <tt>'''omitseparators'''</tt> parameter appears in the formula bar as a number, rather than <tt>'''TRUE'''</tt> or <tt>'''FALSE'''</tt>. This does not affect its function.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: TEXT function|TEXT]]
* [[Documentation/How_Tos/Calc: DOLLAR function|DOLLAR]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIND function

2010-05-17T13:03:21Z

Mwaller:

__NOTOC__

== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive.

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: EXACT function

2010-05-17T13:02:46Z

Mwaller:

__NOTOC__

== EXACT ==
returns <tt>'''TRUE'''</tt> if two text strings are identical

=== Syntax: ===
<tt>'''EXACT(text1; text2)'''</tt>
: returns <tt>'''TRUE'''</tt> if the text strings <tt>'''text1'''</tt> and <tt>'''text2'''</tt> are exactly the same (including case).

=== Example: ===
<tt>'''EXACT("red car"; "red car")'''</tt>
: returns <tt>'''TRUE'''</tt>.

<tt>'''EXACT("red car"; "Red Car")'''</tt>
: returns <tt>'''FALSE'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: ISTEXT function|ISTEXT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Information functions|Information functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: DOLLAR function

2010-05-17T13:02:11Z

Mwaller:

__NOTOC__

== DOLLAR ==
Returns text representing a number in your local currency format.

=== Syntax: ===
<tt>'''DOLLAR(number; decimals)'''</tt>
: returns text representing <tt>'''number'''</tt> as currency. <tt>'''decimals'''</tt> (optional, assumed to be <tt>'''2'''</tt> if omitted) sets the number of decimal places.

: '''Tools - Options - Language Settings - Languages - 'Default Currency' '''sets the currency to be used (normally be the currency of your locale).

=== Example: ===
<tt>'''DOLLAR(255)'''</tt>
: returns <tt>'''$255.00'''</tt>, if your currency is US dollars.

<tt>'''DOLLAR(367.456; 2)'''</tt>
: returns <tt>'''$367.46'''</tt>, if your currency is US dollars.

=== Issues: ===
* Because it depends on local settings, <tt>'''DOLLAR'''</tt> should not be regarded as portable.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: FIXED function|FIXED]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: DECIMAL function

2010-05-17T13:01:37Z

Mwaller:

__NOTOC__

== DECIMAL ==
Returns a decimal number, given a text representation and its base radix.

=== Syntax: ===
<tt>'''DECIMAL(text; radix)'''</tt>
: <tt>'''text'''</tt> is text representing a number with the base radix <tt>'''radix'''</tt> (an integer between 2 and 36).

: Any leading spaces and tabs are ignored.

: Letters, if any, may be upper or lower case.

: If <tt>'''radix'''</tt> is 16 (hexadecimal system), any leading <tt>'''0x'''</tt>, <tt>'''0X'''</tt>, <tt>'''x'''</tt> or <tt>'''X'''</tt> is ignored, as is any trailing <tt>'''h'''</tt> or <tt>'''H'''</tt>.

: If <tt>'''radix'''</tt> is 2 (binary system), any trailing <tt>'''b'''</tt> or <tt>'''B'''</tt> is ignored.

=== Example: ===
<tt>'''DECIMAL("00FF"; 16)'''</tt>
: returns <tt>'''255'''</tt> as a number (hexadecimal system).

<tt>'''DECIMAL("101b"; 2)'''</tt>
: returns <tt>'''5'''</tt> as a number (binary system).

=== Issues: ===
This function is currently shown in the Text category of the Function Wizard.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: BASE function|BASE]]
* [[Documentation/How_Tos/Calc: FIXED function|FIXED]]

* [[Documentation/How_Tos/Calc: BIN2DEC function|BIN2DEC]]
* [[Documentation/How_Tos/Calc: HEX2DEC function|HEX2DEC]]
* [[Documentation/How_Tos/Calc: OCT2DEC function|OCT2DEC]]

* [[Documentation/How_Tos/Calc: Number Conversion functions|Number Conversion functions]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Number Conversion functions]]
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: CONCATENATE function

2010-05-17T13:00:45Z

Mwaller:

__NOTOC__

== CONCATENATE ==
Combines several text strings into one string.

=== Syntax: ===
<tt>'''CONCATENATE(text1; text2; ... text30)'''</tt>
: returns up to 30 text strings <tt>'''text1'''</tt> - <tt>'''text30'''</tt>, joined together.

: <tt>'''text1'''</tt> - <tt>'''text30'''</tt> may also be single cell references.

=== Example: ===
<tt>'''CONCATENATE("al"; "tog"; "ether")'''</tt>
: returns <tt>'''altogether'''</tt>.

<tt>'''CONCATENATE(A1; A2)'''</tt>
: where cell A1 contains <tt>'''key'''</tt> and cell A2 contains <tt>'''board'''</tt> returns <tt>'''keyboard'''</tt>.

<tt>'''CONCATENATE(ROUND(RAND()*10); ROUND(RAND()*10); ROUND(RAND()*10))'''</tt>
: returns <tt>'''xyz'''</tt> where <tt>'''x'''</tt>, <tt>'''y'''</tt>, <tt>'''z'''</tt> are three randomly independent digits, while:
:: <tt>'''REPT(ROUND(RAND()*10); 3)'''</tt>
: returns <tt>'''nnn'''</tt> where <tt>'''n'''</tt> is a random digit repeated three times.

<tt>'''CONCATENATE("Print Date: " ; TEXT(TODAY();"dddd, d mmmm yyyy"))</tt>
: returns <tt>'''Print Date: Thursday, 1 January 2009'''</tt> (assuming today's date was Jan 1, 2009). This example serves to demonstrate that you can combine functions in quite complex ways. This example is made up of the date function <tt>'''TODAY()'''</tt> which returns the current date as a number (NOTE: Jan 1, 2009 is represented as 39814) and the text function <tt>'''TEXT()'''</tt> which allows you to format the numeric representation of the date in a [useful] textual format.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPT function|REPT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: CODE function

2010-05-17T12:59:49Z

Mwaller:

__NOTOC__

== CODE ==
returns the numeric code for the first character in a text string.

=== Syntax: ===
<tt>'''CODE(text)'''</tt>
: returns the numeric code for the first character of the text string <tt>'''text'''</tt>, in the range 0-255.

: Codes greater than 127 may depend on your system's character mapping (for example iso-8859-1, iso-8859-2, Windows-1252, Windows-1250), and hence may not be portable.

=== Example: ===
<tt>'''CODE("H")'''</tt>
: returns <tt>'''72'''</tt>, the character code for "H".

<tt>'''CODE("Hello")'''</tt>
: also returns <tt>'''72'''</tt>.

{{Documentation/Note|The international standard ODFF will introduce the more versatile functions <tt>'''UNICODE'''</tt> and <tt>'''UNICHAR'''</tt>, which will be implemented in Calc.}}

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CHAR function|CHAR]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: CLEAN function

2010-05-17T12:59:18Z

Mwaller:

__NOTOC__

== CLEAN ==
returns a text string with non-printable characters removed.

=== Syntax: ===
<tt>'''CLEAN(text)'''</tt>
: returns <tt>'''text'''</tt> with all non-printable characters removed.

: Spaces are not removed.

=== Example: ===
where cell A1 contains <tt>'''=CHAR(7) & "cat" & CHAR(8)'''</tt>:

<tt>'''CLEAN(A1)'''</tt>
: returns <tt>'''cat'''</tt>. The 2 non-printable characters are removed.

<tt>'''LEN(CLEAN(A1))'''</tt>
: returns <tt>'''3'''</tt>, the number of characters in <tt>'''cat'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: TRIM function|TRIM]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: PROPER function|PROPER]]
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: LEN function|LEN]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: CHAR function

2010-05-17T12:58:19Z

Mwaller:

__NOTOC__

== CHAR ==
Returns a single text character, given a character code.

=== Syntax: ===
<tt>'''CHAR(number)'''</tt>
: <tt>'''number'''</tt> is the character code, in the range 1-255.

: <tt>'''CHAR'''</tt> uses your system's character mapping (for example iso-8859-1, iso-8859-2, Windows-1252, Windows-1250) to determine which character to return. Codes greater than 127 may not be portable.

=== Example: ===
<tt>'''CHAR(72)'''</tt>
: returns <tt>'''H'''</tt>, representing character code 72.

{{Documentation/Note|The international standard ODFF will introduce the more versatile functions <tt>'''UNICODE'''</tt> and <tt>'''UNICHAR'''</tt>, which will be implemented in Calc.}}

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CODE function|CODE]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: BASE function

2010-05-17T12:57:48Z

Mwaller:

__NOTOC__

== BASE ==
Returns a text representation of a number, in a specified base radix.

=== Syntax: ===
<tt>'''BASE(number; radix; minlength)'''</tt>
: converts <tt>'''number'''</tt> (a positive integer) to text, with the base radix <tt>'''radix'''</tt> (an integer between 2 and 36), using characters <tt>'''0-9'''</tt> and <tt>'''A-Z'''</tt>.

: <tt>'''minlength'''</tt> (optional) specifies the minimum number of characters returned; zeroes are added on the left if necessary.

=== Example: ===
<tt>'''BASE(17; 2)'''</tt>
: returns <tt>'''10001'''</tt> as text (binary system).

<tt>'''BASE(17; 10)'''</tt>
: returns <tt>'''17'''</tt> as text (decimal system).

<tt>'''BASE(255; 16; 4) '''</tt>
: returns <tt>'''00FF'''</tt> as text (hexadecimal system). Extra zeroes are added to give 4 characters.

<tt>'''ISTEXT(BASE(17; 10))'''</tt>
: returns <tt>'''TRUE'''</tt>. Text is returned, not a number.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: DECIMAL function|DECIMAL]]
* [[Documentation/How_Tos/Calc: FIXED function|FIXED]]

* [[Documentation/How_Tos/Calc: BIN2DEC function|BIN2DEC]]
* [[Documentation/How_Tos/Calc: BIN2HEX function|BIN2HEX]]
* [[Documentation/How_Tos/Calc: BIN2OCT function|BIN2OCT]]

* [[Documentation/How_Tos/Calc: ISTEXT function|ISTEXT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: BAHTTEXT function

2010-05-17T12:56:55Z

Mwaller:

__NOTOC__

== BAHTTEXT ==
Returns Thai text, including the Thai currency names, given a number.

=== Syntax: ===
<tt>'''BAHTTEXT(number)'''</tt>
: <tt>'''number'''</tt> is any number. <tt>'''BAHTTEXT'''</tt> returns a string in Thai characters, with "Baht" appended to the integer part of the number, and "Satang" appended to the decimal part of the number.

=== Example: ===
<tt>'''BAHTTEXT("12.65")'''</tt>
: returns a string of Thai characters with the meaning of "Twelve Baht and sixty five Satang".

=== Issues: ===
<tt>'''BAHTTEXT'''</tt> is not included in the forthcoming ODFF international standard. It is (at least partially) implemented in Excel.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: Number Conversion functions|Number Conversion functions]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Number Conversion functions]]
[[Category: Documentation/Reference/Calc/Text functions]]

Category:Documentation/Reference/Calc/Text functions

2010-05-17T12:56:11Z

Mwaller: Created page with 'Category: Documentation/Reference/Calc'

[[Category: Documentation/Reference/Calc]]

Documentation/How Tos/Calc: ARABIC function

2010-05-17T12:55:44Z

Mwaller:

__NOTOC__

== ARABIC ==
Returns an Arabic number (eg 14), given a Roman number (eg XIV).

=== Syntax: ===
<tt>'''ARABIC(text)'''</tt>
: returns the Roman numeral <tt>'''text'''</tt> as a number, limited to the range 1-3999.

=== Example: ===
<tt>'''ARABIC("MXIV")'''</tt>
: returns <tt>'''1014'''</tt>.

<tt>'''ARABIC("MMII") '''</tt>
: returns <tt>'''2002'''</tt>.

=== Issues: ===
This function is currently shown in the Text category of the Function Wizard.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: ROMAN function|ROMAN]]

* [[Documentation/How_Tos/Calc: Number Conversion functions|Number Conversion functions]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Number Conversion functions]]
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: SMALL function

2010-05-17T12:53:09Z

Mwaller:

__NOTOC__

== SMALL ==
Returns the nth smallest value in a list of numbers.

=== Syntax: ===
<tt>'''SMALL(numberlist; n)'''</tt>

: returns the <tt>'''n'''</tt>th smallest number within the (unordered) range or array of numbers <tt>'''numberlist'''</tt>.

=== Example: ===

<tt>'''SMALL(B1:B4; 3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''4'''</tt>, <tt>'''7'''</tt>, <tt>'''5'''</tt>,and <tt>'''8'''</tt>, returns <tt>'''7'''</tt>. The lowest number is <tt>'''4'''</tt>, then <tt>'''5'''</tt>, then the third lowest is <tt>'''7'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LARGE function|LARGE]]
* [[Documentation/How_Tos/Calc: RANK function|RANK]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: RANK function

2010-05-17T12:52:20Z

Mwaller:

__NOTOC__

== RANK ==
Returns the rank of a number in a list of numbers.

=== Syntax: ===
<tt>'''RANK(number; numberlist; order)'''</tt>

: returns the rank of <tt>'''number'''</tt> within the unordered range or array of numbers <tt>'''numberlist'''</tt>.

: If <tt>'''order'''</tt> is zero or omitted, numbers are ranked with the highest being first; if <tt>'''order'''</tt> is non-zero, numbers are ranked with the lowest being first.

: An error results if <tt>'''number'''</tt> is not present in <tt>'''numberlist'''</tt>.

=== Example: ===

<tt>'''RANK(5; B1:B4)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''4'''</tt>, <tt>'''7'''</tt>, <tt>'''5'''</tt>,and <tt>'''8'''</tt>, returns <tt>'''3'''</tt>. The highest number is <tt>'''8'''</tt>, then <tt>'''7'''</tt>, then in third place <tt>'''5'''</tt>.

<tt>'''RANK(5; {3;5|2.6;1}; 1)'''</tt>
: returns <tt>'''4'''</tt>, as the other three numbers are lower than <tt>'''5'''</tt>.

===Issues: ===
* By design, <tt>'''RANK'''</tt> returns the same rank for any value that is repeated in <tt>'''numberlist'''</tt>. If you wish a unique rank for each number in (say) <tt>'''A1:A4'''</tt>, enter <tt>'''=RANK(A1;$A$1:$A$4)+COUNTIF($A$1:A1;A1)-1'''</tt> in cell <tt>'''B1'''</tt> and copy/paste into <tt>'''B2:B4'''</tt>.
* Calc assumes that any logical values are <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>). Excel's behaviour with logical values may be 'unexpected'.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LARGE function|LARGE]]
* [[Documentation/How_Tos/Calc: SMALL function|SMALL]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]
* [[Documentation/How_Tos/Calc: PERCENTRANK function|PERCENTRANK]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: QUARTILE function

2010-05-17T12:51:24Z

Mwaller:

__NOTOC__

== QUARTILE ==
Returns a specified quartile in a list of numbers.

=== Syntax: ===
<tt>'''QUARTILE(numberlist; q)'''</tt>

: <tt>'''QUARTILE'''</tt> returns the value below which <tt>'''q'''</tt> quarters of the numbers in <tt>'''numberlist'''</tt> lie. <tt>'''q'''</tt> may be 0 (to return the minimum value), 1 (to return the value below which a quarter of values lie), 2 (to return the median value), 3 (to return the value below which three quarters of values lie) or 4 (to return the maximum value). <tt>'''numberlist'''</tt> is a range or array of numbers, not necessarily in order.

: <tt>'''QUARTILE'''</tt> calculates a return value in the same way as <tt>[[Documentation/How_Tos/Calc: PERCENTILE function|PERCENTILE]]</tt>

=== Example: ===
<tt>'''QUARTILE(A2:A99; 3)'''</tt>
: where cells A2:A99 contain scores from a test, returns the score below which 75% of the scores lie.

The next two examples illustrate the calculation, but are not intended to show practical application:

<tt>'''QUARTILE({3; 4; 9; 11; 12}; 2)'''</tt>
: returns <tt>'''9'''</tt>, the median value of the list.

<tt>'''QUARTILE({9; 3; 7; 8}; 1)'''</tt>
: returns <tt>'''6'''</tt>. The calculation is the same as for <tt>'''PERCENTILE({9; 3; 7; 8}; 0.25)'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: PERCENTILE function|PERCENTILE]]
* [[Documentation/How_Tos/Calc: PERCENTRANK function|PERCENTRANK]]
* [[Documentation/How_Tos/Calc: RANK function|RANK]]
* [[Documentation/How_Tos/Calc: MEDIAN function|MEDIAN]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]
* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: PERCENTRANK function

2010-05-17T12:50:47Z

Mwaller:

__NOTOC__

== PERCENTRANK ==
Returns the percentage rank of a number in a list of numbers.

=== Syntax: ===
<tt>'''PERCENTRANK(numberlist; number)'''</tt>

: <tt>'''number'''</tt> is the number whose percentage rank is to be found within the unordered range or array of numbers <tt>'''numberlist'''</tt>.

: <tt>'''PERCENTRANK'''</tt> internally assigns a rank ''r'' to <tt>'''number'''</tt>, where 0 is the rank of the lowest number, 1 of the next lowest and so on. If <tt>'''number'''</tt> is not in <tt>'''numberlist'''</tt>, it is assigned a fractional rank proportionately between the rank of the numbers on either side (see the examples). Where ''N'' is the count of numbers in <tt>'''numberlist'''</tt>, <tt>'''PERCENTRANK'''</tt> returns ''r'' / (''N''-1), which is a fraction between 0 and 1 inclusive.

: This function is of limited use with a small list of numbers. It may be useful for example when determining where a score lies within all scores for a test, in which case the formula returned can be stated as ''L'' / (''N''-1), where ''L'' is the count of scores which are lower than <tt>'''number'''</tt>.

=== Example: ===
<tt>'''PERCENTRANK(A2:A99; A7)'''</tt>
: where cells A2:A99 contain scores from a test, returns the percent rank (as a fraction) for the student whose test score is in cell A7.

The next two examples illustrate the calculation, but have little practical application:

<tt>'''PERCENTRANK({3; 4; 9; 11; 12}; 4)'''</tt>
: returns <tt>'''0.25'''</tt>.

<tt>'''PERCENTRANK({9; 3; 7; 8}; 6)'''</tt>
: returns <tt>'''0.25'''</tt>. The lowest number <tt>'''3'''</tt> is assigned rank '0'; the next highest number <tt>'''7'''</tt> is assigned rank '1'. <tt>'''6'''</tt> is assigned a rank proportionately between these, rank '0.75'. There are 4 numbers in the list, so the result is calculated as 0.75/(4-1) = 0.75/3 = 0.25.

===Issues: ===
* Despite its name, <tt>'''PERCENTRANK'''</tt> returns a fraction, not a percentage.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: RANK function|RANK]]
* [[Documentation/How_Tos/Calc: PERCENTILE function|PERCENTILE]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: PERCENTILE function

2010-05-17T12:49:51Z

Mwaller:

__NOTOC__

== PERCENTILE ==
Returns a specified percentile in a list of numbers.

=== Syntax: ===
<tt>'''PERCENTILE(numberlist; fraction)'''</tt>

: <tt>'''PERCENTILE'''</tt> returns the value below which <tt>'''fraction'''</tt> of the numbers in <tt>'''numberlist'''</tt> lie. <tt>'''fraction'''</tt> must be between 0 and 1 inclusive; <tt>'''numberlist'''</tt> is a range or array of numbers, not necessarily in order.

: <tt>'''PERCENTILE'''</tt> internally assigns a rank to each number in <tt>'''numberlist'''</tt>, where 0 is the rank of the lowest number, 1 of the next lowest and so on. The rank ''r'' of the value to be found is <tt>'''fraction'''</tt> * (''N''-1), where ''N'' is the count of numbers in <tt>'''numberlist'''</tt>. If ''r'' is integer the corresponding value from <tt>'''numberlist'''</tt> is returned; otherwise the value is calculated proportionately between the values with rank |''r''| and |''r''|+1 (see the examples).

: This function may be useful for example when determining a score below which lies a certain percentage of all scores for a test. It is of limited use with a small list of numbers.

: <tt>'''PERCENTILE'''</tt> returns the maximum, median, minimum value when <tt>'''fraction'''</tt> is 1, 0.5, 0 respectively.

=== Example: ===
<tt>'''PERCENTILE(A2:A99; 0.6)'''</tt>
: where cells A2:A99 contain scores from a test, returns the score below which 60% of the scores lie.

The next two examples illustrate the calculation, but are not intended to show practical application:

<tt>'''PERCENTILE({3; 4; 9; 11; 12}; 0.5)'''</tt>
: returns <tt>'''9'''</tt>, the median value of the list.

<tt>'''PERCENTILE({9; 3; 7; 8}; 0.25)'''</tt>
: returns <tt>'''6'''</tt>. There are 4 numbers in the list. The rank of the value to be found is 0.25 * (4-1) = 0.75. The lowest number <tt>'''3'''</tt> is assigned rank 0; the next highest number <tt>'''7'''</tt> is assigned rank 1. The value with rank 0.75 lies proportionately between 3 and 7, and is calculated as 3 + (7-3)*0.75 = 6.

===Issues: ===
* Despite its name, <tt>'''PERCENTILE'''</tt> requires a fraction, not a percentage.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: QUARTILE function|QUARTILE]]
* [[Documentation/How_Tos/Calc: PERCENTRANK function|PERCENTRANK]]
* [[Documentation/How_Tos/Calc: RANK function|RANK]]
* [[Documentation/How_Tos/Calc: MEDIAN function|MEDIAN]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: MINA function

2010-05-17T12:48:53Z

Mwaller:

__NOTOC__

== MINA ==
Returns the minimum of a list of arguments, including text and logical entries.

=== Syntax: ===
<tt>'''MINA(value1; value2; ... value30)'''</tt>

: <tt>'''value1'''</tt> to <tt>'''value30'''</tt> are up to 30 values or ranges, which may include numbers, text and logical values. Text is evaluated as <tt>'''0'''</tt>. Logical values are evaluated as <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>).

=== Example: ===

<tt>'''MINA(2; 6; 4)'''</tt>
: returns <tt>'''2'''</tt>, the smallest value in the list.

<tt>'''MINA(B1:B3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''3'''</tt>, <tt>'''4'''</tt>, and <tt>'''apple'''</tt> returns <tt>'''0'''</tt>, the value of the text.

<tt>'''MINA(2; TRUE)'''</tt>
: returns <tt>'''1'''</tt>, the value of <tt>'''TRUE'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: MIN function|MIN]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MAXA function|MAXA]]
* [[Documentation/How_Tos/Calc: DMIN function|DMIN]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: MIN function

2010-05-17T12:48:11Z

Mwaller:

__NOTOC__

== MIN ==
Returns the minimum of a list of arguments, ignoring text entries.

=== Syntax: ===
<tt>'''MIN(number1; number2; ... number30)'''</tt>

: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges containing numbers.

=== Example: ===

<tt>'''MIN(2; 6; 4)'''</tt>
: returns <tt>'''2'''</tt>, the smallest value in the list.

<tt>'''MIN(B1:B3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''1.1'''</tt>, <tt>'''2.2'''</tt>, and <tt>'''apple'''</tt> returns <tt>'''1.1'''</tt>.

===Issues: ===
* Calc assumes that logical values are <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>). Excel ignores logical values.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: MINA function|MINA]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MAXA function|MAXA]]

* [[Documentation/How_Tos/Calc: DMIN function|DMIN]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: MAXA function

2010-05-17T12:47:26Z

Mwaller:

__NOTOC__

== MAXA ==
Returns the maximum of a list of arguments, including text and logical entries.

=== Syntax: ===
<tt>'''MAXA(value1; value2; ... value30)'''</tt>

: <tt>'''value1'''</tt> to <tt>'''value30'''</tt> are up to 30 values or ranges, which may include numbers, text and logical values. Text is evaluated as <tt>'''0'''</tt>. Logical values are evaluated as <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>).

=== Example: ===

<tt>'''MAXA(2; 6; 4)'''</tt>
: returns <tt>'''6'''</tt>, the largest value in the list.

<tt>'''MAXA(B1:B3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''-1'''</tt>, <tt>'''-2'''</tt>, and <tt>'''apple'''</tt> returns <tt>'''0'''</tt>, the value of the text.

<tt>'''MAXA(0; TRUE)'''</tt>
: returns <tt>'''1'''</tt>, the value of <tt>'''TRUE'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]
* [[Documentation/How_Tos/Calc: MINA function|MINA]]
* [[Documentation/How_Tos/Calc: DMAX function|DMAX]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: MAX function

2010-05-17T12:46:48Z

Mwaller:

__NOTOC__

== MAX ==
Returns the maximum of a list of arguments, ignoring text entries.

=== Syntax: ===
<tt>'''MAX(number1; number2; ... number30)'''</tt>

: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges containing numbers.

=== Example: ===

<tt>'''MAX(2; 6; 4)'''</tt>
: returns <tt>'''6'''</tt>, the largest value in the list.

<tt>'''MAX(B1:B3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''1.1'''</tt>, <tt>'''2.2'''</tt>, and <tt>'''apple'''</tt> returns <tt>'''2.2'''</tt>.

===Issues: ===
* Calc assumes that logical values are <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>). Excel ignores logical values.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: MAXA function|MAXA]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]
* [[Documentation/How_Tos/Calc: MINA function|MINA]]
* [[Documentation/How_Tos/Calc: DMAX function|DMAX]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: MAX function

2010-05-17T12:46:13Z

Mwaller:

Documentation/How Tos/Calc: LARGE function

2010-05-17T12:45:38Z

Mwaller:

__NOTOC__

== LARGE ==
Returns the nth largest value in a list of numbers.

=== Syntax: ===
<tt>'''LARGE(numberlist; n)'''</tt>

: returns the <tt>'''n'''</tt>th largest number within the (unordered) range or array of numbers <tt>'''numberlist'''</tt>.

=== Example: ===

<tt>'''LARGE(B1:B4; 3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''4'''</tt>, <tt>'''7'''</tt>, <tt>'''5'''</tt>,and <tt>'''8'''</tt>, returns <tt>'''5'''</tt>. The highest number is <tt>'''8'''</tt>, then <tt>'''7'''</tt>, then in third place <tt>'''5'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: SMALL function|SMALL]]
* [[Documentation/How_Tos/Calc: RANK function|RANK]]
* [[Documentation/How_Tos/Calc: MAX function|MAX]]
* [[Documentation/How_Tos/Calc: MIN function|MIN]]

* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}

[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: TREND function

2010-05-17T12:44:41Z

Mwaller:

__NOTOC__

== TREND ==
Fits a straight line to data using linear regression and returns points on that line.

=== Syntax: ===
<tt>'''TREND(yvalues; xvalues; new_xvalues; type)'''</tt>
: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> are single row or column ranges specifying points in a set of data.
: <tt>'''TREND'''</tt> fits a straight line through these data points, using the linear regression method.
: If <tt>'''type'''</tt> is <tt>'''0'''</tt> the straight line found will pass through the origin; if <tt>'''type'''</tt> is non-zero or omitted the best fit straight line will be found.

: <tt>'''TREND'''</tt> returns an array of the y values of the straight line found, corresponding to the x values in <tt>'''new_xvalues'''</tt> (or if omitted <tt>'''xvalues'''</tt>). It must be entered as an [[Documentation/How_Tos/Using Arrays|array formula]] (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''').

: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> must be the same size. <tt>'''new_xvalues'''</tt> may have a different size.

=== Example: ===
[[Image:Calc_trend_example.png|right]]
<tt>'''=TREND(B2:B5; A2:A5; A2:A7)'''</tt>
: when entered as an array formula in cell C2, where the x values in A2:A7 are 1,2,3,4,5, 6 and the y values in B2:B4 are 2, 4, 6.1, 8 returns {2.01|4.02|6.03|8.04|10.05|12.06}. The data points are very nearly on the line y = 2x (and would be if B4 contained 6 instead of 6.1). The best fit line found is therefore very nearly y=2x.
: This example shows how <tt>'''TREND'''</tt> may be used to predict future values.
 

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]]
* [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]]
* [[Documentation/How_Tos/Calc: STEYX function|STEYX]]
* [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]]
* [[Documentation/How_Tos/Calc: Array functions|Array functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Array functions]]
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: LOGEST function

2010-05-17T12:43:41Z

Mwaller:

__NOTOC__

== LOGEST ==
Returns a table of statistics for an exponential curve that best fits a data set.

=== Syntax: ===
<tt>'''LOGEST(yvalues; xvalues; allow_const; stats)'''</tt>
: <tt>'''yvalues'''</tt> is a single row or column range specifying the y coordinates in a set of data points.

: <tt>'''xvalues'''</tt> is a corresponding single row or column range specifying the x coordinates. If <tt>'''xvalues'''</tt> is omitted it defaults to <tt>'''1, 2, 3, ..., n'''</tt>. If there is more than one set of ''x'' variables, <tt>'''xvalues'''</tt> may be a range with corresponding multiple rows or columns.

: <tt>'''LOGEST'''</tt> finds an exponential curve ''y'' = ''a'' . ''bx'' that best fits the data. With more than one set of variables the curve is of the form ''y'' = ''a'' . ''b1x1'' . ''b2x2'' ... . ''bnxn''.

: In order to fit the curve, <tt>'''LOGEST'''</tt> uses linear regression (the "least squares" method) based on the equation ln(''y'') = ln(''a'') + ''x1''ln(''b1'') + ''x2''ln(''b2'') + ... ''xn''ln(''bn'').

: if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the constant ''a'' is forced to be one; ''y'' = ''bx''. If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (a value for ''a'' is found).

: <tt>'''LOGEST'''</tt> returns a table (array) of statistics as below and must be entered as an [[Documentation/How_Tos/Using Arrays|array formula]] (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''')

: If <tt>'''stats'''</tt> is omitted or <tt>'''FALSE'''</tt> only the top line of the statistics table is returned. If <tt>'''TRUE'''</tt> the entire table is returned. The statistics relate to the equation for ln(''y'') given above.

[[Image:Calc_linest_output.png|right]]
: ''b1'' to ''bn'' and ''a'' are the coeficients for the equation above.

: ''σ1'' to ''σn'' are the standard error values for the ln(''b'') values; ''σa'' is the standard error value for the ln(''a'') value.

:''r2'' is the determination coefficient ([[Documentation/How_Tos/Calc: RSQ function|RSQ]]); ''σy'' is the standard error value for the ln(''y'') estimate.

: ''F'' is the F statistic (F-observed value); ''df'' is the number of degrees of freedom.

: ''ssreg'' is the regression sum of squares; ''ssresid'' is the residual sum of squares.

 

=== Example: ===

[[Image:Calc_logest_example.png|center]]
 

:In the example above, cells A2:B6 contain the ''x'',''y'' values for a set of points. <tt>'''LOGEST(B2:B6;A2:A6;1;1)'''</tt> returns the statistics for a best fit exponential curve through those points.

=== Issues: ===
* You need a good understanding of the statistics involved.
* Any empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel).

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LINEST function|LINEST]]

* [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]]

* [[Documentation/How_Tos/Calc: Array functions|Array functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Array functions]]
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: LINEST function

2010-05-17T12:42:44Z

Mwaller:

__NOTOC__

== LINEST ==
Returns a table of statistics for a straight line that best fits a data set.

=== Syntax: ===
<tt>'''LINEST(yvalues; xvalues; allow_const; stats)'''</tt>
: <tt>'''yvalues'''</tt> is a single row or column range specifying the y coordinates in a set of data points.

: <tt>'''xvalues'''</tt> is a corresponding single row or column range specifying the x coordinates. If <tt>'''xvalues'''</tt> is omitted it defaults to <tt>'''1, 2, 3, ..., n'''</tt>. If there is more than one set of variables <tt>'''xvalues'''</tt> may be a range with corresponding multiple rows or columns.

: <tt>'''LINEST'''</tt> finds a straight line ''y'' = ''a'' + ''bx'' that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form ''y'' = ''a'' + ''b1x1'' + ''b2x2'' ... + ''bnxn''.

: if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the straight line found is forced to pass through the origin (the constant ''a'' is zero; ''y'' = ''bx''). If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (the line is not forced through the origin).

: <tt>'''LINEST'''</tt> returns a table (array) of statistics as below and must be entered as an [[Documentation/How_Tos/Using Arrays|array formula]] (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''')

: If <tt>'''stats'''</tt> is omitted or <tt>'''FALSE'''</tt> only the top line of the statistics table is returned. If <tt>'''TRUE'''</tt> the entire table is returned.

[[Image:Calc_linest_output.png|right]]
: ''b1'' to ''bn'' are the line gradients; ''a'' is the ''y''-axis intercept.

: ''σ1'' to ''σn'' are the standard error values for the line gradients; ''σa'' is the standard error value for the ''y''-axis intercept.

:''r2'' is the determination coefficient ([[Documentation/How_Tos/Calc: RSQ function|RSQ]]); ''σy'' is the standard error value for the ''y'' estimate.

: ''F'' is the F statistic (F-observed value); ''df'' is the number of degrees of freedom.

: ''ssreg'' is the regression sum of squares; ''ssresid'' is the residual sum of squares.

 

=== Example: ===

[[Image:Calc_linest_example.png|center]]
 

:In the example above, cells A2:B8 contain the ''x'',''y'' values for a set of points. <tt>'''LINEST(B2:B8;A2:A8;1;1)'''</tt> returns the statistics for the best fit line through those points.

[[Image:Calc_linest_example2.png|center]]
 

: In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells <tt>'''A2:C8'''</tt>). To predict the value of other houses in the area you might use: ''value'' = ''a'' + ''b1''*''floor_area'' + ''b2''*''num_windows'', where ''a'', ''b1'' and ''b2'' are constants. <tt>'''LINEST(A2:A8;B2:C8;1;1)'''</tt> returns appropriate statistics for that equation.

=== Issues: ===
* You need a good understanding of the statistics involved.
* Empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel).

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: LOGEST function|LOGEST]]
* [[Documentation/How_Tos/Calc: TREND function|TREND]]
* [[Documentation/How_Tos/Calc: RSQ function|RSQ]]
* [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]]
* [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]]
* [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]]
* [[Documentation/How_Tos/Calc: Array functions|Array functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Array functions]]
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: GROWTH function

2010-05-17T12:41:36Z

Mwaller:

__NOTOC__

== GROWTH ==
Fits an exponential curve to a data set, and returns points on that curve.

=== Syntax: ===
<tt>'''GROWTH(yvalues; xvalues; new_xvalues; allow_factor)'''</tt>
: <tt>'''data'''</tt> is a range or array containing numerical data.
: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> are single row or column ranges specifying points in a set of data.
: <tt>'''GROWTH'''</tt> fits an exponential curve of the form ''y''=''bmx'' through these data points, using the linear regression method.
: If <tt>'''allow_factor'''</tt> is <tt>'''FALSE'''</tt> the factor ''b'' in this equation is set to <tt>'''1'''</tt>; if <tt>'''allow_factor'''</tt> is non-zero, <tt>'''TRUE'''</tt> or omitted the factor ''b'' is calculated from the data.

: <tt>'''GROWTH'''</tt> returns an array of the y values of the exponential curve found, corresponding to the x values in <tt>'''new_xvalues'''</tt> (or if omitted <tt>'''xvalues'''</tt>). It must be entered as an array formula (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''').

: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> must be the same size. <tt>'''new_xvalues'''</tt> may have a different size.

=== Example: ===
[[Image:Calc_growth_example.png|right]]
<tt>'''=GROWTH(B2:B6; A2:A6; A2:A7)'''</tt>
: when entered as an array formula in cell C2, where the x values in A2:A7 are 4, 5, 6, 7, 8, 9 and the y values in B2:B6 are 1.80, 3.20, 6.20, 12.30, 24.50, finds a best fit exponential curve for those points, and in C2:C7 returns y values on that curve for the x values in A2:A7.

: This example shows how <tt>'''GROWTH'''</tt> may be used to predict future values (A7,C7).
 

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: TREND function|TREND]]
* [[Documentation/How_Tos/Calc: LOGEST function|LOGEST]]

* [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]]

* [[Documentation/How_Tos/Calc: Array functions|Array functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Array functions]]
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: STEYX function

2010-05-17T12:40:05Z

Mwaller:

__NOTOC__

== STEYX ==
Fits a straight line to data using linear regression and returns the standard error of y values.

=== Syntax: ===
<tt>'''STEYX(yvalues; xvalues)'''</tt>
: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> are single row or column ranges specifying points in a set of data. <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> must be the same size.

: <tt>'''STEYX'''</tt> in effect fits a straight line through these data points, using the linear regression method (least squares). It then returns the standard error of actual y values compared to y values on the straight line found.

: The equation of a straight line may be given as y = a + bx. The linear regression method calculates:
:: [[Image:Calc_linregress_b.png]]

: and

:: [[Image:Calc_linregress_a.png]].

: <tt>'''STEYX'''</tt> calculates:
:: [[Image:Calc_steyx_equation.png]]

: where yi are the actual values and yi' are the computed values on the line.

: Advanced topic:
: The parameters <tt>'''xvalues'''</tt> and <tt>'''yvalues'''</tt> are always evaluated as [[Documentation/How_Tos/Using Arrays|array formulas]].

=== Example: ===
<tt>'''STEYX(B2:B6; A2:A6)'''</tt>
: where the x values in A2:A6 are 1, 2, 3, 4, 5 and the y values in B2:B6 are 2, 4, 7, 8, 10 returns approximately <tt>'''0.51'''</tt>. The equation of the straight line found is very nearly y = 2x, and would be if B4 contained 6. The standard error is therefore quite small.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: FORECAST function|FORECAST]]
* [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]]
* [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]]
* [[Documentation/How_Tos/Using Arrays|Array formulas]]
* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: SLOPE function

2010-05-17T12:39:17Z

Mwaller:

__NOTOC__

== SLOPE ==
Fits a straight line to data using linear regression and returns its slope.

=== Syntax: ===
<tt>'''SLOPE(yvalues; xvalues)'''</tt>
: <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> are single row or column ranges specifying points in a set of data. <tt>'''yvalues'''</tt> and <tt>'''xvalues'''</tt> must be the same size.

: <tt>'''SLOPE'''</tt> fits a straight line through these data points, using the linear regression method (least squares). It then returns the slope of that line.

: The equation of a straight line may be given as y = a + bx. The linear regression method calculates:
:: [[Image:Calc_linregress_b.png]]

: and

:: [[Image:Calc_linregress_a.png]].

: b is the slope, returned by this function.

: Advanced topic:
: The parameters <tt>'''xvalues'''</tt> and <tt>'''yvalues'''</tt> are always evaluated as [[Documentation/How_Tos/Using Arrays|array formulas]].

=== Example: ===
<tt>'''SLOPE(B2:B6; A2:A6)'''</tt>
: where the x values in A2:A6 are 1, 2, 3, 4, 5 and the y values in B2:B6 are 2, 4, 6, 8, 11 returns <tt>'''2.2'''</tt>. The equation of the straight line found is very nearly y = 2x - thus the slope is very nearly (but not quite) 2.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: FORECAST function|FORECAST]]
* [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]]
* [[Documentation/How_Tos/Calc: STEYX function|STEYX]]
* [[Documentation/How_Tos/Using Arrays|Array formulas]]
* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]

Documentation/How Tos/Calc: RSQ function

2010-05-17T12:37:31Z

Mwaller:

__NOTOC__

== RSQ ==
Returns the square of the Pearson correlation coefficient of two sets of data.

=== Syntax: ===
<tt>'''RSQ(x; y)'''</tt>
: where <tt>'''x'''</tt> and <tt>'''y'''</tt> are ranges or arrays containing the two sets of data.

: Any text or empty entries are ignored.

: <tt>'''RSQ'''</tt> calculates the square of the Pearson correlation coefficient (which is conventionally given the letter r, hence "r squared"). See the <tt>* [[Documentation/How_Tos/Calc: PEARSON function|PEARSON]]</tt> function for more details.

: <tt>'''RSQ'''</tt> indicates how much of the variance in <tt>'''y'''</tt> is attributable to the variance in <tt>'''x'''</tt>.

: Advanced topic:
: The parameters <tt>'''x'''</tt> and <tt>'''y'''</tt> are always evaluated as [[Documentation/How_Tos/Using Arrays|array formulas]].

=== Example: ===
<tt>'''RSQ(A1:A30; B1:B30)'''</tt>
: returns the square of the Pearson correlation coefficient for the two sets of data in A1:A30 and B1:B30.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CORREL function|CORREL]]
* [[Documentation/How_Tos/Calc: PEARSON function|PEARSON]]
* [[Documentation/How_Tos/Using Arrays|Array formulas]]
* [[Documentation/How_Tos/Calc: Statistical functions|Statistical functions]]
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Statistical functions]]