Apache OpenOffice Wiki - User contributions [en]

Documentation/How Tos/Calc: PROPER function

2014-05-07T23:17:33Z

Drking: /* Example: */ examples to show apostrophe handling

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_REPLACE_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_MID_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__

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

=== Syntax: ===
<tt>'''PROPER(text)'''</tt>
: returns <tt>'''text'''</tt> with the first letter of each word capitalised, and other letters in lower case. More specifically, the first letter and any letter that follows a non-letter are capitalised.

=== Example: ===
<tt>'''PROPER("gooD morNINg")'''</tt>
: returns <tt>'''Good Morning'''</tt>.
<tt>'''PROPER("john o'connor")'''</tt>
: returns <tt>'''John O'Connor'''</tt>.
<tt>'''PROPER("don't worry")'''</tt>
: returns <tt>'''Don'T Worry'''</tt>. The '''T''' is capitalised as it follows a non-letter.
<tt>'''SUBSTITUTE(PROPER(SUBSTITUTE("don't worry";"'";"'x"));"'X";"'")'''</tt>
: returns <tt>'''Don't Worry'''</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: PROPER function

2014-05-07T23:05:14Z

Drking: /* Syntax: */ more detail

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_REPLACE_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_MID_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__

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

=== Syntax: ===
<tt>'''PROPER(text)'''</tt>
: returns <tt>'''text'''</tt> with the first letter of each word capitalised, and other letters in lower case. More specifically, the first letter and any letter that follows a non-letter are capitalised.

=== 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: PMT function

2010-12-03T15:54:14Z

Drking: /* Issues: */ Issue 91870 now resolved - changed text accordingly

__NOTOC__

== PMT ==
Returns the payment per period for a fixed rate loan.

=== Syntax: ===
<tt>'''PMT(rate; numperiods; principal; finalbalance; type)'''</tt>
: <tt>'''rate'''</tt>: the interest rate per period.
: <tt>'''numperiods'''</tt>: the total number of payment periods in the term.
: <tt>'''principal'''</tt>: the initial sum borrowed.
: <tt>'''finalbalance'''</tt>: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.
: <tt>'''type'''</tt>: when payments are made (optional - defaults to 0):
:: 0 - at the end of each period.
:: 1 - at the start of each period (including a payment at the start of the term).

: See the examples for how this function can be used for building up savings with fixed regular payments.

=== Example: ===
<tt>'''PMT(5.5%/12; 12*2; 5000; 0; 0)'''</tt>
: returns <tt>'''-220.48'''</tt> in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You pay 220.48 currency units each month; it is given as negative because you pay it.

<tt>'''PMT(5%/12; 12*2; 0; 1000; 1)'''</tt>
: returns <tt>'''-39.54'''</tt> in currency units. You wish to save 1000 currency units over 2 years, making monthly payments, beginning today. You assume the rate will remain the same at 5%. Interest is compounded monthly. If you save 39.54 currency units each month, the value at the end of 2 years will be 1000 currency units.

<tt>'''PMT(5.5%/12; 12*2; 5000; 1000; 0)'''</tt>
: returns <tt>'''-259.99'''</tt> in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You wish to build up a lump sum of 1000 currency units, to be paid to you at the end of the term. Interest is compounded monthly.

: This is a hypothetical example, which makes the unlikely assumption that the interest you are charged when in debt is the same as the interest that you get when in credit (when building up your 1000 lump sum). It illustrates the necessary sign of each parameter: 5000 is positive, because you are paid the loan sum; 1000 is positive, because you are paid the lump sum; 259.99 is negative, because you pay the monthly sum.

=== Issues: ===
* '''PMT''' formats the result as currency if the cell has default formatting. It thus displays a real currency amount. The amount returned by '''PMT''' may still be fractional - the display rounds this to the nearest real currency. Note that your loan provider might round in a different way (for example always downwards).
* Prior to OOo3.4, Calc could produce an error with tiny <tt>'''rate'''</tt> values. This had no impact in a real world calculation. Excel also had, and may still have, this problem. See [http://www.openoffice.org/issues/show_bug.cgi?id=91870 Issue 91870].

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CUMIPMT function|CUMIPMT]]
* [[Documentation/How_Tos/Calc: CUMIPMT_ADD function|CUMIPMT_ADD]]
* [[Documentation/How_Tos/Calc: IPMT function|IPMT]]
* [[Documentation/How_Tos/Calc: PPMT function|PPMT]]
* [[Documentation/How_Tos/Calc: CUMPRINC function|CUMPRINC]]
* [[Documentation/How_Tos/Calc: CUMPRINC_ADD function|CUMPRINC_ADD]]

* [[Documentation/How_Tos/Calc: Derivation of Financial Formulas|Derivation of Financial Formulas]]

* [[Documentation/How_Tos/Calc: Financial functions|Financial 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/Financial functions]]

Documentation/How Tos/Calc: N function

2010-08-09T14:33:21Z

Drking: /* Issues: */ revised the text handling issue

__NOTOC__

== N ==
Returns the numeric value.

=== Syntax: ===
<tt>'''N(value)'''</tt>

: returns the numeric value of <tt>'''value'''</tt> if possible. It returns the logical values <tt>'''TRUE'''</tt> and <tt>'''FALSE'''</tt> as <tt>'''1'''</tt> and <tt>'''0'''</tt> respectively.

: <tt>'''N()'''</tt> is not intended to convert text to a number - use the <tt>'''VALUE()'''</tt> function instead.

=== Example: ===
<tt>'''N(123)'''</tt>
: returns <tt>'''123'''</tt>, because <tt>'''123'''</tt> is (already) a number.

<tt>'''N(TRUE)'''</tt>
: returns <tt>'''1'''</tt>.

<tt>'''N(A3)'''</tt>
: where A3 contains <tt>'''FALSE'''</tt>, returns <tt>'''0'''</tt>.

===Issues: ===
<tt>'''N()'''</tt> in Calc will sometimes convert text to a number, but in Excel will always return text as <tt>'''0'''</tt>. In Calc <tt>'''N("4")'''</tt> returns <tt>'''0'''</tt> in OOo3 but <tt>'''4'''</tt> in OOo2. Issue [http://qa.openoffice.org/issues/show_bug.cgi?id=113739 113739] describes some further inconsistencies in the handling of text. In fact, the ODFF standard deliberately does not specify a behaviour. It may be better to use <tt>'''VALUE()'''</tt> to convert from text to number.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: ISNUMBER function|ISNUMBER]]
* [[Documentation/How_Tos/Calc: VALUE function|VALUE]]
* [[Documentation/How_Tos/Calc: T function|T]]
* [[Documentation/How_Tos/Calc: TEXT function|TEXT]]
* [[Documentation/How_Tos/Calc: FIXED function|FIXED]]
* [[Documentation/How_Tos/Calc: DECIMAL function|DECIMAL]]

* [[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/Information functions]]

Documentation/How Tos/Calc: ADDRESS function

2010-08-02T12:09:20Z

Drking: /* Issues: */ More on ODF formats

__NOTOC__

== ADDRESS ==
Returns a cell reference as text, given row and column numbers.

=== Syntax: ===
<tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt>

: <tt>'''row'''</tt> is a number specifying the row.

: <tt>'''column'''</tt> is a number (not a letter) specifying the column.

: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
<blockquote>
{| border="0" cellpadding="0" cellspacing="10" align="left"
|-valign="top" align="center"
|mode'''
|row'''
|column'''
|example'''

|-valign="top" align="center"
|<tt>'''1'''</tt>
|absolute
|absolute
|$A$1

|-valign="top" align="center"
|<tt>'''2'''</tt>
|absolute
|relative
|A$1

|-valign="top" align="center"
|<tt>'''3'''</tt>
|relative
|absolute
|$A1

|-valign="top" align="center"
|<tt>'''4'''</tt>
|relative
|relative
|A1
|}
 
</blockquote>

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation.

: <tt>'''sheet'''</tt> is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character.

=== Example: ===
<tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter.

<tt>'''ADDRESS(4; 3; 4)'''</tt>
: returns the text <tt>'''C4'''</tt>.

<tt>'''ADDRESS(4; 3)'''</tt>
: returns the text <tt>'''$C$4'''</tt>.

<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*When storing a document in the older ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. From OOo 3.0, Calc writes in ODF 1.2 draft format as standard, although this can be changed (Tools->Options->Load/Save->General). A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.

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

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: ADDRESS function

2010-08-02T12:04:27Z

Drking: /* Issues: */ ODFF issue (OFFICE-3261) resolved - removed from this help

__NOTOC__

== ADDRESS ==
Returns a cell reference as text, given row and column numbers.

=== Syntax: ===
<tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt>

: <tt>'''row'''</tt> is a number specifying the row.

: <tt>'''column'''</tt> is a number (not a letter) specifying the column.

: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
<blockquote>
{| border="0" cellpadding="0" cellspacing="10" align="left"
|-valign="top" align="center"
|mode'''
|row'''
|column'''
|example'''

|-valign="top" align="center"
|<tt>'''1'''</tt>
|absolute
|absolute
|$A$1

|-valign="top" align="center"
|<tt>'''2'''</tt>
|absolute
|relative
|A$1

|-valign="top" align="center"
|<tt>'''3'''</tt>
|relative
|absolute
|$A1

|-valign="top" align="center"
|<tt>'''4'''</tt>
|relative
|relative
|A1
|}
 
</blockquote>

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation.

: <tt>'''sheet'''</tt> is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character.

=== Example: ===
<tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter.

<tt>'''ADDRESS(4; 3; 4)'''</tt>
: returns the text <tt>'''C4'''</tt>.

<tt>'''ADDRESS(4; 3)'''</tt>
: returns the text <tt>'''$C$4'''</tt>.

<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*When storing a document in ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.

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

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: INDEX function

2010-08-01T18:58:15Z

Drking: /* Issues: */ rmoved all issues - resolved

__NOTOC__

== INDEX ==
Returns a value from a table, given a row and column number.

=== Syntax: ===
<tt>'''INDEX(datatable; row; column; areanumber)'''</tt>

: returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>.

: If <tt>'''datatable'''</tt> has multiple ranges then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>.

: If <tt>'''datatable'''</tt> has a single column, this syntax may be used:
<tt>'''INDEX(datatable; row)'''</tt>.

: If <tt>'''datatable'''</tt> has a single row, this syntax may be used:
<tt>'''INDEX(datatable; column)'''</tt>.

=== Example: ===
If cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''5'''</tt>, <tt>'''red'''</tt> and <tt>'''6'''</tt> and cells <tt>'''C1'''</tt>, <tt>'''C2'''</tt>, <tt>'''C3'''</tt> contain <tt>'''TRUE'''</tt>, <tt>'''3'''</tt> and <tt>'''blue'''</tt>:-

<tt>'''INDEX(B1:C3; 1; 1)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''INDEX(B1:C3; 3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

<tt>'''INDEX(B1:B3; 2)'''</tt>
: returns <tt>'''red'''</tt>.

<tt>'''INDEX(B1:C2; 1; 2)'''</tt>
: returns <tt>'''TRUE'''</tt>.

<tt>'''INDEX(B3:C3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
* [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet 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/Spreadsheet functions]]

Documentation/How Tos/Calc: INDEX function

2010-08-01T18:57:24Z

Drking: /* Example: */

__NOTOC__

== INDEX ==
Returns a value from a table, given a row and column number.

=== Syntax: ===
<tt>'''INDEX(datatable; row; column; areanumber)'''</tt>

: returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>.

: If <tt>'''datatable'''</tt> has multiple ranges then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>.

: If <tt>'''datatable'''</tt> has a single column, this syntax may be used:
<tt>'''INDEX(datatable; row)'''</tt>.

: If <tt>'''datatable'''</tt> has a single row, this syntax may be used:
<tt>'''INDEX(datatable; column)'''</tt>.

=== Example: ===
If cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''5'''</tt>, <tt>'''red'''</tt> and <tt>'''6'''</tt> and cells <tt>'''C1'''</tt>, <tt>'''C2'''</tt>, <tt>'''C3'''</tt> contain <tt>'''TRUE'''</tt>, <tt>'''3'''</tt> and <tt>'''blue'''</tt>:-

<tt>'''INDEX(B1:C3; 1; 1)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''INDEX(B1:C3; 3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

<tt>'''INDEX(B1:B3; 2)'''</tt>
: returns <tt>'''red'''</tt>.

<tt>'''INDEX(B1:C2; 1; 2)'''</tt>
: returns <tt>'''TRUE'''</tt>.

<tt>'''INDEX(B3:C3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

=== Issues: ===
*Calc cannot yet handle empty parameters, so for example <tt>'''INDEX(multirange; 3; ; 2)'''</tt> will fail.
*If <tt>'''datatable'''</tt> has a single row, the ODFF standard implies that the syntax <tt>'''INDEX(datatable; column)'''</tt> may be used. This is to be implemented in Calc (issue 78781)

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
* [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet 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/Spreadsheet functions]]

Documentation/How Tos/Calc: INDEX function

2010-08-01T18:55:05Z

Drking: /* Example: */ new example for single row range

__NOTOC__

== INDEX ==
Returns a value from a table, given a row and column number.

=== Syntax: ===
<tt>'''INDEX(datatable; row; column; areanumber)'''</tt>

: returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>.

: If <tt>'''datatable'''</tt> has multiple ranges then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>.

: If <tt>'''datatable'''</tt> has a single column, this syntax may be used:
<tt>'''INDEX(datatable; row)'''</tt>.

: If <tt>'''datatable'''</tt> has a single row, this syntax may be used:
<tt>'''INDEX(datatable; column)'''</tt>.

=== Example: ===
If cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''5'''</tt>, <tt>'''red'''</tt> and <tt>'''6'''</tt> and cells <tt>'''C1'''</tt>, <tt>'''C2'''</tt>, <tt>'''C3'''</tt> contain <tt>'''TRUE'''</tt>, <tt>'''3'''</tt> and <tt>'''blue'''</tt>:-

<tt>'''INDEX(B1:C3; 1; 1)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''INDEX(B1:C3; 3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

<tt>'''INDEX(B1:B3; 2)'''</tt>
: returns <tt>'''red'''</tt>.

<tt>'''INDEX(B1:C2 1; 2)'''</tt>
: returns <tt>'''TRUE'''</tt>.

<tt>'''INDEX(B4:C4;2)'''</tt>
: returns <tt>'''blue'''</tt>.

=== Issues: ===
*Calc cannot yet handle empty parameters, so for example <tt>'''INDEX(multirange; 3; ; 2)'''</tt> will fail.
*If <tt>'''datatable'''</tt> has a single row, the ODFF standard implies that the syntax <tt>'''INDEX(datatable; column)'''</tt> may be used. This is to be implemented in Calc (issue 78781)

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
* [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet 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/Spreadsheet functions]]

Documentation/How Tos/Calc: INDEX function

2010-08-01T18:52:08Z

Drking: /* Syntax: */ single row range

__NOTOC__

== INDEX ==
Returns a value from a table, given a row and column number.

=== Syntax: ===
<tt>'''INDEX(datatable; row; column; areanumber)'''</tt>

: returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>.

: If <tt>'''datatable'''</tt> has multiple ranges then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>.

: If <tt>'''datatable'''</tt> has a single column, this syntax may be used:
<tt>'''INDEX(datatable; row)'''</tt>.

: If <tt>'''datatable'''</tt> has a single row, this syntax may be used:
<tt>'''INDEX(datatable; column)'''</tt>.

=== Example: ===
If cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''5'''</tt>, <tt>'''red'''</tt> and <tt>'''6'''</tt> and cells <tt>'''C1'''</tt>, <tt>'''C2'''</tt>, <tt>'''C3'''</tt> contain <tt>'''TRUE'''</tt>, <tt>'''3'''</tt> and <tt>'''blue'''</tt>:-

<tt>'''INDEX(B1:C3; 1; 1)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''INDEX(B1:C3; 3; 2)'''</tt>
: returns <tt>'''blue'''</tt>.

<tt>'''INDEX(B1:B3; 2)'''</tt>
: returns <tt>'''red'''</tt>.

<tt>'''INDEX(B1:C2 1; 2)'''</tt>
: returns <tt>'''TRUE'''</tt>.

=== Issues: ===
*Calc cannot yet handle empty parameters, so for example <tt>'''INDEX(multirange; 3; ; 2)'''</tt> will fail.
*If <tt>'''datatable'''</tt> has a single row, the ODFF standard implies that the syntax <tt>'''INDEX(datatable; column)'''</tt> may be used. This is to be implemented in Calc (issue 78781)

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
* [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]]

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet 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/Spreadsheet functions]]

Documentation/How Tos/Calc: CONCATENATE function

2010-08-01T17:26:41Z

Drking: /* Example: */ '&' example, and clarified RAND example

__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.

: The ampersand operator <tt>'''&'''</tt> may also be used to concatenate text.

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

<tt>'''"al" & "tog" & "ether"'''</tt>
: also 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 the text string <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.
: Here Calc converts each number given by <tt>'''ROUND(RAND()*10))'''</tt> to text before concatenating.

<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: CONCATENATE function

2010-08-01T17:20:32Z

Drking: /* Syntax: */ & operator added

__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.

: The ampersand operator <tt>'''&'''</tt> may also be used to concatenate text.

=== 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: ADDRESS function

2010-08-01T14:43:09Z

Drking: /* Issues: */

__NOTOC__

== ADDRESS ==
Returns a cell reference as text, given row and column numbers.

=== Syntax: ===
<tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt>

: <tt>'''row'''</tt> is a number specifying the row.

: <tt>'''column'''</tt> is a number (not a letter) specifying the column.

: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
<blockquote>
{| border="0" cellpadding="0" cellspacing="10" align="left"
|-valign="top" align="center"
|mode'''
|row'''
|column'''
|example'''

|-valign="top" align="center"
|<tt>'''1'''</tt>
|absolute
|absolute
|$A$1

|-valign="top" align="center"
|<tt>'''2'''</tt>
|absolute
|relative
|A$1

|-valign="top" align="center"
|<tt>'''3'''</tt>
|relative
|absolute
|$A1

|-valign="top" align="center"
|<tt>'''4'''</tt>
|relative
|relative
|A1
|}
 
</blockquote>

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation.

: <tt>'''sheet'''</tt> is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character.

=== Example: ===
<tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter.

<tt>'''ADDRESS(4; 3; 4)'''</tt>
: returns the text <tt>'''C4'''</tt>.

<tt>'''ADDRESS(4; 3)'''</tt>
: returns the text <tt>'''$C$4'''</tt>.

<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*When storing a document in ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an <tt>'''ADDRESS'''</tt> function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.
*The draft ODFF standard says that the default <tt>'''mode'''</tt> (if <tt>'''mode'''</tt> is omitted) should be <tt>'''4'''</tt>. Calc and Excel both default to <tt>'''1'''</tt>, so this may be a problem with ODFF.

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

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: ADDRESS function

2010-08-01T14:40:53Z

Drking: New 'type' parameter; removal of pasted dev. message on this topic

__NOTOC__

== ADDRESS ==
Returns a cell reference as text, given row and column numbers.

=== Syntax: ===
<tt>'''ADDRESS(row; column; mode; type; sheet)'''</tt>

: <tt>'''row'''</tt> is a number specifying the row.

: <tt>'''column'''</tt> is a number (not a letter) specifying the column.

: <tt>'''mode'''</tt> (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be <tt>'''1'''</tt>.
<blockquote>
{| border="0" cellpadding="0" cellspacing="10" align="left"
|-valign="top" align="center"
|mode'''
|row'''
|column'''
|example'''

|-valign="top" align="center"
|<tt>'''1'''</tt>
|absolute
|absolute
|$A$1

|-valign="top" align="center"
|<tt>'''2'''</tt>
|absolute
|relative
|A$1

|-valign="top" align="center"
|<tt>'''3'''</tt>
|relative
|absolute
|$A1

|-valign="top" align="center"
|<tt>'''4'''</tt>
|relative
|relative
|A1
|}
 
</blockquote>

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''ADDRESS'''</tt> returns [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''ADDRESS'''</tt> returns the usual A1 notation.

: <tt>'''sheet'''</tt> is an optional text string specifying the sheet. In the usual A1 notation, Calc separates the sheet name with a <tt>'''.'''</tt> character; in the R1C1 notation, Calc separates the sheet name with a <tt>'''!'''</tt> character.

=== Example: ===
<tt>'''ADDRESS(4; 3; 2;; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2.C$4'''</tt>. Note the omitted <tt>'''type'''</tt> parameter.

<tt>'''ADDRESS(4; 3; 4)'''</tt>
: returns the text <tt>'''C4'''</tt>.

<tt>'''ADDRESS(4; 3)'''</tt>
: returns the text <tt>'''$C$4'''</tt>.

<tt>'''ADDRESS(4; 3; 2; 0; "Sheet2")'''</tt>
: returns the text <tt>'''Sheet2!R4C[3]'''</tt>.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''ADDRESS(1;1;4;;"Sheet2")'''</tt> returns <tt>'''Sheet2.A1'''</tt> in Calc; the equivalent in Excel returns <tt>'''Sheet2!A1'''</tt>.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*When storing a document in ODF 1.0/1.1 format, any <tt>'''type'''</tt> parameter in an ADDRESS function is stripped and not written; any subsequent read of that file may therefore produce incorrect results. A <tt>'''type'''</tt> value of <tt>'''1'''</tt> is allocated for any ODF 1.0/1.1 format document opened.
*The draft ODFF standard says that the default <tt>'''mode'''</tt> (if <tt>'''mode'''</tt> is omitted) should be <tt>'''4'''</tt>. Calc and Excel both default to <tt>'''1'''</tt>, so this may be a problem with ODFF.

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

* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: INDIRECT function

2010-08-01T14:09:01Z

Drking: /* Issues: */ Excel type is a logical value

__NOTOC__

== INDIRECT ==
Returns a reference, given a text string.

=== Syntax: ===
<tt>'''INDIRECT(textref; type)'''</tt>

: <tt>'''textref'''</tt> is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.

: The reference may then be used in formulas or functions requiring a reference - see the examples below.

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''textref'''</tt> is taken to be in [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''textref'''</tt> is taken to be in the usual A1 notation.

: A named reference is recognised with either <tt>'''type'''</tt>.

=== Example: ===
<tt>'''=INDIRECT("B2")'''</tt>
: (in a cell) returns the contents of cell B2. <tt>'''INDIRECT("B2")'''</tt> returns a reference to cell B2, so this is the same as <tt>'''=B2'''</tt>.

<tt>'''=INDIRECT("B" & "2")'''</tt>
: also returns the contents of cell B2. <tt>'''"B" & "2"'''</tt> becomes the text string <tt>'''"B2"'''</tt>.

<tt>'''=INDIRECT(D3)'''</tt>
: where cell D3 contains the text <tt>'''B2'''</tt> returns the contents of cell B2.

<tt>'''=SUM(INDIRECT("A1:C3"))'''</tt>
: returns the sum of the numbers in A1:C3.

<tt>'''=INDIRECT("B2"; 99)'''</tt>
: returns the contents of cell B2. The <tt>'''type'''</tt> is non-zero, so <tt>'''B2'''</tt> is evaluated as the usual A1 notation.

<tt>'''=INDIRECT("R2C3"; 0)'''</tt>
: returns the contents of cell C2. The <tt>'''type'''</tt> is zero, so <tt>'''R2C3'''</tt> is evaluated as R1C1 notation.

<tt>'''=INDIRECT("R[3]C[1]"; 0)'''</tt>
: entered in cell B1, returns the contents of cell C4. The <tt>'''type'''</tt> is zero, so <tt>'''R[3]C[1]'''</tt> is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''INDIRECT("Sheet2.A1")'''</tt> is valid in Calc, but in Excel the required form is <tt>'''INDIRECT("Sheet2!A1")'''</tt>. A portable solution might be <tt>'''INDIRECT(ADDRESS(1;1;4;"Sheet2"))'''</tt>. In the case of R1C1 notation only, Calc uses the <tt>'''!'''</tt> character to separate the sheet identifier.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*In Excel, <tt>'''type'''</tt> is a logical value; Calc also accepts logical values but they are displayed as numbers.
*Named cells/ranges are recognised in Calc from OOo2.4 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=4695 Issue 4695].

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

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

* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: R1C1 notation

2010-07-31T16:17:49Z

Drking: /* R1C1 notation */

__NOTOC__

== R1C1 notation ==

The usual way to refer to cells in Calc is by using a column letter and a row number:

:<tt>'''C5'''</tt> - the cell at the third column across and the fifth row down.

An older method in some other spreadsheets is R1C1 notation. This is only implemented in Calc in the [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]] and [[Documentation/How_Tos/Calc: ADDRESS function|ADDRESS]] functions, for compatability.

In R1C1 notation:

:<tt>'''R5C3'''</tt> refers to the cell at the fifth row, third column (<tt>'''C5'''</tt> in usual notation)

:<tt>'''R[2]C[4]'''</tt> is a relative reference, two rows down and four rows across from the current cell. If <tt>'''R[2]C[4]'''</tt> is entered in a formula in cell D3, it refers to cell H5.

:<tt>'''R[-1]C[4]'''</tt> is a relative reference, one row up and four rows across from the current cell.

:<tt>'''R'''</tt> is a reference to the current row.

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

* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: R1C1 notation

2010-07-31T16:16:42Z

Drking: added: see also

__NOTOC__

== R1C1 notation ==

The usual way to refer to cells in Calc is by using a column letter and a row number:

:<tt>'''C5'''</tt> - the cell at the third column across and the fifth row down.

An older method is R1C1 notation. This is only implemented in Calc in the [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]] and [[Documentation/How_Tos/Calc: ADDRESS function|ADDRESS]] functions, for compatability.

In R1C1 notation:

:<tt>'''R5C3'''</tt> refers to the cell at the fifth row, third column (<tt>'''C5'''</tt> in usual notation)

:<tt>'''R[2]C[4]'''</tt> is a relative reference, two rows down and four rows across from the current cell. If <tt>'''R[2]C[4]'''</tt> is entered in a formula in cell D3, it refers to cell H5.

:<tt>'''R[-1]C[4]'''</tt> is a relative reference, one row up and four rows across from the current cell.

:<tt>'''R'''</tt> is a reference to the current row.

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

* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: R1C1 notation

2010-07-31T16:06:03Z

Drking: /* R1C1 notation */

__NOTOC__

== R1C1 notation ==

The usual way to refer to cells in Calc is by using a column letter and a row number:

:<tt>'''C5'''</tt> - the cell at the third column across and the fifth row down.

An older method is R1C1 notation. This is only implemented in Calc in the [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]] and [[Documentation/How_Tos/Calc: ADDRESS function|ADDRESS]] functions, for compatability.

In R1C1 notation:

:<tt>'''R5C3'''</tt> refers to the cell at the fifth row, third column (<tt>'''C5'''</tt> in usual notation)

:<tt>'''R[2]C[4]'''</tt> is a relative reference, two rows down and four rows across from the current cell. If <tt>'''R[2]C[4]'''</tt> is entered in a formula in cell D3, it refers to cell H5.

:<tt>'''R[-1]C[4]'''</tt> is a relative reference, one row up and four rows across from the current cell.

:<tt>'''R'''</tt> is a reference to the current row.

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

Documentation/How Tos/Calc: R1C1 notation

2010-07-31T16:05:04Z

Drking: initial content

__NOTOC__

== R1C1 notation ==

The usual way to refer to cells in Calc is by using a column letter and a row number:

:<tt>'''C5'''</tt> - the cell at the third column across and the fifth row down.

An older method is R1C1 notation. This is only implemented in Calc in the [[Documentation/How_Tos/Calc: INDIRECT function|INDIRECT]] and [[Documentation/How_Tos/Calc: ADDRESS function|ADDRESS]] functions, for compatability.

In R1C1 notation:

:<tt>'''R5C3'''</tt> refers to the cell at the fifth row, third column (<tt>'''C5'''</tt> in usual notation)

:<tt>'''R[2]C[4]'''</tt> is a relative reference, two rows down and 4 rows across from the current cell. If <tt>'''R[2]C[4]'''</tt> is entered in a formula in cell D3, it refers to cell H5.

:<tt>'''R[-1]C[4]'''</tt> is a relative reference, one row up and 4 rows across from the current cell.

:<tt>'''R'''</tt> is a reference to the current row.

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

Documentation/How Tos/Calc: INDIRECT function

2010-07-31T15:45:59Z

Drking: updated to include the type parameter; removed long copy/pasted email from developer about this

__NOTOC__

== INDIRECT ==
Returns a reference, given a text string.

=== Syntax: ===
<tt>'''INDIRECT(textref; type)'''</tt>

: <tt>'''textref'''</tt> is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.

: The reference may then be used in formulas or functions requiring a reference - see the examples below.

: <tt>'''type'''</tt> is an optional number; if <tt>'''0'''</tt>, <tt>'''textref'''</tt> is taken to be in [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]], and otherwise (or if omitted) <tt>'''textref'''</tt> is taken to be in the usual A1 notation.

: A named reference is recognised with either <tt>'''type'''</tt>.

=== Example: ===
<tt>'''=INDIRECT("B2")'''</tt>
: (in a cell) returns the contents of cell B2. <tt>'''INDIRECT("B2")'''</tt> returns a reference to cell B2, so this is the same as <tt>'''=B2'''</tt>.

<tt>'''=INDIRECT("B" & "2")'''</tt>
: also returns the contents of cell B2. <tt>'''"B" & "2"'''</tt> becomes the text string <tt>'''"B2"'''</tt>.

<tt>'''=INDIRECT(D3)'''</tt>
: where cell D3 contains the text <tt>'''B2'''</tt> returns the contents of cell B2.

<tt>'''=SUM(INDIRECT("A1:C3"))'''</tt>
: returns the sum of the numbers in A1:C3.

<tt>'''=INDIRECT("B2"; 99)'''</tt>
: returns the contents of cell B2. The <tt>'''type'''</tt> is non-zero, so <tt>'''B2'''</tt> is evaluated as the usual A1 notation.

<tt>'''=INDIRECT("R2C3"; 0)'''</tt>
: returns the contents of cell C2. The <tt>'''type'''</tt> is zero, so <tt>'''R2C3'''</tt> is evaluated as R1C1 notation.

<tt>'''=INDIRECT("R[3]C[1]"; 0)'''</tt>
: entered in cell B1, returns the contents of cell C4. The <tt>'''type'''</tt> is zero, so <tt>'''R[3]C[1]'''</tt> is evaluated as R1C1 notation, in this case relative to cell B1: 3 rows down and 1 column across to yield a reference to cell C4.

=== Issues: ===
*Excel represents some cell references differently to Calc, so this function is not always portable. For example <tt>'''INDIRECT("Sheet2.A1")'''</tt> is valid in Calc, but in Excel the required form is <tt>'''INDIRECT("Sheet2!A1")'''</tt>. A portable solution might be <tt>'''INDIRECT(ADDRESS(1;1;4;"Sheet2"))'''</tt>. In the case of R1C1 notation only, Calc uses the <tt>'''!'''</tt> character to separate the sheet identifier.
*The <tt>'''type'''</tt> parameter is implemented from OOo3.0 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=91020 Issue 91020].
*Named cells/ranges are recognised in Calc from OOo2.4 - see [http://qa.openoffice.org/issues/show_bug.cgi?id=4695 Issue 4695].

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

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

* [[Documentation/How_Tos/Calc: R1C1 notation|R1C1 notation]]
* [[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/Spreadsheet functions]]

Documentation/How Tos/Calc: CORREL function

2010-07-30T07:10:26Z

Drking: /* Syntax: */

__NOTOC__

== CORREL ==
Returns the Pearson correlation coefficient of two sets of data.

=== Syntax: ===
<tt>'''CORREL(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>'''CORREL'''</tt> calculates:
::[[Image:Calc_pearson_equation.png]]
:: where [[Image:Calc_xymean.png]] are the averages of x,y.

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

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

=== Issues: ===
* <tt>'''CORREL'''</tt> is identical to <tt>'''PEARSON'''</tt>.

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

* [[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: PEARSON function

2010-07-30T07:09:50Z

Drking: /* Syntax: */

__NOTOC__

== PEARSON ==
Returns the Pearson correlation coefficient of two sets of data.

=== Syntax: ===
<tt>'''PEARSON(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>'''PEARSON'''</tt> calculates:
::[[Image:Calc_pearson_equation.png]]
:: where [[Image:Calc_xymean.png]] are the averages of x,y.

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

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

=== Issues: ===
* <tt>'''PEARSON'''</tt> is identical to <tt>'''CORREL'''</tt>.

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

* [[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: MODE function

2010-07-30T06:57:49Z

Drking: moved the issue (now closed) to an example

__NOTOC__

== MODE ==
Returns the most common value in a set of numbers.

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

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

: <tt>'''MODE'''</tt> returns the number that occurs most often, or if more than one number occurs that many times, the smallest such number. If no number occurs more than once, <tt>'''MODE'''</tt> returns an error.

: Advanced topic: <tt>'''MODE'''</tt> evaluates its parameters as [[Documentation/How_Tos/Using_Arrays|array formulas]] - see the examples.

=== Example: ===
<tt>'''MODE(A1:A4)'''</tt>
: where cells <tt>'''A1:A4'''</tt> contain <tt>'''1'''</tt>, <tt>'''3'''</tt>, <tt>'''2'''</tt>, <tt>'''3'''</tt> returns <tt>'''3'''</tt>, the number occurring most often.

<tt>'''MODE(3; 7; 3; 9; 9; 3; 5; 1; 9; 5)'''</tt>
: returns <tt>'''3'''</tt>. The number <tt>'''9'''</tt> occurs the same number of times, but <tt>'''3'''</tt> is smaller.

<tt>'''( -MODE(-A1:A10)=MODE(A1:A10) )'''</tt>
: returns <tt>'''TRUE'''</tt> if there is just one mode for the data in A1:A10, and <tt>'''FALSE'''</tt> otherwise. Notice the two minus signs.

Advanced topic: Evaluation as an [[Documentation/How_Tos/Using_Arrays|array formula]]:

<tt>'''MODE(ABS({1; 1; 1; 2; 2; -2; -2}))'''</tt>
: returns <tt>'''2'''</tt>. The <tt>'''ABS'''</tt> function normally takes a single number, but here it is given an array. <tt>'''MODE'''</tt> forces the formula to be an array formula, so that the absolute value of each number in the array is found, and given to <tt>'''MODE'''</tt> as the array {1; 1; 1; 2; 2; 2; 2}. The most common value is now <tt>'''2'''</tt>.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: AVERAGE function|AVERAGE]]
* [[Documentation/How_Tos/Calc: AVERAGEA function|AVERAGEA]]
* [[Documentation/How_Tos/Calc: MEDIAN function|MEDIAN]]

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

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

* [[Documentation/How_Tos/Using_Arrays|Using Arrays]]

* [[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/Using Arrays

2010-07-30T06:33:53Z

Drking: /* Issues */ different dimension arrays - example; re-ordered

== Introduction ==

Arrays may be helpful in reducing the number of cells you use, but they can be a little complex to understand. In some ways, they extend what a spreadsheet can do beyond what spreadsheets were intended for. Almost all tasks are possible without them.

An array is simply a rectangular block of information that Calc can manipulate in a formula - that is, it is information organised in rows and columns. An array may be cells on a spreadsheet, or may be held internally by Calc.

There are 2 ways to specify an array in a formula:
*as a range - for example <tt>'''A2:C3'''</tt>.
[[Image:Calc_array4.png|right]]
*as an "inline array", for example <tt>'''{1; 5; 3 | 6; 2; 4}'''</tt> (these are fully functional from OOo2.4, but do exist in earlier versions - see [[Documentation/How_Tos/Using Arrays#Issues|Array Issues]]). You type curly braces <tt>'''{ }'''</tt> around an inline array. Entries on a row are separated by a semicolon ‘<tt>''';'''</tt>’, and rows are separated by the pipe character ‘<tt>'''|'''</tt>’. Each row must have the same number of elements (it is wrong to write <tt>'''{1; 2; 3 | 4; 5}'''</tt> because there are 3 elements in the top row and only 2 in the next row). Inline arrays may have mixed contents, for example <tt>'''{4; 2; "dog" | -22; "cat"; 0}'''</tt>. However an inline array may not contain references (eg <tt>'''A4'''</tt>), or formulae (eg <tt>'''PI()'''</tt>, <tt>'''2*3'''</tt> ), or percentages (eg <tt>'''5%'''</tt>).
 

[[Image:Calc_array5.png|right]]
You can give a name to a range of cells: select the range and choose '''Insert - Names - Define'''.

You can give a name to an inline array: choose '''Insert - Names - Define'''; type the array (eg <tt>'''{1; 3; 2}'''</tt> including the curly braces) in the '''Assigned to''' box.

 

== Functions which understand array parameters ==

Some functions, such as <tt>'''SUM()'''</tt>, <tt>'''AVERAGE()'''</tt>, <tt>'''MATCH()'''</tt>, <tt>'''LOOKUP()'''</tt>, accept one or more of their parameters as arrays.

''For example:''

:<tt>'''SUM( A2:C3 )'''</tt> returns the sum of the numbers in the range <tt>'''A2:C3'''</tt>.

:<tt>'''SUM( {3; 2; 4} )'''</tt> returns <tt>'''9'''</tt>, the sum of the numbers in the inline array <tt>'''{3; 2; 4}'''</tt>.

:<tt>'''SUM'''</tt> expects/understands single (‘scalar’) values too - <tt>'''SUM( B5; 7 )'''</tt> returns the sum of <tt>'''B5'''</tt> and <tt>'''7'''</tt>.

== Functions not expecting array parameters ==

Some functions, such as <tt>'''ABS()'''</tt>, <tt>'''SQRT()'''</tt>, <tt>'''COS()'''</tt>, <tt>'''LEN()'''</tt> expect their parameters to be ‘scalar’ - that is, a single value such as <tt>'''354'''</tt> or <tt>'''"dog"'''</tt> or the contents of a cell eg <tt>'''B5'''</tt>.

''Example:''

:<tt>'''SQRT(4)'''</tt> returns <tt>'''2'''</tt>.

:<tt>'''LEN("dog")'''</tt> returns <tt>'''3'''</tt>.

However, you may still use an array where a single value is expected - ''for example'' <tt>'''SQRT( {9; 4} )'''</tt>. If you enter the formula ‘normally’ by pressing '''Enter''', Calc will then evaluate the formula using a single value from the array as follows:

If it is an '''inline array''':

:Calc will use the first value (the ‘top left’).

:''Example:''

::<tt>'''=SQRT( {9; 4 | 25; 16} )'''</tt> then press '''Enter''' returns <tt>'''3'''</tt>, the square root of the first element in the array (<tt>'''9'''</tt>).

If it is a '''range''':

:1. Calc will return an error unless the array is a single row or a single column.

:2. For a single row or a single column range, Calc will use the value where the formula cell’s column/row intersects with the array (or return an error if there is no intersection).

:''Examples:''

<center>[[Image:Calc_array1.png]]</center>
:: The formula <tt>'''=ABS(B2:B5)'''</tt> is entered 'normally' in cell D3, that is on row 3. Row 3 intersects <tt>'''B2:B5'''</tt> at cell B3, thus the formula evaluated is <tt>'''=ABS(B3)'''</tt>.

<center>[[Image:Calc_array2.png]]</center>
:: The formula <tt>'''=LEN(B5:D5)'''</tt> is entered 'normally' in cell B1, that is in column B. Column B intersects <tt>'''B5:D5'''</tt> at cell B5, thus the formula evaluated is <tt>'''=LEN(B5)'''</tt>.

== Array formulas ==
The real power of arrays comes when you enter a formula in a special way, as an ‘array formula’. You do this by pressing '''Cntrl-Shift-Enter''' instead of the '''Enter''' button (or by ticking the ''Array'' checkbox if you are using the Formula Wizard).

If in cell B1 you enter <tt>'''={3; 4}'''</tt> ‘normally’ by pressing '''Enter''', the first value <tt>'''3'''</tt> is displayed in the cell.

If in cell B2 you type <tt>'''={3; 4}'''</tt> but press '''Cntrl-Shift-Enter''' instead of '''Enter''', the cell becomes an 'array formula'. The formula now returns the entire array <tt>'''{3; 4}'''</tt>. Cell B2 displays <tt>'''3'''</tt> and cell C2 displays <tt>'''4'''</tt>.

:''Note that if you entered the formula using the '''Enter''' key, simply selecting the cell and pressing '''Cntrl-Shift-Enter''' will not convert the cell to an array expression - you must make an actual edit (such as adding then deleting a character), or click the Function Wizard and tick the ''Array'' checkbox.''

If you now try to edit cell B2, you are told that ''"you cannot change only part of an array"''. To edit an array you must select the entire array, either with the mouse or by typing Cntrl-/ (hold the Cntrl key and press the slash key ‘/’).
[[Image:Calc_array6.png|right]]

The formula bar indicates that this is an array formula by enclosing it in curly braces {}. You do not need to type these - they will disappear while you edit the formula, and Calc will show them again when you have finished editing.
 

== Array formula calculations ==
When Calc evaluates an array formula, it treats ‘unexpected arrays’ as a series of values (rather than using a single value), calculating a result for each of the array elements, and returning an array of results.

''Example:''
[[Image:Calc_array3.png|right]]
:<tt>'''=SQRT( {16; 4; 25} )'''</tt> when entered by pressing '''Cntrl-Shift-Enter''' instead of '''Enter''' returns an array of results, with 1 row and 3 columns - <tt>'''{4; 2; 5}'''</tt>. If the formula is in cell B2, Calc places the results in cells B2:D2. <tt>'''4'''</tt> is placed in B2, <tt>'''2'''</tt> in C2 and <tt>'''5'''</tt> in D2.
 

The process in effect works thus:

#All ‘unexpected arrays’ in the same array calculation should be the same size (see this issue: Issue 46681).
#The result will be returned in an array of that size.
#The calculation is done for each element in turn, with the result returned in the corresponding element of the output array.

''Example:''

With the array formula <tt>'''=SQRT( {16; 4; 25} )'''</tt>:

#There is only one array, with 1 row and 3 columns.
#The result will be returned in an array with 1 row and 3 columns.
#The calculation is done for <tt>'''16'''</tt> first, then for <tt>'''4'''</tt>, then for <tt>'''25'''</tt>, giving the array result <tt>'''{4; 2; 5}'''</tt>.

''Example:''

With the array formula <tt>'''=SQRT( {8 | 18} * 2 )'''</tt> in cell A5:

#There is only one array, with 2 rows and 1 column.
#The result will be returned in an array with 2 rows and 1 column.
#The calculation is: first element <tt>'''SQRT(8*2) = 4'''</tt>; second element <tt>'''SQRT(18*2) = 6'''</tt>; the array result is thus <tt>'''{4 | 6}'''</tt> - that is, <tt>'''4'''</tt> in cell A5 and <tt>'''6'''</tt> in cell A6.

The result of an array expression is an array, which can be used within the formula.

''Example:''

:<tt>'''=SUM(SQRT( {16; 4; 25} ))'''</tt> as an array formula. The calculation of <tt>'''SQRT( {16; 4; 25} )'''</tt>, as before, yields a result of <tt>'''{4; 2; 5}'''</tt>, thus giving <tt>'''SUM( {4; 2; 5} )'''</tt>, returning a final result in the cell of 4+2+5 = <tt>'''11'''</tt>.

''Example:''

With the array formula <tt>'''=SUM(IF(A1:A4>0; B1:B4; 0))'''</tt>

#The two arrays <tt>'''A1:A4'''</tt> and <tt>'''B1:B4'''</tt> both have 4 rows and 1 column.
#The result of the <tt>'''IF()'''</tt> array calculation will be an array of that size, which <tt>'''SUM()'''</tt> will add up.
#If A1>0 the first element is <tt>'''B1'''</tt>; else <tt>'''0'''</tt>. If A2>0 the second element is <tt>'''B2'''</tt>; else <tt>'''0'''</tt> .... The array presented to <tt>'''SUM()'''</tt> has the values in B1:B4 where the adjacent value in A1:A4 is >0. The final output is the sum of the values in B1:B4 for which the adjacent value in A1:A4 is >0.

== Array functions ==
Some functions calculate their result as an array. If they are to return that array, they must be entered as an array formula, by pressing '''Cntrl-Shift-Enter''' (or by ticking the ''Array'' checkbox if you are using the Formula Wizard). If, instead, they are entered 'normally' with the '''Enter''' button, just the (single) top left element of the calculated array will be returned. These funtions are listed in the '''[[Documentation/How_Tos/Calc: Array functions|Array function category]]''', and are:
:[[Documentation/How_Tos/Calc: MINVERSE function|MINVERSE]], [[Documentation/How_Tos/Calc: MMULT function|MMULT]], [[Documentation/How_Tos/Calc: MUNIT function|MUNIT]], [[Documentation/How_Tos/Calc: TRANSPOSE function|TRANSPOSE]], [[Documentation/How_Tos/Calc: FREQUENCY function|FREQUENCY]], [[Documentation/How_Tos/Calc: GROWTH function|GROWTH]], [[Documentation/How_Tos/Calc: LINEST function|LINEST]], [[Documentation/How_Tos/Calc: LOGEST function|LOGEST]], [[Documentation/How_Tos/Calc: TREND function|TREND]]

''For example'': =[[Documentation/How_Tos/Calc: MUNIT function|MUNIT(2)]] entered as an array formula in cell B1 returns the 2 x 2 unit (identity) matrix as the array {1; 0 | 0; 1} - that is, cells B1, B2, C1, C2 show 1, 0, 0, 1. If this formula is entered 'normally', just the 'top left' value (1) is returned in cell B1.

Some functions (including some listed above) take parameters that are forced to evaluate as an array formula, even when the formula is entered 'normally':

:[[Documentation/How_Tos/Calc: MDETERM function|MDETERM]], [[Documentation/How_Tos/Calc: MINVERSE function|MINVERSE]], [[Documentation/How_Tos/Calc: MMULT function|MMULT]], [[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]], [[Documentation/How_Tos/Calc: SUMX2MY2 function|SUMX2MY2]], [[Documentation/How_Tos/Calc: SUMX2PY2 function|SUMX2PY2]], [[Documentation/How_Tos/Calc: SUMXMY2 function|SUMXMY2]], [[Documentation/How_Tos/Calc: CORREL function|CORREL]], [[Documentation/How_Tos/Calc: COVAR function|COVAR]], [[Documentation/How_Tos/Calc: FORECAST function|FORECAST]], [[Documentation/How_Tos/Calc: FTEST function|FTEST]], [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]], [[Documentation/How_Tos/Calc: MODE function|MODE]], [[Documentation/How_Tos/Calc: PEARSON function|PEARSON]], [[Documentation/How_Tos/Calc: PROB function|PROB]], [[Documentation/How_Tos/Calc: RSQ function|RSQ]], [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]], [[Documentation/How_Tos/Calc: STEYX function|STEYX]], [[Documentation/How_Tos/Calc: TTEST function|TTEST]]

''For example'': [[Documentation/How_Tos/Calc: MODE function|MODE(ABS(A1:A3))]] (in normal mode) forces ABS(A1:A3) to be evaluated as an array formula, returning an array of the absolute values of A1:A3 from which MODE selects the most common value to be returned.

''For example'': [[Documentation/How_Tos/Calc: PROB function|PROB(A1:A5; B1:B5/100; 3)]] (in normal mode) forces B1:B5/100 to be evaluated as an array formula, returning an array where each value is one hundredth of its value in B1:B5 (perhaps because the probabilities were expressed in percent).

A few of these functions have some (array) parameters that are forced to evaluate as an array formula, and other (single value) parameters that evaluate normally.

''For example'': [[Documentation/How_Tos/Calc: PROB function|PROB(A1:A5; B1:B5; {3; 4})]] (in normal mode) does not evaulate {3; 4} as an array, because this would mean PROB returning an array. It evaluates PROB(A1:A5; B1:B5; 3) - that is, it interprets {3; 4} in scalar mode, taking the 'top left' value 3. However, if entered as an array formula it will of course return an array.

== Tips and Tricks ==
All these examples are to be entered as an array expression, by pressing '''Cntrl-Shift-Enter'''.

====Sum of entries matching multiple conditions====
<tt>'''SUM( (A1:A6="red")*(B1:B6="big")*C1:C6 )'''</tt> returns the sum of entries in C1:C6 whose A column entries are <tt>'''"red"'''</tt> AND whose B column entries are <tt>'''"big"'''</tt>. A1:A6 and B1:B6 each produce a 6 element array of <tt>'''TRUE'''</tt> or <tt>'''FALSE'''</tt> - which in number calculations are <tt>'''1'''</tt> or <tt>'''0'''</tt>. Thus if A2 contains <tt>'''"red"'''</tt> and B2 contains <tt>'''"big"'''</tt> the second element of the array is <tt>'''1 * 1 * C2 = C2'''</tt>. If A2 contains <tt>'''"blue"'''</tt> instead, the second element of the array is <tt>'''0 * 1 * C2 = 0'''</tt>.

<tt>'''SUM( ((A1:A6="red")+(B1:B6="big")>0)*C1:C6 )'''</tt> returns the sum of entries in C1:C6 whose A column entries are <tt>'''"red"'''</tt> OR whose B column entries are <tt>'''"big"'''</tt>.

<tt>'''SUM( MOD((A1:A6="red")+(B1:B6="big");2)*C1:C6 )'''</tt> returns the sum of entries in C1:C6 either whose A column entries are <tt>'''"red"'''</tt> OR whose B column entries are <tt>'''"big"'''</tt> but not both (exclusive OR)

<tt>'''SUM( NOT((A1:A6="red")+(B1:B6="big"))*C1:C6 )'''</tt> returns the sum of entries in C1:C6 where neither the A column entry is <tt>'''"red"'''</tt> NOR the B column entry is <tt>'''"big"'''</tt>.

====Count of entries matching multiple conditions====
<tt>'''SUM( (A1:A6="red")*(B1:B6="big") )'''</tt> returns the number of rows whose A column entries are <tt>'''"red"'''</tt> AND whose B column entries are <tt>'''"big"'''</tt>.

====Maximum in a particular month====
<tt>'''MAX(IF(MONTH(B1:B9)=5; C1:C9; 0))'''</tt> returns the maximum value in C1:C9 where the corresponding B1:B9 date is in May (month <tt>'''5'''</tt>). The <tt>'''MIN()'''</tt> function would provide the minimum in the month.

====Average of entries meeting a condition====
<tt>'''AVERAGE(IF(A1:A9="red"; B1:B9; ""))'''</tt> returns the average of entries in B1:B9 whose A column entries are <tt>'''“red”'''</tt>. The <tt>'''AVERAGE'''</tt> function ignores any blank entries.

The <tt>'''MEDIAN'''</tt> function can be used similarly.

====Dynamic sorting of a column====
<tt>'''=LARGE(B3:B9;ROW(B3:B9)+1-ROW(B3))'''</tt> entered in cell C3 returns an array in C3:C9 which is B3:B9 in descending order. <tt>'''+1-ROW(3)'''</tt> is a constant - you could write <tt>'''=LARGE(B3:B9;ROW(B3:B9)-2)'''</tt> instead.

<tt>'''=SMALL(B3:B9;ROW(B3:B9)-2)'''</tt> returns B3:B9 in ascending order.

====Sum ignoring errors====
<tt>'''SUM( IF(ISERROR(A1:A9); 0; A1:A9) )'''</tt>. Normally <tt>'''SUM'''</tt> will propagate any error found.

====Average, ignoring zero entries====
<tt>'''AVERAGE(IF(B1:B9<>0; B1:B9; ""))'''</tt>

====Test a cell for one of a set of values====
<tt>'''OR(B2={2; 5; 6})'''</tt> as an array expression, or <tt>'''OR(B2=2; B2=5; B2=6)'''</tt> as a ‘normal’ formula.

====Sum of the smallest 4 numbers====
<tt>'''SUM(SMALL(B3:B9; {1;2;3;4}))'''</tt> or
<tt>'''SUM(SMALL(B3:B9;ROW(A1:A4)))'''</tt>. <tt>'''ROW(A1:A4)'''</tt> produces <tt>'''{1;2;3;4}'''</tt>.

====Last used cell in a column====
<tt>'''MAX(ROW(B1:B9)*(B1:B9<>""))'''</tt> returns the row number of the last cell used.

== Issues ==
* Some functions cannot yet be used in array formulas: <tt>'''COUNTIF'''</tt>, <tt>'''SUMIF'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=65866 issue 65866]), and <tt>'''ISLOGICAL'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=87219 issue 87219]). <tt>'''MATCH'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=8947 issue 8947]) works from OOo3.0.
* <tt>'''OFFSET'''</tt> has limited behaviour within an array formula.
* Names defined by '''Insert - Names - Define''' can be used within an array formula, but labels (either '''Insert - Names - Labels''' or automatically recognised at the head of a column) should not be used ([http://qa.openoffice.org/issues/show_bug.cgi?id=87268 issue 87268]).
* In Excel, not all functions can be used in array formulas. For example <tt>'''CONCATENATE'''</tt> works in Calc but not in Excel. Be aware of Excel's limitations if you plan to use a Calc spreadsheet in Excel.
* Volatile functions such as <tt>'''RAND'''</tt>, <tt>'''RANDBETWEEN'''</tt>, <tt>'''NOW'''</tt> may not be recalculated in array formulas; for example in <tt>'''{=A1:A3+RAND()}'''</tt> just one random number is generated ([http://qa.openoffice.org/issues/show_bug.cgi?id=67135 issue 67135]).
* Before OOo2.4 spaces and negative numbers in inline arrays failed ([http://qa.openoffice.org/issues/show_bug.cgi?id=82644 issue 82644]).
* Before OOo3.1 it was not possible to include different dimension arrays in a formula ([http://qa.openoffice.org/issues/show_bug.cgi?id=46681 issue 46681]). Now <tt>'''{ {1|2} + {10;20|30;40} }'''</tt> internally expands <tt>'''{1|2}'''</tt> to <tt>'''{1;1|2;2}'''</tt>, to correctly return the result <tt>'''{11;21|32;42}'''</tt>.
* Array formulas may slow down your computer if you have large arrays.

{{PDL1}}

[[Category:Documentation/How Tos/Calc]]
[[Category:Calc]]

Documentation/How Tos/Using Arrays

2010-07-28T16:34:44Z

Drking: /* Issues */ updated after bug fixes

== Introduction ==

Arrays may be helpful in reducing the number of cells you use, but they can be a little complex to understand. In some ways, they extend what a spreadsheet can do beyond what spreadsheets were intended for. Almost all tasks are possible without them.

An array is simply a rectangular block of information that Calc can manipulate in a formula - that is, it is information organised in rows and columns. An array may be cells on a spreadsheet, or may be held internally by Calc.

There are 2 ways to specify an array in a formula:
*as a range - for example <tt>'''A2:C3'''</tt>.
[[Image:Calc_array4.png|right]]
*as an "inline array", for example <tt>'''{1; 5; 3 | 6; 2; 4}'''</tt> (these are fully functional from OOo2.4, but do exist in earlier versions - see [[Documentation/How_Tos/Using Arrays#Issues|Array Issues]]). You type curly braces <tt>'''{ }'''</tt> around an inline array. Entries on a row are separated by a semicolon ‘<tt>''';'''</tt>’, and rows are separated by the pipe character ‘<tt>'''|'''</tt>’. Each row must have the same number of elements (it is wrong to write <tt>'''{1; 2; 3 | 4; 5}'''</tt> because there are 3 elements in the top row and only 2 in the next row). Inline arrays may have mixed contents, for example <tt>'''{4; 2; "dog" | -22; "cat"; 0}'''</tt>. However an inline array may not contain references (eg <tt>'''A4'''</tt>), or formulae (eg <tt>'''PI()'''</tt>, <tt>'''2*3'''</tt> ), or percentages (eg <tt>'''5%'''</tt>).
 

[[Image:Calc_array5.png|right]]
You can give a name to a range of cells: select the range and choose '''Insert - Names - Define'''.

You can give a name to an inline array: choose '''Insert - Names - Define'''; type the array (eg <tt>'''{1; 3; 2}'''</tt> including the curly braces) in the '''Assigned to''' box.

 

== Functions which understand array parameters ==

Some functions, such as <tt>'''SUM()'''</tt>, <tt>'''AVERAGE()'''</tt>, <tt>'''MATCH()'''</tt>, <tt>'''LOOKUP()'''</tt>, accept one or more of their parameters as arrays.

''For example:''

:<tt>'''SUM( A2:C3 )'''</tt> returns the sum of the numbers in the range <tt>'''A2:C3'''</tt>.

:<tt>'''SUM( {3; 2; 4} )'''</tt> returns <tt>'''9'''</tt>, the sum of the numbers in the inline array <tt>'''{3; 2; 4}'''</tt>.

:<tt>'''SUM'''</tt> expects/understands single (‘scalar’) values too - <tt>'''SUM( B5; 7 )'''</tt> returns the sum of <tt>'''B5'''</tt> and <tt>'''7'''</tt>.

== Functions not expecting array parameters ==

Some functions, such as <tt>'''ABS()'''</tt>, <tt>'''SQRT()'''</tt>, <tt>'''COS()'''</tt>, <tt>'''LEN()'''</tt> expect their parameters to be ‘scalar’ - that is, a single value such as <tt>'''354'''</tt> or <tt>'''"dog"'''</tt> or the contents of a cell eg <tt>'''B5'''</tt>.

''Example:''

:<tt>'''SQRT(4)'''</tt> returns <tt>'''2'''</tt>.

:<tt>'''LEN("dog")'''</tt> returns <tt>'''3'''</tt>.

However, you may still use an array where a single value is expected - ''for example'' <tt>'''SQRT( {9; 4} )'''</tt>. If you enter the formula ‘normally’ by pressing '''Enter''', Calc will then evaluate the formula using a single value from the array as follows:

If it is an '''inline array''':

:Calc will use the first value (the ‘top left’).

:''Example:''

::<tt>'''=SQRT( {9; 4 | 25; 16} )'''</tt> then press '''Enter''' returns <tt>'''3'''</tt>, the square root of the first element in the array (<tt>'''9'''</tt>).

If it is a '''range''':

:1. Calc will return an error unless the array is a single row or a single column.

:2. For a single row or a single column range, Calc will use the value where the formula cell’s column/row intersects with the array (or return an error if there is no intersection).

:''Examples:''

<center>[[Image:Calc_array1.png]]</center>
:: The formula <tt>'''=ABS(B2:B5)'''</tt> is entered 'normally' in cell D3, that is on row 3. Row 3 intersects <tt>'''B2:B5'''</tt> at cell B3, thus the formula evaluated is <tt>'''=ABS(B3)'''</tt>.

<center>[[Image:Calc_array2.png]]</center>
:: The formula <tt>'''=LEN(B5:D5)'''</tt> is entered 'normally' in cell B1, that is in column B. Column B intersects <tt>'''B5:D5'''</tt> at cell B5, thus the formula evaluated is <tt>'''=LEN(B5)'''</tt>.

== Array formulas ==
The real power of arrays comes when you enter a formula in a special way, as an ‘array formula’. You do this by pressing '''Cntrl-Shift-Enter''' instead of the '''Enter''' button (or by ticking the ''Array'' checkbox if you are using the Formula Wizard).

If in cell B1 you enter <tt>'''={3; 4}'''</tt> ‘normally’ by pressing '''Enter''', the first value <tt>'''3'''</tt> is displayed in the cell.

If in cell B2 you type <tt>'''={3; 4}'''</tt> but press '''Cntrl-Shift-Enter''' instead of '''Enter''', the cell becomes an 'array formula'. The formula now returns the entire array <tt>'''{3; 4}'''</tt>. Cell B2 displays <tt>'''3'''</tt> and cell C2 displays <tt>'''4'''</tt>.

:''Note that if you entered the formula using the '''Enter''' key, simply selecting the cell and pressing '''Cntrl-Shift-Enter''' will not convert the cell to an array expression - you must make an actual edit (such as adding then deleting a character), or click the Function Wizard and tick the ''Array'' checkbox.''

If you now try to edit cell B2, you are told that ''"you cannot change only part of an array"''. To edit an array you must select the entire array, either with the mouse or by typing Cntrl-/ (hold the Cntrl key and press the slash key ‘/’).
[[Image:Calc_array6.png|right]]

The formula bar indicates that this is an array formula by enclosing it in curly braces {}. You do not need to type these - they will disappear while you edit the formula, and Calc will show them again when you have finished editing.
 

== Array formula calculations ==
When Calc evaluates an array formula, it treats ‘unexpected arrays’ as a series of values (rather than using a single value), calculating a result for each of the array elements, and returning an array of results.

''Example:''
[[Image:Calc_array3.png|right]]
:<tt>'''=SQRT( {16; 4; 25} )'''</tt> when entered by pressing '''Cntrl-Shift-Enter''' instead of '''Enter''' returns an array of results, with 1 row and 3 columns - <tt>'''{4; 2; 5}'''</tt>. If the formula is in cell B2, Calc places the results in cells B2:D2. <tt>'''4'''</tt> is placed in B2, <tt>'''2'''</tt> in C2 and <tt>'''5'''</tt> in D2.
 

The process in effect works thus:

#All ‘unexpected arrays’ in the same array calculation should be the same size (see this issue: Issue 46681).
#The result will be returned in an array of that size.
#The calculation is done for each element in turn, with the result returned in the corresponding element of the output array.

''Example:''

With the array formula <tt>'''=SQRT( {16; 4; 25} )'''</tt>:

#There is only one array, with 1 row and 3 columns.
#The result will be returned in an array with 1 row and 3 columns.
#The calculation is done for <tt>'''16'''</tt> first, then for <tt>'''4'''</tt>, then for <tt>'''25'''</tt>, giving the array result <tt>'''{4; 2; 5}'''</tt>.

''Example:''

With the array formula <tt>'''=SQRT( {8 | 18} * 2 )'''</tt> in cell A5:

#There is only one array, with 2 rows and 1 column.
#The result will be returned in an array with 2 rows and 1 column.
#The calculation is: first element <tt>'''SQRT(8*2) = 4'''</tt>; second element <tt>'''SQRT(18*2) = 6'''</tt>; the array result is thus <tt>'''{4 | 6}'''</tt> - that is, <tt>'''4'''</tt> in cell A5 and <tt>'''6'''</tt> in cell A6.

The result of an array expression is an array, which can be used within the formula.

''Example:''

:<tt>'''=SUM(SQRT( {16; 4; 25} ))'''</tt> as an array formula. The calculation of <tt>'''SQRT( {16; 4; 25} )'''</tt>, as before, yields a result of <tt>'''{4; 2; 5}'''</tt>, thus giving <tt>'''SUM( {4; 2; 5} )'''</tt>, returning a final result in the cell of 4+2+5 = <tt>'''11'''</tt>.

''Example:''

With the array formula <tt>'''=SUM(IF(A1:A4>0; B1:B4; 0))'''</tt>

#The two arrays <tt>'''A1:A4'''</tt> and <tt>'''B1:B4'''</tt> both have 4 rows and 1 column.
#The result of the <tt>'''IF()'''</tt> array calculation will be an array of that size, which <tt>'''SUM()'''</tt> will add up.
#If A1>0 the first element is <tt>'''B1'''</tt>; else <tt>'''0'''</tt>. If A2>0 the second element is <tt>'''B2'''</tt>; else <tt>'''0'''</tt> .... The array presented to <tt>'''SUM()'''</tt> has the values in B1:B4 where the adjacent value in A1:A4 is >0. The final output is the sum of the values in B1:B4 for which the adjacent value in A1:A4 is >0.

== Array functions ==
Some functions calculate their result as an array. If they are to return that array, they must be entered as an array formula, by pressing '''Cntrl-Shift-Enter''' (or by ticking the ''Array'' checkbox if you are using the Formula Wizard). If, instead, they are entered 'normally' with the '''Enter''' button, just the (single) top left element of the calculated array will be returned. These funtions are listed in the '''[[Documentation/How_Tos/Calc: Array functions|Array function category]]''', and are:
:[[Documentation/How_Tos/Calc: MINVERSE function|MINVERSE]], [[Documentation/How_Tos/Calc: MMULT function|MMULT]], [[Documentation/How_Tos/Calc: MUNIT function|MUNIT]], [[Documentation/How_Tos/Calc: TRANSPOSE function|TRANSPOSE]], [[Documentation/How_Tos/Calc: FREQUENCY function|FREQUENCY]], [[Documentation/How_Tos/Calc: GROWTH function|GROWTH]], [[Documentation/How_Tos/Calc: LINEST function|LINEST]], [[Documentation/How_Tos/Calc: LOGEST function|LOGEST]], [[Documentation/How_Tos/Calc: TREND function|TREND]]

''For example'': =[[Documentation/How_Tos/Calc: MUNIT function|MUNIT(2)]] entered as an array formula in cell B1 returns the 2 x 2 unit (identity) matrix as the array {1; 0 | 0; 1} - that is, cells B1, B2, C1, C2 show 1, 0, 0, 1. If this formula is entered 'normally', just the 'top left' value (1) is returned in cell B1.

Some functions (including some listed above) take parameters that are forced to evaluate as an array formula, even when the formula is entered 'normally':

:[[Documentation/How_Tos/Calc: MDETERM function|MDETERM]], [[Documentation/How_Tos/Calc: MINVERSE function|MINVERSE]], [[Documentation/How_Tos/Calc: MMULT function|MMULT]], [[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]], [[Documentation/How_Tos/Calc: SUMX2MY2 function|SUMX2MY2]], [[Documentation/How_Tos/Calc: SUMX2PY2 function|SUMX2PY2]], [[Documentation/How_Tos/Calc: SUMXMY2 function|SUMXMY2]], [[Documentation/How_Tos/Calc: CORREL function|CORREL]], [[Documentation/How_Tos/Calc: COVAR function|COVAR]], [[Documentation/How_Tos/Calc: FORECAST function|FORECAST]], [[Documentation/How_Tos/Calc: FTEST function|FTEST]], [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]], [[Documentation/How_Tos/Calc: MODE function|MODE]], [[Documentation/How_Tos/Calc: PEARSON function|PEARSON]], [[Documentation/How_Tos/Calc: PROB function|PROB]], [[Documentation/How_Tos/Calc: RSQ function|RSQ]], [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]], [[Documentation/How_Tos/Calc: STEYX function|STEYX]], [[Documentation/How_Tos/Calc: TTEST function|TTEST]]

''For example'': [[Documentation/How_Tos/Calc: MODE function|MODE(ABS(A1:A3))]] (in normal mode) forces ABS(A1:A3) to be evaluated as an array formula, returning an array of the absolute values of A1:A3 from which MODE selects the most common value to be returned.

''For example'': [[Documentation/How_Tos/Calc: PROB function|PROB(A1:A5; B1:B5/100; 3)]] (in normal mode) forces B1:B5/100 to be evaluated as an array formula, returning an array where each value is one hundredth of its value in B1:B5 (perhaps because the probabilities were expressed in percent).

A few of these functions have some (array) parameters that are forced to evaluate as an array formula, and other (single value) parameters that evaluate normally.

''For example'': [[Documentation/How_Tos/Calc: PROB function|PROB(A1:A5; B1:B5; {3; 4})]] (in normal mode) does not evaulate {3; 4} as an array, because this would mean PROB returning an array. It evaluates PROB(A1:A5; B1:B5; 3) - that is, it interprets {3; 4} in scalar mode, taking the 'top left' value 3. However, if entered as an array formula it will of course return an array.

== Tips and Tricks ==
All these examples are to be entered as an array expression, by pressing '''Cntrl-Shift-Enter'''.

====Sum of entries matching multiple conditions====
<tt>'''SUM( (A1:A6="red")*(B1:B6="big")*C1:C6 )'''</tt> returns the sum of entries in C1:C6 whose A column entries are <tt>'''"red"'''</tt> AND whose B column entries are <tt>'''"big"'''</tt>. A1:A6 and B1:B6 each produce a 6 element array of <tt>'''TRUE'''</tt> or <tt>'''FALSE'''</tt> - which in number calculations are <tt>'''1'''</tt> or <tt>'''0'''</tt>. Thus if A2 contains <tt>'''"red"'''</tt> and B2 contains <tt>'''"big"'''</tt> the second element of the array is <tt>'''1 * 1 * C2 = C2'''</tt>. If A2 contains <tt>'''"blue"'''</tt> instead, the second element of the array is <tt>'''0 * 1 * C2 = 0'''</tt>.

<tt>'''SUM( ((A1:A6="red")+(B1:B6="big")>0)*C1:C6 )'''</tt> returns the sum of entries in C1:C6 whose A column entries are <tt>'''"red"'''</tt> OR whose B column entries are <tt>'''"big"'''</tt>.

<tt>'''SUM( MOD((A1:A6="red")+(B1:B6="big");2)*C1:C6 )'''</tt> returns the sum of entries in C1:C6 either whose A column entries are <tt>'''"red"'''</tt> OR whose B column entries are <tt>'''"big"'''</tt> but not both (exclusive OR)

<tt>'''SUM( NOT((A1:A6="red")+(B1:B6="big"))*C1:C6 )'''</tt> returns the sum of entries in C1:C6 where neither the A column entry is <tt>'''"red"'''</tt> NOR the B column entry is <tt>'''"big"'''</tt>.

====Count of entries matching multiple conditions====
<tt>'''SUM( (A1:A6="red")*(B1:B6="big") )'''</tt> returns the number of rows whose A column entries are <tt>'''"red"'''</tt> AND whose B column entries are <tt>'''"big"'''</tt>.

====Maximum in a particular month====
<tt>'''MAX(IF(MONTH(B1:B9)=5; C1:C9; 0))'''</tt> returns the maximum value in C1:C9 where the corresponding B1:B9 date is in May (month <tt>'''5'''</tt>). The <tt>'''MIN()'''</tt> function would provide the minimum in the month.

====Average of entries meeting a condition====
<tt>'''AVERAGE(IF(A1:A9="red"; B1:B9; ""))'''</tt> returns the average of entries in B1:B9 whose A column entries are <tt>'''“red”'''</tt>. The <tt>'''AVERAGE'''</tt> function ignores any blank entries.

The <tt>'''MEDIAN'''</tt> function can be used similarly.

====Dynamic sorting of a column====
<tt>'''=LARGE(B3:B9;ROW(B3:B9)+1-ROW(B3))'''</tt> entered in cell C3 returns an array in C3:C9 which is B3:B9 in descending order. <tt>'''+1-ROW(3)'''</tt> is a constant - you could write <tt>'''=LARGE(B3:B9;ROW(B3:B9)-2)'''</tt> instead.

<tt>'''=SMALL(B3:B9;ROW(B3:B9)-2)'''</tt> returns B3:B9 in ascending order.

====Sum ignoring errors====
<tt>'''SUM( IF(ISERROR(A1:A9); 0; A1:A9) )'''</tt>. Normally <tt>'''SUM'''</tt> will propagate any error found.

====Average, ignoring zero entries====
<tt>'''AVERAGE(IF(B1:B9<>0; B1:B9; ""))'''</tt>

====Test a cell for one of a set of values====
<tt>'''OR(B2={2; 5; 6})'''</tt> as an array expression, or <tt>'''OR(B2=2; B2=5; B2=6)'''</tt> as a ‘normal’ formula.

====Sum of the smallest 4 numbers====
<tt>'''SUM(SMALL(B3:B9; {1;2;3;4}))'''</tt> or
<tt>'''SUM(SMALL(B3:B9;ROW(A1:A4)))'''</tt>. <tt>'''ROW(A1:A4)'''</tt> produces <tt>'''{1;2;3;4}'''</tt>.

====Last used cell in a column====
<tt>'''MAX(ROW(B1:B9)*(B1:B9<>""))'''</tt> returns the row number of the last cell used.

== Issues ==
* Before OOo2.4 spaces and negative numbers in inline arrays failed ([http://qa.openoffice.org/issues/show_bug.cgi?id=82644 issue 82644]).
* Before OOo3.1 it was not possible to include different size arrays in a formula ([http://qa.openoffice.org/issues/show_bug.cgi?id=46681 issue 46681]).
* Some functions cannot yet be used in array formulas: <tt>'''COUNTIF'''</tt>, <tt>'''SUMIF'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=65866 issue 65866]), and <tt>'''ISLOGICAL'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=87219 issue 87219]). <tt>'''MATCH'''</tt> ([http://qa.openoffice.org/issues/show_bug.cgi?id=8947 issue 8947]) works from OOo3.0.
* <tt>'''OFFSET'''</tt> has limited behaviour within an array formula.
* Names defined by '''Insert - Names - Define''' can be used within an array formula, but labels (either '''Insert - Names - Labels''' or automatically recognised at the head of a column) should not be used ([http://qa.openoffice.org/issues/show_bug.cgi?id=87268 issue 87268]).
* In Excel, not all functions can be used in array formulas. For example <tt>'''CONCATENATE'''</tt> works in Calc but not in Excel. Be aware of Excel's limitations if you plan to use a Calc spreadsheet in Excel.
* Volatile functions such as <tt>'''RAND'''</tt>, <tt>'''RANDBETWEEN'''</tt>, <tt>'''NOW'''</tt> may not be recalculated in array formulas; for example in <tt>'''{=A1:A3+RAND()}'''</tt> just one random number is generated ([http://qa.openoffice.org/issues/show_bug.cgi?id=67135 issue 67135]).
* Array formulas may slow down your computer if you have large arrays.

{{PDL1}}

[[Category:Documentation/How Tos/Calc]]
[[Category:Calc]]

Documentation/How Tos/Conditional Counting and Summation

2010-07-28T15:42:52Z

Drking: Added: Counting how many odd numbers

=Conditional Counting and Summation in Calc=

This is a review of various ways to count and sum the contents of cells conditionally, depending on the result of some test.

==COUNT==

The [[Documentation/How_Tos/Calc: COUNT function|COUNT function]] counts the number of cells which contain numbers and will ignore any others. For example text cells are ignored.

==COUNTA==

The [[Documentation/How_Tos/Calc: COUNTA function|COUNTA function]] counts the number of cells which contain anything (text, numbers, errors, logical values, formulas). It ignores empty (blank) cells.

==COUNTBLANK==

The [[Documentation/How_Tos/Calc: COUNTBLANK function|COUNTBLANK function]] counts the number of empty (blank) cells.

==SUM==

The [[Documentation/How_Tos/Calc: SUM function|SUM function]] sums all the numbers in the specified cells. See later for how to use it as a conditional function.

==SUBTOTAL==

The [[Documentation/How_Tos/Calc: SUBTOTAL function|SUBTOTAL function]] returns <tt>'''COUNT'''</tt>, <tt>'''COUNTA'''</tt> or <tt>'''SUM'''</tt> results for filtered data, that is data in cells chosen by a '''filter'''.

==COUNTIF==

The [[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF function]] counts those items that meet a single condition. For example <tt>'''COUNTIF(A1:A4; ">4")'''</tt> counts the cells in <tt>'''A1:A4'''</tt> that are greater than <tt>'''4'''</tt>.

==SUMIF==

The [[Documentation/How_Tos/Calc: SUMIF function|SUMIF function]] sums those items that meet a single condition. For example <tt>'''SUMIF(A1:A4; "=red"; B1:B4)'''</tt> sums the values in <tt>'''B1:B4'''</tt> that correspond to “<tt>'''red'''</tt>” entries in <tt>'''A1:A4'''</tt>.

==DCOUNT, DCOUNTA, DSUM==

The [[Documentation/How_Tos/Calc: DCOUNT function|DCOUNT function]],
[[Documentation/How_Tos/Calc: DCOUNTA function|DCOUNTA function]], and
[[Documentation/How_Tos/Calc: DSUM function|DSUM function]] perform similarly to <tt>'''COUNT'''</tt>, <tt>'''COUNTA'''</tt> and <tt>'''SUM'''</tt>, except that the cells to be counted or summed are chosen according to a table of conditions. For example, <tt>'''DCOUNT(A1:C5; 0; E6:F7)'''</tt> counts the number of rows of <tt>'''A1:C5'''</tt> for which the multiple conditions specified in <tt>'''E6:F7'''</tt> are all true.

==Conditions in Cell Ranges==

One simple method to count or sum using multiple conditions is to enter those conditions in a new row or column. For example, if <tt>'''A1:A6'''</tt> contains a list of colours and <tt>'''B1:B6'''</tt> a list of sizes, then we can enter in cell <tt>'''D1'''</tt> the formula <tt>'''<nowiki>=(A1="red")</nowiki>'''</tt>, which returns <tt>'''TRUE'''</tt> or <tt>'''FALSE'''</tt> depending if cell <tt>'''A1'''</tt> is <tt>'''red'''</tt> or not. Alternatively, we can enter in cell <tt>'''D1'''</tt> the formula <tt>'''<nowiki>=AND(A1="red"; B1="big")</nowiki>'''</tt>, which returns <tt>'''TRUE'''</tt> if cell <tt>'''A1'''</tt> is <tt>'''red'''</tt> AND cell <tt>'''B1'''</tt> is <tt>'''big'''</tt> and <tt>'''FALSE'''</tt> otherwise. Copy and paste this formula to <tt>'''D2:D6'''</tt> and we have a range of cells which are <tt>'''TRUE'''</tt> if the conditions are met and <tt>'''FALSE'''</tt> otherwise.

[[Image:Condsummation_1.png|example]]

In numerical calculations, <tt>'''TRUE'''</tt> is treated as <tt>'''1'''</tt>, and <tt>'''FALSE'''</tt> is treated as <tt>'''0'''</tt>. So entering <tt>'''<nowiki>=SUM(D1:D6)</nowiki>'''</tt> will simply sum those <tt>'''1'''</tt>s and <tt>'''0'''</tt>s, and give us the count of items that are both <tt>'''red'''</tt> AND <tt>'''big'''</tt>.

In fact, because <tt>'''TRUE'''</tt> and <tt>'''FALSE'''</tt> evaluate as <tt>'''1'''</tt> and <tt>'''0'''</tt>, we do not need the <tt>'''AND'''</tt> function - in <tt>'''D1'''</tt> we can simply write <tt>'''<nowiki>=(A1="red")*(B1="big")</nowiki>'''</tt>, and copy/paste down to <tt>'''D2:D6'''</tt>.

Now let us say that <tt>'''C1:C6'''</tt> contains a list of weights of these items, and we wish to know the total weight for all <tt>'''big'''</tt> <tt>'''red'''</tt> items. In <tt>'''D1'''</tt> we write <tt>'''<nowiki>=(A1="red")*(B1="big")*C1</nowiki>'''</tt>, and copy/paste down to <tt>'''D2:D6'''</tt>. <tt>'''D1'''</tt> will contain the weight in <tt>'''C1'''</tt> if the conditions are met (and zero otherwise) and so on for <tt>'''D2:D6'''</tt>, Therefore <tt>'''<nowiki>=SUM(D1:D6)</nowiki>'''</tt> will now give us the total weight.

[[Image:Condsummation_2.png|example]]

Alternatively, it is possible to fill <tt>'''D1:D6'''</tt> with an [[Documentation/How_Tos/Using Arrays|array formula]]. In <tt>'''D1'''</tt>, write <tt>'''<nowiki>=(A1:A6="red")*(B1:B6="big")*C1:C6</nowiki>'''</tt>, and enter by pressing Ctrl_Shift_Enter. All the cells in <tt>'''D1:D6'''</tt> now show the desired weights as before.

==SUMPRODUCT==

The [[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT function]] can be used to perform the counting and summation calculations in the previous section, without using extra columns. It is necessary to understand [[Documentation/How_Tos/Using Arrays|array formulas]] to understand this.

Using the summation example from the previous section, <tt>'''A1:A6="red"'''</tt>, <tt>'''B1:B6="big"'''</tt> and <tt>'''C1:C6'''</tt> may be treated as 3 separate arrays, not displayed but internally calculated.

<tt>'''<nowiki>=SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)</nowiki>'''</tt> will multiply corresponding elements of the arrays together and return their sum, i.e.:

'''(A1="red")*(B1="big")*C1 + (A2="red")*(B2="big")*C2 + ...'''

[[Image:Condsummation_3.png|example]]

This again gives us the total weight, without requiring an extra column.

For portability to Excel, use <tt>'''<nowiki>=SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)</nowiki>'''</tt>, as Calc and Excel treat logical results differently.

Notice that <tt>'''SUMPRODUCT'''</tt> formulas are simply entered by pressing the Enter key - they do not require Ctrl_Shift_Enter even though arrays are involved.

Also, be aware that calculations using very large arrays take a lot of computer processing time, and may slow the spreadsheet down.

==SUM with Array Formulas==

An alternative to SUMPRODUCT is to use the [[Documentation/How_Tos/Calc: SUM function|SUM function]]. The previous example would be written:

'''<nowiki>=SUM( (A1:A6="red")*(B1:B6="big")*C1:C6) )</nowiki>'''

and entered as an array formula by pressing Ctrl_Shift_Enter. As with SUMPRODUCT, this works by multiplying corresponding elements of the arrays together and returning their sum.

See [[Documentation/How_Tos/Using Arrays|'''Using Arrays''']] for details of array formulas and more examples.

==DataPilot==

Another way to approach conditional counting and summation is to use the DataPilot to generate an interactive table, whereby data can be arranged and summarised according to different points of view.

==Tips and Tricks: Checking Settings==

When matching text with some functions (such as <tt>'''SUMIF'''</tt>) , the results may well depend on the settings on the ''Tools menu-> Options-> OpenOffice.org Calc-> Calculate'' page. If the user's settings are incorrect, the results may therefore be wrong.

[[Image:Condsummation_4.png|settings]]

One solution is to include prominently in the spreadsheet a check that the settings are correct. For example:

'''<nowiki>=IF(ISERR(SEARCH(".";"a"));"ERROR: please enable regular expressions";"")</nowiki>'''

will show an error message if regular expressions are disabled.

Another example - in cell <tt>'''A3'''</tt> enter the text:

'''Check: '''

In cell <tt>'''A4'''</tt> enter:

'''<nowiki>="Regular expressions are "&IF(COUNTIF(A3;".*"); "enabled"; "disabled")</nowiki>'''

In cell <tt>'''A5'''</tt> enter:

'''<nowiki>="Whole cell matching is "&IF(COUNTIF(A3;"<>e"); "enabled"; "disabled")</nowiki>'''

[[Image:Condsummation_7.png|checking the settings]]

or better, use appropriate error messages.

==Tips and Tricks: Items Between Two Dates==

Dates are stored internally as numbers and can thus be compared easily. For example, to count the number of cells in A1:A6 between two dates, you could use:

'''<nowiki>=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))</nowiki>'''

If you express the dates with slashes (e.g. “<tt>'''1/2/2005'''</tt>”) you can dispense with the <tt>'''DATEVALUE'''</tt> function, as Calc will convert the date. However, be aware that in one country this text may be converted to 1Feb05 and in another to 2Jan05.

==Tips and Tricks: Summing the Largest/Smallest Items==

To add up the largest 3 numbers in <tt>'''A1:A5'''</tt>, the most straightforward method is to enter:

<tt>'''<nowiki>=LARGE(A1:A5; 1)</nowiki>'''</tt> in cell <tt>'''B1'''</tt>

<tt>'''<nowiki>=LARGE(A1:A5; 2)</nowiki>'''</tt> in cell <tt>'''B2'''</tt>

<tt>'''<nowiki>=LARGE(A1:A5; 3)</nowiki>'''</tt> in cell <tt>'''B3'''</tt>

so that the largest 3 numbers are in <tt>'''B1:B3'''</tt>, and then use the formula:

<tt>'''<nowiki>=SUM(B1:B3)</nowiki>'''</tt> to give the result.

[[Image:Condsummation_5.png|example]]

This method is very clear, and generally therefore to be recommended.

However if you wish to derive the same result in a single cell, you could use

'''<nowiki>=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))</nowiki>'''

[[Image:Condsummation_6.png|example]]

Here <tt>'''ROW(A1:A3)'''</tt> is a 1 column 3 row array containing the numbers <tt>'''1'''</tt>, <tt>'''2'''</tt>, <tt>'''3'''</tt>.

<tt>'''LARGE(A1:A5; ROW(A1:A3))'''</tt> is then a 1 column 3 row array containing the largest 3 numbers and <tt>'''SUMPRODUCT'''</tt> simply adds them up. We could use <tt>'''SUM'''</tt> instead of <tt>'''SUMPRODUCT'''</tt> but in that case the formula must be entered as an array formula by pressing Ctrl_Shift_Enter.

To add up the largest 4 numbers (say), use <tt>'''...ROW(A1:A4)..'''</tt> instead of <tt>'''...ROW(A1:A3)..'''</tt>

To add the smallest numbers, use <tt>'''SMALL(...)'''</tt> instead of <tt>'''LARGE(...)'''</tt>.

==Tips and Tricks: Summing Matching Blank, etc. Cells==

'''<nowiki>=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)</nowiki>'''

sums those cells in <tt>'''B1:B5'''</tt> corresponding to blank cells in <tt>'''A1:A5'''</tt>.

Other similar functions (e.g. <tt>'''ISTEXT'''</tt>, <tt>'''ISNUMBER'''</tt>) can be used in the same way.

==Tips and Tricks: Summing More Than One Column==

'''<nowiki>=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)</nowiki>'''

will sum cells in <tt>'''E1:E6'''</tt> and in <tt>'''F1:F6'''</tt> which correspond to cells in <tt>'''D1:D6'''</tt> containing <tt>'''red'''</tt>. For example if <tt>'''D2'''</tt> and <tt>'''D4'''</tt> contain red, the result is <tt>'''E2+F2+E4+F4'''</tt>.

==Tips and Tricks: Counting How Many Odd Numbers==

'''<nowiki>=SUMPRODUCT(MOD(A1:A8; 2))</nowiki>'''

will return the number of numbers in A1:A8 which are odd (1, 3, 5, 7,...), and

'''<nowiki>=SUMPRODUCT(MOD(A1:A8; 2) = 0)</nowiki>'''

will return the number which are even.

==Tips and Tricks: Summing Every nth Row==

'''<nowiki>=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0; </nowiki> A1:A8)'''

will sum every second row in <tt>'''A1:A8'''</tt>. Change the <tt>'''=0'''</tt> to <tt>'''=1'''</tt> to sum every second row, but starting at the first row. Change the <tt>'''2'''</tt> to <tt>'''3'''</tt> to sum every third row, and so on.

[[Image:Condsummation_8.png|example]]

==Tips and Tricks: Summing Items With Certain Formatting==

The CELL function returns information about cells, for instance the format that numbers or dates are displayed in and the column width. It can thus be used with one of the methods above. There is no function that returns the colour or font of a cell. If no independent formula relating to colour or font exists, then it may be necessary to use a macro.

==Tips and Tricks: Summing Matching Items in a Separate List==

Say that <tt>'''A1:A9'''</tt> contains a list of dates, <tt>'''B1:B9'''</tt> contains phone numbers and <tt>'''C1:C9'''</tt> the costs of making phone calls. <tt>'''F1:F5'''</tt> is a list of certain phone numbers, and you want to know the total cost of calls to these numbers.

In cell <tt>'''D1'''</tt> enter:

'''<nowiki>=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1</nowiki>'''

and copy/paste down to <tt>'''D2:D9'''</tt>

<tt>'''<nowiki>=SUM(D1:D9)</nowiki>'''</tt> now gives the total cost of calls.

[[Image:Condsummation_9.png|example]]

To perform this calculation without an extra column, you could use:

<nowiki>=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)</nowiki>

We discussed above how it can be important to check Calc settings; this is a good example - if the user has regular expressions turned on (the default) a telephone number written as (720) 528-1700 is interpreted as a regular expression and might match entries other than (720) 528-1700.

[[fr:Documentation/FR/Calc/Sommes et calculs conditionnels]]
[[nl:Documentation/nl/How_Tos/Optellen_en_tellen_met_voorwaarden]]

{{PDL1}}

[[Category:Calc]]
[[Category:Documentation/How Tos/Calc]]

Documentation/How Tos/Calc: TYPE function

2010-07-24T12:40:15Z

Drking: more 'arrays' detail

__NOTOC__

== TYPE ==
Returns the type of value (number, text, etc.).

=== Syntax: ===
<tt>'''TYPE(value)'''</tt>

: Returns the type of value as a number: <tt>'''1'''</tt> = number, <tt>'''2'''</tt> = text, <tt>'''4'''</tt> = logical value, <tt>'''8'''</tt> = formula, <tt>'''16'''</tt> = error value, <tt>'''64'''</tt> = array.

: If a cell contains both an error and a formula, <tt>'''16'''</tt> (error value) is returned.
: A blank cell is classed as a number here, and <tt>'''1'''</tt> is returned.

=== Example: ===
<tt>'''TYPE(C2)'''</tt>
: where cell C2 contains <tt>'''dog'''</tt> returns <tt>'''2'''</tt>, because <tt>'''dog'''</tt> is text.

<tt>'''TYPE(C3)'''</tt>
: where cell C3 contains <tt>'''=NA()'''</tt> returns <tt>'''16'''</tt>, because <tt>'''NA()'''</tt> returns the error value <tt>'''#N/A'''</tt>.

Advanced topic:

For more information on arrays, see [[Documentation/How Tos/Using Arrays|Using arrays]].

<tt>'''TYPE({3;5;7})'''</tt>
: returns <tt>'''64'''</tt>

<tt>'''{=TYPE(A1:B1)}'''</tt> entered as an array formula in cells A3:B3
: returns <tt>'''64'''</tt> in cells A3:B3 (whatever the contents of cells A1 and B1), as A1:B1 is an internal array in the array formula.

<tt>'''TYPE(A1:B1)'''</tt> entered as a normal formula in both cells A3 and B3
: returns <tt>'''TYPE(A1)'''</tt> in cell A3, and <tt>'''TYPE(B1)'''</tt> in cell B3. Here Calc finds the row/column intersection of the formula cell with range A1:B1, to determine a reference to the single cell to examine.

<tt>'''TYPE(D1)'''</tt>, where D1:D2 contain the array formula {={4;2}}
: returns <tt>'''8'''</tt>. Here the result is for D1's content, which although part of an array is not the array itself.

=== Issues: ===
* Calc returns <tt>'''8'''</tt> for a formula. Excel returns the TYPE of the formula's returned value; it has no return value to recognise a formula.

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

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

* [[Documentation/How Tos/Using Arrays|Using arrays]]

* [[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/Information functions]]

Documentation/How Tos/Calc: TYPE function

2010-07-24T12:25:45Z

Drking: revision of array reporting

__NOTOC__

== TYPE ==
Returns the type of value (number, text, etc.).

=== Syntax: ===
<tt>'''TYPE(value)'''</tt>

: Returns the type of value as a number: <tt>'''1'''</tt> = number, <tt>'''2'''</tt> = text, <tt>'''4'''</tt> = logical value, <tt>'''8'''</tt> = formula, <tt>'''16'''</tt> = error value, <tt>'''64'''</tt> = array.

: If a cell contains both an error and a formula, <tt>'''16'''</tt> (error value) is returned.
: A blank cell is classed as a number here, and <tt>'''1'''</tt> is returned.

=== Example: ===
<tt>'''TYPE(C2)'''</tt>
: where cell C2 contains <tt>'''dog'''</tt> returns <tt>'''2'''</tt>, because <tt>'''dog'''</tt> is text.

<tt>'''TYPE(C3)'''</tt>
: where cell C3 contains <tt>'''=NA()'''</tt> returns <tt>'''16'''</tt>, because <tt>'''NA()'''</tt> returns the error value <tt>'''#N/A'''</tt>.

Advanced topics:

<tt>'''TYPE({3;5;7})'''</tt>
: returns <tt>'''64'''</tt>

<tt>'''{=TYPE(A1:B1)}'''</tt> entered as an array formula in cells A3:B3
: returns <tt>'''64'''</tt> in cells A3:B3 (whatever the contents of cells A1 and B1), as A1:B1 is an internal array in the array formula.

<tt>'''TYPE(A1:B1)'''</tt> entered as a normal formula in both cells A3 and B3
: returns <tt>'''TYPE(A1)'''</tt> in cell A3, and <tt>'''TYPE(B1)'''</tt> in cell B3. Here Calc finds the row/column intersection of the formula cell with range A1:B1, to determine a reference to the single cell to examine. For more information, see [[Documentation/How Tos/Using Arrays|Using arrays]].

=== Issues: ===
* Calc returns <tt>'''8'''</tt> for a formula. Excel returns the TYPE of the formula's returned value; it has no return value to recognise a formula.

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

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

* [[Documentation/How Tos/Using Arrays|Using arrays]]

* [[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/Information functions]]

Documentation/How Tos/Calc: TYPE function

2010-07-23T20:42:27Z

Drking: added: return value 64; issue of Excel having no return value 8

FR/Documentation/Calc: fonction LOI.POISSON

2008-12-21T22:58:24Z

Drking: /* Syntaxe: */ correction: lambda need not be integer

__NOTOC__

== LOI.POISSON ==
Calcule les valeurs d'une distribution de Poisson.

=== Syntaxe: ===
<tt>'''LOI.POISSON(x; λ; mode)'''</tt>
:La distribution de Poisson est une distribution de probabilité discrète donnant la probabilité que <tt>'''x'''</tt> événements adviennent dans un certain temps, où les événements adviennent indépendamment et où la moyenne des éléments <tt>'''λ'''</tt> est attendue. <tt>'''x'''</tt> doit être >=0 et <tt>'''λ'''</tt> doit être >0. <tt>'''x'''</tt> doit être des nombres entiers.

: Si mode est <tt>'''0'''</tt>, <tt>'''LOI.POISSON'''</tt> calcule la fonction de densité de probabilité de la distribution Poisson :
::[[Image:Calc_poisson0_equation.png]]

: Si mode est <tt>'''1'''</tt>, <tt>'''LOI.POISSON'''</tt> calcule la fonction de distribution cumulative de la distribution Poisson :
::[[Image:Calc_poisson1_equation.png]]

=== Exemple : ===
<tt>'''LOI.POISSON(8; 9; 0)'''</tt>
: renvoie approximativement 0,1317, la probabilité qu'exactement 7 événements adviennent dans une période de temps donnée où vous attendez 9 événements.

<tt>'''LOI.POISSON(8; 9; 1)'''</tt>
: renvoie approximativement 0,4556, la propabilité que jusqu'à 8 événements inclus adviennent dans un période de temps donnée où vous en attendiez 9.

=== Voir également : ===
[[Documentation/FR/Calc:_Fonctions_statistiques|'''Fonctions statistiques''']]

[[Documentation/FR/Calc:_Fonctions_listees_alphabetiquement|'''Fonctions listées alphabétiquement''']],
[[Documentation/FR/Calc/Fonctions_listees_par_categories|'''Fonctions listées par catégories''']]

Documentation/How Tos/Calc: POISSON function

2008-12-21T22:56:13Z

Drking: /* Syntax: */ correction: lamda need not be integer

__NOTOC__

== POISSON ==
Calculates values for a Poisson distribution.

=== Syntax: ===
<tt>'''POISSON(x; λ; mode)'''</tt>
: The Poisson distribution is a discrete probability distribution giving the probability that <tt>'''x'''</tt> events occur in a certain time, where events occur independently, and where on average <tt>'''λ'''</tt> events are expected. <tt>'''x'''</tt> should be >=0 and <tt>'''λ'''</tt> should be >0. <tt>'''x'''</tt> should be integer.

: If mode is <tt>'''0'''</tt>, <tt>'''POISSON'''</tt> calculates the probability density function of the Poisson distribution:
::[[Image:Calc_poisson0_equation.png]]

: If mode is <tt>'''1'''</tt>, <tt>'''POISSON'''</tt> calculates the cumulative distribution function of the Poisson distribution:
::[[Image:Calc_poisson1_equation.png]]

=== Example: ===
<tt>'''POISSON(8; 9; 0)'''</tt>
: returns approximately 0.1317, the probability that exactly 8 events occur in a time period where you expect 9 events.

<tt>'''POISSON(8; 9; 1)'''</tt>
: returns approximately 0.4556, the probability that up to and including 8 events occur in a time period where you expect 9 events.

=== See also: ===
[[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''']]

FR/Documentation/Calc: Fonctions listees alphabetiquement

2008-11-16T08:27:38Z

Drking: Initial content

__NOTOC__

== Fonctions Calc listées alphabétiquement ==

Vous pouvez aussi voir une liste des '''[[Documentation/FR/Calc/Fonctions_listees_par_categories|fonctions listées par catégories]]'''.

*[[Documentation/FR/Calc:_fonction_ABS|ABS]], [[Documentation/FR/Calc:_fonction_ACOS|ACOS]], [[Documentation/FR/Calc:_fonction_ACOSH|ACOSH]], [[Documentation/FR/Calc:_fonction_ACOT|ACOT]], [[Documentation/FR/Calc:_fonction_ACOTH|ACOTH]], [[Documentation/FR/Calc:_fonction_ACTUELLE|ACTUELLE]], [[Documentation/FR/Calc:_fonction_ADRESSE|ADRESSE]], [[Documentation/FR/Calc:_fonction_ALEA.ENTRE.BORNES|ALEA.ENTRE.BORNES]], [[Documentation/FR/Calc:_fonction_ALEA|ALEA]], [[Documentation/FR/Calc:_fonction_AMORDEGRC|AMORDEGRC]], [[Documentation/FR/Calc:_fonction_AMORLIN|AMORLIN]], [[Documentation/FR/Calc:_fonction_AMORLINC|AMORLINC]], [[Documentation/FR/Calc:_fonction_ANNEE|ANNEE]], [[Documentation/FR/Calc:_fonction_ANNEES|ANNEES]], [[Documentation/FR/Calc:_fonction_ARABE|ARABE]], [[Documentation/FR/Calc:_fonction_ARRONDI.AU.MULTIPLE|ARRONDI.AU.MULTIPLE]], [[Documentation/FR/Calc:_fonction_ARRONDI.INF|ARRONDI.INF]], [[Documentation/FR/Calc:_fonction_ARRONDI.SUP|ARRONDI.SUP]], [[Documentation/FR/Calc:_fonction_ARRONDI|ARRONDI]], [[Documentation/FR/Calc:_fonction_ASIN|ASIN]], [[Documentation/FR/Calc:_fonction_ASINH|ASINH]], [[Documentation/FR/Calc:_fonction_ATAN|ATAN]], [[Documentation/FR/Calc:_fonction_ATAN2|ATAN2]], [[Documentation/FR/Calc:_fonction_ATANH|ATANH]], [[Documentation/FR/Calc:_fonction_AUJOURDHUI|AUJOURDHUI]]

*[[Documentation/FR/Calc:_fonction_B|B]], [[Documentation/FR/Calc:_fonction_BAHTTEXT|BAHTTEXT]], [[Documentation/FR/Calc:_fonction_BASE|BASE]], [[Documentation/FR/Calc:_fonction_BDECARTYPE|BDECARTYPE]], [[Documentation/FR/Calc:_fonction_BDECARTYPEP|BDECARTYPEP]], [[Documentation/FR/Calc:_fonction_BDLIRE|BDLIRE]], [[Documentation/FR/Calc:_fonction_BDMAX|BDMAX]], [[Documentation/FR/Calc:_fonction_BDMIN|BDMIN]], [[Documentation/FR/Calc:_fonction_BDMOYENNE|BDMOYENNE]], [[Documentation/FR/Calc:_fonction_BDNB|BDNB]], [[Documentation/FR/Calc:_fonction_BDNBVAL|BDNBVAL]], [[Documentation/FR/Calc:_fonction_BDPRODUIT|BDPRODUIT]], [[Documentation/FR/Calc:_fonction_BDSOMME|BDSOMME]], [[Documentation/FR/Calc:_fonction_BDVAR|BDVAR]], [[Documentation/FR/Calc:_fonction_BDVARP|BDVARP]], [[Documentation/FR/Calc:_fonction_BESSELI|BESSELI]], [[Documentation/FR/Calc:_fonction_BESSELJ|BESSELJ]], [[Documentation/FR/Calc:_fonction_BESSELK|BESSELK]], [[Documentation/FR/Calc:_fonction_BESSELY|BESSELY]], [[Documentation/FR/Calc:_fonction_BETA.INVERSE|BETA.INVERSE]], [[Documentation/FR/Calc:_fonction_BINDEC|BINDEC]], [[Documentation/FR/Calc:_fonction_BINHEX|BINHEX]], [[Documentation/FR/Calc:_fonction_BINOCT|BINOCT]]

*[[Documentation/FR/Calc:_fonction_CAR|CAR]], [[Documentation/FR/Calc:_fonction_CELLULE|CELLULE]], [[Documentation/FR/Calc:_fonction_CENTILE|CENTILE]], [[Documentation/FR/Calc:_fonction_CENTREE.REDUITE|CENTREE.REDUITE]], [[Documentation/FR/Calc:_fonction_CHERCHE|CHERCHE]], [[Documentation/FR/Calc:_fonction_CHOISIR|CHOISIR]], [[Documentation/FR/Calc:_fonction_CNUM|CNUM]], [[Documentation/FR/Calc:_fonction_CODE|CODE]], [[Documentation/FR/Calc:_fonction_COEFFICIENT.ASYMETRIE|COEFFICIENT.ASYMETRIE]], [[Documentation/FR/Calc:_fonction_COEFFICIENT.CORRELATION|COEFFICIENT.CORRELATION]], [[Documentation/FR/Calc:_fonction_COEFFICIENT.DETERMINATION|COEFFICIENT.DETERMINATION]], [[Documentation/FR/Calc:_fonction_COLONNE|COLONNE]], [[Documentation/FR/Calc:_fonction_COLONNES|COLONNES]], [[Documentation/FR/Calc:_fonction_COMBIN|COMBIN]], [[Documentation/FR/Calc:_fonction_COMBINA|COMBINA]], [[Documentation/FR/Calc:_fonction_COMPLEXE.ARGUMENT|COMPLEXE.ARGUMENT]], [[Documentation/FR/Calc:_fonction_COMPLEXE.CONJUGUE|COMPLEXE.CONJUGUE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.COS|COMPLEXE.COS]], [[Documentation/FR/Calc:_fonction_COMPLEXE.DIFFERENCE|COMPLEXE.DIFFERENCE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.DIV|COMPLEXE.DIV]], [[Documentation/FR/Calc:_fonction_COMPLEXE.EXP|COMPLEXE.EXP]], [[Documentation/FR/Calc:_fonction_COMPLEXE.IMAGINAIRE|COMPLEXE.IMAGINAIRE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.LN|COMPLEXE.LN]], [[Documentation/FR/Calc:_fonction_COMPLEXE.LOG10|COMPLEXE.LOG10]], [[Documentation/FR/Calc:_fonction_COMPLEXE.LOG2|COMPLEXE.LOG2]], [[Documentation/FR/Calc:_fonction_COMPLEXE.MODULE|COMPLEXE.MODULE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.PRODUIT|COMPLEXE.PRODUIT]], [[Documentation/FR/Calc:_fonction_COMPLEXE.PUISSANCE|COMPLEXE.PUISSANCE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.RACINE|COMPLEXE.RACINE]], [[Documentation/FR/Calc:_fonction_COMPLEXE.REEL|COMPLEXE.REEL]], [[Documentation/FR/Calc:_fonction_COMPLEXE.SIN|COMPLEXE.SIN]], [[Documentation/FR/Calc:_fonction_COMPLEXE.SOMME|COMPLEXE.SOMME]], [[Documentation/FR/Calc:_fonction_COMPLEXE|COMPLEXE]], [[Documentation/FR/Calc:_fonction_CONCATENER|CONCATENER]], [[Documentation/FR/Calc:_fonction_CONVERT_ADD|CONVERT_ADD]], [[Documentation/FR/Calc:_fonction_CONVERTIR|CONVERTIR]], [[Documentation/FR/Calc:_fonction_COS|COS]], [[Documentation/FR/Calc:_fonction_COSH|COSH]], [[Documentation/FR/Calc:_fonction_COT|COT]], [[Documentation/FR/Calc:_fonction_COTH|COTH]], [[Documentation/FR/Calc:_fonction_COVARIANCE|COVARIANCE]], [[Documentation/FR/Calc:_fonction_CRITERE.LOI.BINOMIALE|CRITERE.LOI.BINOMIALE]], [[Documentation/FR/Calc:_fonction_CROISSANCE|CROISSANCE]], [[Documentation/FR/Calc:_fonction_CTXT|CTXT]], [[Documentation/FR/Calc:_fonction_CUMUL.INTER_ADD|CUMUL.INTER_ADD]], [[Documentation/FR/Calc:_fonction_CUMUL.PRINCPER_ADD|CUMUL.PRINCPER_ADD]], [[Documentation/FR/Calc:_fonction_CUMUL.PRINCPER|CUMUL.PRINCPER]], [[Documentation/FR/Calc:_fonction_CUMUL|CUMUL.INTER]]

*[[Documentation/FR/Calc:_fonction_DATE.COUPON.PREC|DATE.COUPON.PREC]], [[Documentation/FR/Calc:_fonction_DATE.COUPON.SUIV|DATE.COUPON.SUIV]], [[Documentation/FR/Calc:_fonction_DATE|DATE]], [[Documentation/FR/Calc:_fonction_DATEVAL|DATEVAL]], [[Documentation/FR/Calc:_fonction_DB|DB]], [[Documentation/FR/Calc:_fonction_DDB|DDB]], [[Documentation/FR/Calc:_fonction_DDE|DDE]], [[Documentation/FR/Calc:_fonction_DECALER|DECALER]], [[Documentation/FR/Calc:_fonction_DECBIN|DECBIN]], [[Documentation/FR/Calc:_fonction_DECHEX|DECHEX]], [[Documentation/FR/Calc:_fonction_DECIMAL|DECIMAL]], [[Documentation/FR/Calc:_fonction_DECOCT|DECOCT]], [[Documentation/FR/Calc:_fonction_DEGRES|DEGRES]], [[Documentation/FR/Calc:_fonction_DELTA|DELTA]], [[Documentation/FR/Calc:_fonction_DETERMAT|DETERMAT]], [[Documentation/FR/Calc:_fonction_DIMANCHEDEPAQUES|DIMANCHEDEPAQUES]], [[Documentation/FR/Calc:_fonction_DROITE|DROITE]], [[Documentation/FR/Calc:_fonction_DROITEREG|DROITEREG]], [[Documentation/FR/Calc:_fonction_DUREE_ADD|DUREE_ADD]], [[Documentation/FR/Calc:_fonction_DUREE.MODIFIEE|DUREE.MODIFIEE]], [[Documentation/FR/Calc:_fonction_DUREE|DUREE]]

*[[Documentation/FR/Calc:_fonction_ECART.MOYEN|ECART.MOYEN]], [[Documentation/FR/Calc:_fonction_ECARTYPE|ECARTYPE]], [[Documentation/FR/Calc:_fonction_ECARTYPEA|ECARTYPEA]], [[Documentation/FR/Calc:_fonction_ECARTYPEP|ECARTYPEP]], [[Documentation/FR/Calc:_fonction_ECARTYPEPA|ECARTYPEPA]], [[Documentation/FR/Calc:_fonction_ENT|ENT]], [[Documentation/FR/Calc:_fonction_EPURAGE|EPURAGE]], [[Documentation/FR/Calc:_fonction_EQUIV|EQUIV]], [[Documentation/FR/Calc:_fonction_ERF|ERF]], [[Documentation/FR/Calc:_fonction_ERFC|ERFC]], [[Documentation/FR/Calc:_fonction_ERREUR.TYPE.XY|ERREUR.TYPE.XY]], [[Documentation/FR/Calc:_fonction_EST.IMPAIR_ADD|EST.IMPAIR_ADD]], [[Documentation/FR/Calc:_fonction_EST.IMPAIR|EST.IMPAIR]], [[Documentation/FR/Calc:_fonction_EST.IMPAIR|EST.IMPAIR]], [[Documentation/FR/Calc:_fonction_EST.PAIR_ADD|EST.PAIR_ADD]], [[Documentation/FR/Calc:_fonction_EST.PAIR|EST.PAIR]], [[Documentation/FR/Calc:_fonction_EST.PAIR|EST.PAIR]], [[Documentation/FR/Calc:_fonction_ESTBISSEXTILE|ESTBISSEXTILE]], [[Documentation/FR/Calc:_fonction_ESTERR|ESTERR]], [[Documentation/FR/Calc:_fonction_ESTERREUR|ESTERREUR]], [[Documentation/FR/Calc:_fonction_ESTFORMULE|ESTFORMULE]], [[Documentation/FR/Calc:_fonction_ESTLOGIQUE|ESTLOGIQUE]], [[Documentation/FR/Calc:_fonction_ESTNA|ESTNA]], [[Documentation/FR/Calc:_fonction_ESTNONTEXTE|ESTNONTEXTE]], [[Documentation/FR/Calc:_fonction_ESTNUM|ESTNUM]], [[Documentation/FR/Calc:_fonction_ESTREF|ESTREF]], [[Documentation/FR/Calc:_fonction_ESTTEXTE|ESTTEXTE]], [[Documentation/FR/Calc:_fonction_ESTVIDE|ESTVIDE]], [[Documentation/FR/Calc:_fonction_ET|ET]], [[Documentation/FR/Calc:_fonction_EXACT|EXACT]], [[Documentation/FR/Calc:_fonction_EXP|EXP]]

*[[Documentation/FR/Calc:_fonction_FACT|FACT]], [[Documentation/FR/Calc:_fonction_FACTDOUBLE|FACTDOUBLE]], [[Documentation/FR/Calc:_fonction_FAUX|FAUX]], [[Documentation/FR/Calc:_fonction_FEUILLE|FEUILLE]], [[Documentation/FR/Calc:_fonction_FEUILLES|FEUILLES]], [[Documentation/FR/Calc:_fonction_FIN.MOIS|FIN.MOIS]], [[Documentation/FR/Calc:_fonction_FISHER.INVERSE|FISHER.INVERSE]], [[Documentation/FR/Calc:_fonction_FISHER|FISHER]], [[Documentation/FR/Calc:_fonction_FORMULE|FORMULE]], [[Documentation/FR/Calc:_fonction_FRACTIONANNEE|FRACTIONANNEE]], [[Documentation/FR/Calc:_fonction_FRANC|FRANC]], [[Documentation/FR/Calc:_fonction_FREQUENCE|FREQUENCE]]

*[[Documentation/FR/Calc:_fonction_GAUCHE|GAUCHE]], [[Documentation/FR/Calc:_fonction_GAUSS|GAUSS]], [[Documentation/FR/Calc:_fonction_GRANDE.VALEUR|GRANDE.VALEUR]]

*[[Documentation/FR/Calc:_fonction_HEURE|HEURE]], [[Documentation/FR/Calc:_fonction_HEXBIN|HEXBIN]], [[Documentation/FR/Calc:_fonction_HEXDEC|HEXDEC]], [[Documentation/FR/Calc:_fonction_HEXOCT|HEXOCT]]

*[[Documentation/FR/Calc:_fonction_IMPAIR|IMPAIR]], [[Documentation/FR/Calc:_fonction_INDEX|INDEX]], [[Documentation/FR/Calc:_fonction_INDIRECT|INDIRECT]], [[Documentation/FR/Calc:_fonction_INFO|INFO]], [[Documentation/FR/Calc:_fonction_INTERET.ACC.MAT|INTERET.ACC.MAT]], [[Documentation/FR/Calc:_fonction_INTERET.ACC|INTERET.ACC]], [[Documentation/FR/Calc:_fonction_INTERVALLE.CONFIANCE|INTERVALLE.CONFIANCE]], [[Documentation/FR/Calc:_fonction_INTPER|INTPER]], [[Documentation/FR/Calc:_fonction_INVERSE.LOI.F|INVERSE.LOI.F]], [[Documentation/FR/Calc:_fonction_INVERSEMAT|INVERSEMAT]], [[Documentation/FR/Calc:_fonction_ISPMT|ISPMT]]

*[[Documentation/FR/Calc:_fonction_JOUR|JOUR]], [[Documentation/FR/Calc:_fonction_JOURS|JOURS]], [[Documentation/FR/Calc:_fonction_JOURS360|JOURS360]], [[Documentation/FR/Calc:_fonction_JOURSDANSANNEE|JOURSDANSANNEE]], [[Documentation/FR/Calc:_fonction_JOURSDANSMOIS|JOURSDANSMOIS]], [[Documentation/FR/Calc:_fonction_JOURSEM|JOURSEM]]

*[[Documentation/FR/Calc:_fonction_KHIDEUX.INVERSE|KHIDEUX.INVERSE]], [[Documentation/FR/Calc:_fonction_KURTOSIS|KURTOSIS]]

*[[Documentation/FR/Calc:_fonction_LIEN_HYPERTEXTE|LIEN_HYPERTEXTE]], [[Documentation/FR/Calc:_fonction_LIGNE|LIGNE]], [[Documentation/FR/Calc:_fonction_LIGNES|LIGNES]], [[Documentation/FR/Calc:_fonction_LN|LN]], [[Documentation/FR/Calc:_fonction_LNGAMMA|LNGAMMA]], [[Documentation/FR/Calc:_fonction_LOG|LOG]], [[Documentation/FR/Calc:_fonction_LOG10|LOG10]], [[Documentation/FR/Calc:_fonction_LOGREG|LOGREG]], [[Documentation/FR/Calc:_fonction_LOI.BETA|LOI.BETA]], [[Documentation/FR/Calc:_fonction_LOI.BINOMIALE.NEG|LOI.BINOMIALE.NEG]], [[Documentation/FR/Calc:_fonction_LOI.BINOMIALE|LOI.BINOMIALE]], [[Documentation/FR/Calc:_fonction_LOI.EXPONENTIELLE|LOI.EXPONENTIELLE]], [[Documentation/FR/Calc:_fonction_LOI.F|LOI.F]], [[Documentation/FR/Calc:_fonction_LOI.GAMMA.INVERSE|LOI.GAMMA.INVERSE]], [[Documentation/FR/Calc:_fonction_LOI.GAMMA|LOI.GAMMA]], [[Documentation/FR/Calc:_fonction_LOI.HYPERGEOMETRIQUE|LOI.HYPERGEOMETRIQUE]], [[Documentation/FR/Calc:_fonction_LOI.KHIDEUX|LOI.KHIDEUX]], [[Documentation/FR/Calc:_fonction_LOI.LOGNORMALE.INVERSE|LOI.LOGNORMALE.INVERSE]], [[Documentation/FR/Calc:_fonction_LOI.LOGNORMALE|LOI.LOGNORMALE]], [[Documentation/FR/Calc:_fonction_LOI.NORMALE.INVERSE|LOI.NORMALE.INVERSE]], [[Documentation/FR/Calc:_fonction_LOI.NORMALE.STANDARD.INVERSE|LOI.NORMALE.STANDARD.INVERSE]], [[Documentation/FR/Calc:_fonction_LOI.NORMALE.STANDARD|LOI.NORMALE.STANDARD]], [[Documentation/FR/Calc:_fonction_LOI.NORMALE|LOI.NORMALE]], [[Documentation/FR/Calc:_fonction_LOI.POISSON|LOI.POISSON]], [[Documentation/FR/Calc:_fonction_LOI.STUDENT.INVERSE|LOI.STUDENT.INVERSE]], [[Documentation/FR/Calc:_fonction_LOI.STUDENT|LOI.STUDENT]], [[Documentation/FR/Calc:_fonction_LOI.WEIBULL|LOI.WEIBULL]]

*[[Documentation/FR/Calc:_fonction_MAINTENANT|MAINTENANT]], [[Documentation/FR/Calc:_fonction_MAJUSCULE|MAJUSCULE]], [[Documentation/FR/Calc:_fonction_MAX|MAX]], [[Documentation/FR/Calc:_fonction_MAXA|MAXA]], [[Documentation/FR/Calc:_fonction_MEDIANE|MEDIANE]], [[Documentation/FR/Calc:_fonction_MIN|MIN]], [[Documentation/FR/Calc:_fonction_MINA|MINA]], [[Documentation/FR/Calc:_fonction_MINUSCULE|MINUSCULE]], [[Documentation/FR/Calc:_fonction_MINUTE|MINUTE]], [[Documentation/FR/Calc:_fonction_MOD|MOD]], [[Documentation/FR/Calc:_fonction_MODE|MODE]], [[Documentation/FR/Calc:_fonction_MOIS.DECALER|MOIS.DECALER]], [[Documentation/FR/Calc:_fonction_MOIS|MOIS]], [[Documentation/FR/Calc:_fonction_MOYENNE.GEOMETRIQUE|MOYENNE.GEOMETRIQUE]], [[Documentation/FR/Calc:_fonction_MOYENNE.HARMONIQUE|MOYENNE.HARMONIQUE]], [[Documentation/FR/Calc:_fonction_MOYENNE.REDUITE|MOYENNE.REDUITE]], [[Documentation/FR/Calc:_fonction_MOYENNE|MOYENNE]], [[Documentation/FR/Calc:_fonction_MOYENNEA|MOYENNEA]], [[Documentation/FR/Calc:_fonction_MULTINOMIALE|MULTINOMIALE]], [[Documentation/FR/Calc:_fonction_MUNIT|MUNIT]]

*[[Documentation/FR/Calc:_fonction_N|N]], [[Documentation/FR/Calc:_fonction_NA|NA]], [[Documentation/FR/Calc:_fonction_NB.COUPONS|NB.COUPONS]], [[Documentation/FR/Calc:_fonction_NB.JOURS.COUPON.PREC|NB.JOURS.COUPON.PREC]], [[Documentation/FR/Calc:_fonction_NB.JOURS.COUPON.SUIV|NB.JOURS.COUPON.SUIV]], [[Documentation/FR/Calc:_fonction_NB.JOURS.COUPONS|NB.JOURS.COUPONS]], [[Documentation/FR/Calc:_fonction_NB.JOURS.OUVRES|NB.JOURS.OUVRES]], [[Documentation/FR/Calc:_fonction_NB.MOIS|NB.MOIS]], [[Documentation/FR/Calc:_fonction_NB.SI|NB.SI]], [[Documentation/FR/Calc:_fonction_NB.VIDE|NB.VIDE]], [[Documentation/FR/Calc:_fonction_NB|NB]], [[Documentation/FR/Calc:_fonction_NBCAR|NBCAR]], [[Documentation/FR/Calc:_fonction_NBVAL|NBVAL]], [[Documentation/FR/Calc:_fonction_NO.SEMAINE_ADD|NO.SEMAINE_ADD]], [[Documentation/FR/Calc:_fonction_NO.SEMAINE|NO.SEMAINE]], [[Documentation/FR/Calc:_fonction_NOMPROPRE|NOMPROPRE]], [[Documentation/FR/Calc:_fonction_NON|NON]], [[Documentation/FR/Calc:_fonction_NPM|NPM]]

*[[Documentation/FR/Calc:_fonction_OCTBIN|OCTBIN]], [[Documentation/FR/Calc:_fonction_OCTDEC|OCTDEC]], [[Documentation/FR/Calc:_fonction_OCTHEX|OCTHEX]], [[Documentation/FR/Calc:_fonction_ORDONNEE.ORIGINE|ORDONNEE.ORIGINE]], [[Documentation/FR/Calc:_fonction_OU|OU]]

*[[Documentation/FR/Calc:_fonction_PAIR|PAIR]], [[Documentation/FR/Calc:_fonction_PEARSON|PEARSON]], [[Documentation/FR/Calc:_fonction_PENTE|PENTE]], [[Documentation/FR/Calc:_fonction_PERMUTATION|PERMUTATION]], [[Documentation/FR/Calc:_fonction_PERMUTATIONA|PERMUTATIONA]], [[Documentation/FR/Calc:_fonction_PETITE.VALEUR|PETITE.VALEUR]], [[Documentation/FR/Calc:_fonction_PGCD_ADD|PGCD_ADD]], [[Documentation/FR/Calc:_fonction_PGCD|PGCD]], [[Documentation/FR/Calc:_fonction_PHI|PHI]], [[Documentation/FR/Calc:_fonction_PI|PI]], [[Documentation/FR/Calc:_fonction_PLAFOND|PLAFOND]], [[Documentation/FR/Calc:_fonction_PLANCHER|PLANCHER]], [[Documentation/FR/Calc:_fonction_PPCM_ADD|PPCM_ADD]], [[Documentation/FR/Calc:_fonction_PPCM|PPCM]], [[Documentation/FR/Calc:_fonction_PREVISION|PREVISION]], [[Documentation/FR/Calc:_fonction_PRINCPER|PRINCPER]], [[Documentation/FR/Calc:_fonction_PRIX.BON.TRESOR|PRIX.BON.TRESOR]], [[Documentation/FR/Calc:_fonction_PRIX.DCOUPON.IRREG|PRIX.DCOUPON.IRREG]], [[Documentation/FR/Calc:_fonction_PRIX.DEC|PRIX.DEC]], [[Documentation/FR/Calc:_fonction_PRIX.FRAC|PRIX.FRAC]], [[Documentation/FR/Calc:_fonction_PRIX.PCOUPON.IRREG|PRIX.PCOUPON.IRREG]], [[Documentation/FR/Calc:_fonction_PRIX.TITRE.ECHANGE|PRIX.TITRE.ECHANGE]], [[Documentation/FR/Calc:_fonction_PRIX.TITRE|PRIX.TITRE]], [[Documentation/FR/Calc:_fonction_PROBABILITE|PROBABILITE]], [[Documentation/FR/Calc:_fonction_PRODUIT|PRODUIT]], [[Documentation/FR/Calc:_fonction_PRODUITMAT|PRODUITMAT]], [[Documentation/FR/Calc:_fonction_PUISSANCE|PUISSANCE]]

*[[Documentation/FR/Calc:_fonction_QUARTILE|QUARTILE]], [[Documentation/FR/Calc:_fonction_QUOTIENT|QUOTIENT]]

*[[Documentation/FR/Calc:_fonction_RACINE|RACINE]], [[Documentation/FR/Calc:_fonction_RACINEPI|RACINEPI]], [[Documentation/FR/Calc:_fonction_RADIANS|RADIANS]], [[Documentation/FR/Calc:_fonction_RANG.POURCENTAGE|RANG.POURCENTAGE]], [[Documentation/FR/Calc:_fonction_RANG|RANG]], [[Documentation/FR/Calc:_fonction_RECHERCHE|RECHERCHE]], [[Documentation/FR/Calc:_fonction_RECHERCHEH|RECHERCHEH]], [[Documentation/FR/Calc:_fonction_RECHERCHEV|RECHERCHEV]], [[Documentation/FR/Calc:_fonction_REMPLACER|REMPLACER]], [[Documentation/FR/Calc:_fonction_REND.DCOUPON.IRREG|REND.DCOUPON.IRREG]], [[Documentation/FR/Calc:_fonction_REND.PCOUPON.IRREG|REND.PCOUPON.IRREG]], [[Documentation/FR/Calc:_fonction_RENDEMENT.BON.TRESOR|RENDEMENT.BON.TRESOR]], [[Documentation/FR/Calc:_fonction_RENDEMENT.SIMPLE|RENDEMENT.SIMPLE]], [[Documentation/FR/Calc:_fonction_RENDEMENT.TITRE.ECHEANCE|RENDEMENT.TITRE.ECHEANCE]], [[Documentation/FR/Calc:_fonction_RENDEMENT.TITRE|RENDEMENT.TITRE]], [[Documentation/FR/Calc:_fonction_RENTINVEST|RENTINVEST]], [[Documentation/FR/Calc:_fonction_REPT|REPT]], [[Documentation/FR/Calc:_fonction_ROMAIN|ROMAIN]]

*[[Documentation/FR/Calc:_fonction_SECONDE|SECONDE]], [[Documentation/FR/Calc:_fonction_SEMAINES|SEMAINES]], [[Documentation/FR/Calc:_fonction_SEMAINESDANSANNEE|SEMAINESDANSANNEE]], [[Documentation/FR/Calc:_fonction_SERIE.JOUR.OUVRE|SERIE.JOUR.OUVRE]], [[Documentation/FR/Calc:_fonction_SI|SI]], [[Documentation/FR/Calc:_fonction_SIGNE|SIGNE]], [[Documentation/FR/Calc:_fonction_SIN|SIN]], [[Documentation/FR/Calc:_fonction_SINH|SINH]], [[Documentation/FR/Calc:_fonction_SOMME.CARRES.ECARTS|SOMME.CARRES.ECARTS]], [[Documentation/FR/Calc:_fonction_SOMME.CARRES|SOMME.CARRES]], [[Documentation/FR/Calc:_fonction_SOMME.SI|SOMME.SI]], [[Documentation/FR/Calc:_fonction_SOMME.X2MY2|SOMME.X2MY2]], [[Documentation/FR/Calc:_fonction_SOMME.X2PY2|SOMME.X2PY2]], [[Documentation/FR/Calc:_fonction_SOMME.XMY2|SOMME.XMY2]], [[Documentation/FR/Calc:_fonction_SOMME|SOMME]], [[Documentation/FR/Calc:_fonction_SOMMEPROD|SOMMEPROD]], [[Documentation/FR/Calc:_fonction_SOMMES.SERIES|SOMMES.SERIES]], [[Documentation/FR/Calc:_fonction_SOUS.TOTAL|SOUS.TOTAL]], [[Documentation/FR/Calc:_fonction_STXT|STXT]], [[Documentation/FR/Calc:_fonction_STYLE|STYLE]], [[Documentation/FR/Calc:_fonction_SUBSTITUE|SUBSTITUE]], [[Documentation/FR/Calc:_fonction_SUP.SEUIL|SUP.SEUIL]], [[Documentation/FR/Calc:_fonction_SUPRESPACE|SUPRESPACE]], [[Documentation/FR/Calc:_fonction_SYD|SYD]]

*[[Documentation/FR/Calc:_fonction_T|T]], [[Documentation/FR/Calc:_fonction_TAN|TAN]], [[Documentation/FR/Calc:_fonction_TANH|TANH]], [[Documentation/FR/Calc:_fonction_TAUX.EFFECTIF_ADD|TAUX.EFFECTIF_ADD]], [[Documentation/FR/Calc:_fonction_TAUX.EFFECTIF|TAUX.EFFECTIF]], [[Documentation/FR/Calc:_fonction_TAUX.ESCOMPTE.R|TAUX.ESCOMPTE.R]], [[Documentation/FR/Calc:_fonction_TAUX.ESCOMPTE|TAUX.ESCOMPTE]], [[Documentation/FR/Calc:_fonction_TAUX.INTERET|TAUX.INTERET]], [[Documentation/FR/Calc:_fonction_TAUX.NOMINAL_ADD|TAUX.NOMINAL_ADD]], [[Documentation/FR/Calc:_fonction_TAUX.NOMINAL|TAUX.NOMINAL]], [[Documentation/FR/Calc:_fonction_TAUX|TAUX]], [[Documentation/FR/Calc:_fonction_TEMPS|TEMPS]], [[Documentation/FR/Calc:_fonction_TEMPSVAL|TEMPSVAL]], [[Documentation/FR/Calc:_fonction_TENDANCE|TENDANCE]], [[Documentation/FR/Calc:_fonction_TEST.F|TEST.F]], [[Documentation/FR/Calc:_fonction_TEST.KHIDEUX|TEST.KHIDEUX]], [[Documentation/FR/Calc:_fonction_TEST.STUDENT|TEST.STUDENT]], [[Documentation/FR/Calc:_fonction_TEST.Z|TEST.Z]], [[Documentation/FR/Calc:_fonction_TEXTE|TEXTE]], [[Documentation/FR/Calc:_fonction_TRANSPOSE|TRANSPOSE]], [[Documentation/FR/Calc:_fonction_TRI.PAIEMENTS|TRI.PAIEMENTS]], [[Documentation/FR/Calc:_fonction_TRI|TRI]], [[Documentation/FR/Calc:_fonction_TRIM|TRIM]], [[Documentation/FR/Calc:_fonction_TRONQUE|TRONQUE]], [[Documentation/FR/Calc:_fonction_TROUVE|TROUVE]], [[Documentation/FR/Calc:_fonction_TYPE.ERREUR|TYPE.ERREUR]], [[Documentation/FR/Calc:_fonction_TYPE|TYPE]]

*[[Documentation/FR/Calc:_fonction_VA|VA]], [[Documentation/FR/Calc:_fonction_VALEUR.ENCAISSEMENT|VALEUR.ENCAISSEMENT]], [[Documentation/FR/Calc:_fonction_VALEUR.NOMINALE|VALEUR.NOMINALE]], [[Documentation/FR/Calc:_fonction_VAN.PAIEMENTS|VAN.PAIEMENTS]], [[Documentation/FR/Calc:_fonction_VAN|VAN]], [[Documentation/FR/Calc:_fonction_VAR.P|VAR.P]], [[Documentation/FR/Calc:_fonction_VAR.PA|VAR.PA]], [[Documentation/FR/Calc:_fonction_VAR|VAR]], [[Documentation/FR/Calc:_fonction_VARA|VARA]], [[Documentation/FR/Calc:_fonction_VC.PAIEMENTS|VC.PAIEMENTS]], [[Documentation/FR/Calc:_fonction_VC|VC]], [[Documentation/FR/Calc:_fonction_VDB|VDB]], [[Documentation/FR/Calc:_fonction_VPM|VPM]], [[Documentation/FR/Calc:_fonction_VRAI|VRAI]]

*[[Documentation/FR/Calc:_fonction_ZONES|ZONES]]

==Voir également==

'''[[Documentation/FR/Calc/Fonctions_listees_par_categories|Fonctions listées par catégories]]'''

[[Category:Documentation]]

FR/Documentation/Calc: fonction CELLULE

2008-10-13T19:15:45Z

Drking: /* Issues: */

__NOTOC__

== CELLULE ==
Renvoie des informations au sujet de l'adresse, du formatage ou du contenu de la cellule

=== Syntaxe : ===
<tt>'''CELLULE(type_info; ref_cell)'''</tt>

: renvoie des informations à propos de la cellule <tt>'''ref_cell'''</tt>. Les informations renvoyées dépendent de la chaîne de texte (insensible à la casse) <tt>'''type_info'''</tt>, qui peut être défini de la façon suivante :

: '''COL'''
:: <tt>'''CELLULE()'''</tt> renvoie le numéro de la colonne référencée. Exemple :

:: <tt>'''CELLULE("COL"; D2)'''</tt> renvoie <tt>'''4'''</tt>, puisque D est la quatrième colonne.

: '''ROW'''
:: <tt>'''CELLULE()'''</tt> renvoie le numéro de la ligne référencée. Exemple :

:: <tt>'''CELLULE("ROW"; D2)'''</tt> renvoie <tt>'''2'''</tt>.

: '''SHEET'''
:: <tt>'''CELLULE()'''</tt> renvoi le numéro de la feuille référencée. Exemple :
:: <tt>'''CELLULE("SHEET"; Feuille3.D2)'''</tt> renvoie <tt>'''3'''</tt> supposant que <tt>'''Feuille3'''</tt> est la troisième feuille.

: '''ADDRESS'''
:: <tt>'''CELLULE()'''</tt> renvoi l'adresse absolue de la référence de la cellule, comme texte. Exemples :

:: <tt>'''CELLULE("ADDRESS"; D2)'''</tt> renvoie <tt>'''$D$2'''</tt>.
:: <tt>'''CELLULE("ADDRESS"; Feuille3.D2)'''</tt> renvoie <tt>'''$Feuille3.$D$2'''</tt>.
:: <tt>'''CELLULE("ADDRESS;'X:\dr\test.ods'#$Feuille1.D2)'''</tt> renvoie <tt>''''file:///X:/dr/test.ods'#$Feuille1.$D$2'''</tt>.

: '''FILENAME'''
:: <tt>'''CELLULE()'''</tt> renvoie le nom de fichier et le numéro de la feuille de la cellule référencée, comme texte. Exemples :
:: <tt>'''CELLULE("FILENAME";D2)'''</tt> renvoie <tt>''''file:///X:/dr/monfichier.ods'#$Feuille1'''</tt>, si le document actif est <tt>'''X:\dr\monfichier.ods'''</tt> et que la formule sur la Feuille1.
:: <tt>'''CELLULE("FILENAME";'X:\dr\monfichier.ods'#$Sheet1.D2)'''</tt> renvoie <tt>''''file:///X:/dr/monfichier.ods'#$Feuille1'''</tt>.

: '''COORD'''
:: <tt>'''CELLULE()'''</tt> renvoie l'adresse complète de la cellule en notation Lotus(TM), comme texte. Exemples :
:: <tt>'''CELLULE("COORD"; D2)'''</tt> renvoie <tt>'''$A:$D$2'''</tt>.
:: <tt>'''CELLULE("COORD"; Feuille3.D2)'''</tt> renvoie <tt>'''$C:$D$2'''</tt>.

: '''CONTENTS'''
:: <tt>'''CELLULE()'''</tt> renvoie le contenu de la cellule référencée, sans aucun formatage. Exemple :
:: <tt>'''CELLULE("CONTENTS"; D2)'''</tt> renvoie le contenu de la cellule D2 (ou <tt>'''0'''</tt> si D2 est vide).

: '''TYPE'''
:: <tt>'''CELLULE()'''</tt> renvoie un texte qui indique le type de contenu de la cellule :
::: <tt>'''b'''</tt> (blank) signifie une cellule vide
::: <tt>'''l'''</tt> (label) signifie du texte ou le résultat d'une formule comme texte
::: <tt>'''v'''</tt> (value) signifie un nombre ou le résultat d'une fromule comme nombre

:'''WIDTH'''
:: <tt>'''CELLULE()'''</tt> renvoie la largeur de la colonne référencée. L'unité est le nombre de zéros (0) qui rentre dans la colonne avec le texte et la taille par défaut.

:'''PREFIX'''
:: <tt>'''CELLULE()'''</tt> renvoie un texte qui indique l'alignement de la cellule référencée, comme :
::: <tt>'</tt> = aligné à gauche ou justifié à gauche
::: <tt>'''"'''</tt> = aligné à droite
::: <tt>'''^'''</tt> = centré
::: <tt>'''\'''</tt> = répéter (actuellement inactif)

: '''PROTECT'''
:: <tt>'''CELLULE()'''</tt> renvoie le statut de la protection de cellule pour la cellule.
::: <tt>'''1'''</tt> = la cellule est protégée
::: <tt>'''0'''</tt> = la cellule n'est pas protégée

: '''FORMAT'''
:: <tt>'''CELLULE()'''</tt> renvoie le texte qui indique le format de nombre :
::: <tt>''','''</tt> = nombre avec séparateur de milliers
::: <tt>'''F'''</tt> = nombre sans séparateur de milliers
::: <tt>'''C'''</tt> = format monétaire
::: <tt>'''S'''</tt> = représentation exponentielle, par exemple <tt>'''1.234+E56'''</tt>
::: <tt>'''P'''</tt> = pourcentage
::: Dans les formats ci-dessus, le nombre de décimales après le séparateur est donné comme un numéro. Exemple : le format de nombre <tt>'''#,##0.0'''</tt> renvoie <tt>''',1'''</tt> et le format de nombre <tt>'''00.000%'''</tt> renvoie <tt>'''P3'''</tt>
::: <tt>'''D1'''</tt> = MMM-J-AA, MM-J-AA et les formats similaires
::: <tt>'''D2'''</tt> = JJ-MM
::: <tt>'''D3'''</tt> = MM-AA
::: <tt>'''D4'''</tt> = JJ-MM-AAAA HH:MM:SS
::: <tt>'''D5'''</tt> = MM-JJ
::: <tt>'''D6'''</tt> = HH:MM:SS AM/PM
::: <tt>'''D7'''</tt> = HH:MM AM/PM
::: <tt>'''D8'''</tt> = HH:MM:SS
::: <tt>'''D9'''</tt> = HH:MM
::: <tt>'''G'''</tt> = tous les autres formats
::: <tt>'''-'''</tt> (moins) à la fin = les nombres négatifs sont formatés en couleur
::: <tt>'''()'''</tt> (parenthèses) à la fin = il y a une parenthèse ouvrante dans le code du format

: '''COLOR'''
:: <tt>'''CELLULE()'''</tt> renvoie <tt>'''1'''</tt>, si les valeurs négatives ont été formatées en couleur, sinon <tt>'''0'''</tt>.

: '''PARENTHESES'''
:: <tt>'''CELLULE()'''</tt> renvoie <tt>'''1'''</tt> si le code du format contient une parenthèse ouvrante (, sinon <tt>'''0'''</tt>.

=== Issues: ===
* <tt>'''CELLULE()'''</tt> renvoie des informations capturées lors de l'actualisation de la cellule cible. Par exemple <tt>'''CELLULE("WIDTH"; D2)'''</tt> renvoie la largeur de la colonne D - mais si la colonne D est rétrécie par la suite, la valeur renvoyée ne sera pas actualisée jusqu'à ce que le contenu de la colonne D soit modifié. Pour actualisation, utilisez (par exemple) <tt>'''CELLULE("WIDTH"; D2) + 0*ALEA()'''</tt>; parce que <tt>'''ALEA()'''</tt> est actualisée quand la largeur de la colonne change.

=== Voir également : ===
'''[[Documentation/Fr/Calc:_fonction_TYPE|Fonction TYPE]]'''

'''[[Documentation/FR/Calc:_Fonctions_information|Fonctions d'information]]'''

FR/Documentation/Calc: fonction CELLULE

2008-10-13T19:06:17Z

Drking: /* Issues: */

__NOTOC__

== CELLULE ==
Renvoie des informations au sujet de l'adresse, du formatage ou du contenu de la cellule

=== Syntaxe : ===
<tt>'''CELLULE(type_info; ref_cell)'''</tt>

: renvoie des informations à propos de la cellule <tt>'''ref_cell'''</tt>. Les informations renvoyées dépendent de la chaîne de texte (insensible à la casse) <tt>'''type_info'''</tt>, qui peut être défini de la façon suivante :

: '''COL'''
:: <tt>'''CELLULE()'''</tt> renvoie le numéro de la colonne référencée. Exemple :

:: <tt>'''CELLULE("COL"; D2)'''</tt> renvoie <tt>'''4'''</tt>, puisque D est la quatrième colonne.

: '''ROW'''
:: <tt>'''CELLULE()'''</tt> renvoie le numéro de la ligne référencée. Exemple :

:: <tt>'''CELLULE("ROW"; D2)'''</tt> renvoie <tt>'''2'''</tt>.

: '''SHEET'''
:: <tt>'''CELLULE()'''</tt> renvoi le numéro de la feuille référencée. Exemple :
:: <tt>'''CELLULE("SHEET"; Feuille3.D2)'''</tt> renvoie <tt>'''3'''</tt> supposant que <tt>'''Feuille3'''</tt> est la troisième feuille.

: '''ADDRESS'''
:: <tt>'''CELLULE()'''</tt> renvoi l'adresse absolue de la référence de la cellule, comme texte. Exemples :

:: <tt>'''CELLULE("ADDRESS"; D2)'''</tt> renvoie <tt>'''$D$2'''</tt>.
:: <tt>'''CELLULE("ADDRESS"; Feuille3.D2)'''</tt> renvoie <tt>'''$Feuille3.$D$2'''</tt>.
:: <tt>'''CELLULE("ADDRESS;'X:\dr\test.ods'#$Feuille1.D2)'''</tt> renvoie <tt>''''file:///X:/dr/test.ods'#$Feuille1.$D$2'''</tt>.

: '''FILENAME'''
:: <tt>'''CELLULE()'''</tt> renvoie le nom de fichier et le numéro de la feuille de la cellule référencée, comme texte. Exemples :
:: <tt>'''CELLULE("FILENAME";D2)'''</tt> renvoie <tt>''''file:///X:/dr/monfichier.ods'#$Feuille1'''</tt>, si le document actif est <tt>'''X:\dr\monfichier.ods'''</tt> et que la formule sur la Feuille1.
:: <tt>'''CELLULE("FILENAME";'X:\dr\monfichier.ods'#$Sheet1.D2)'''</tt> renvoie <tt>''''file:///X:/dr/monfichier.ods'#$Feuille1'''</tt>.

: '''COORD'''
:: <tt>'''CELLULE()'''</tt> renvoie l'adresse complète de la cellule en notation Lotus(TM), comme texte. Exemples :
:: <tt>'''CELLULE("COORD"; D2)'''</tt> renvoie <tt>'''$A:$D$2'''</tt>.
:: <tt>'''CELLULE("COORD"; Feuille3.D2)'''</tt> renvoie <tt>'''$C:$D$2'''</tt>.

: '''CONTENTS'''
:: <tt>'''CELLULE()'''</tt> renvoie le contenu de la cellule référencée, sans aucun formatage. Exemple :
:: <tt>'''CELLULE("CONTENTS"; D2)'''</tt> renvoie le contenu de la cellule D2 (ou <tt>'''0'''</tt> si D2 est vide).

: '''TYPE'''
:: <tt>'''CELLULE()'''</tt> renvoie un texte qui indique le type de contenu de la cellule :
::: <tt>'''b'''</tt> (blank) signifie une cellule vide
::: <tt>'''l'''</tt> (label) signifie du texte ou le résultat d'une formule comme texte
::: <tt>'''v'''</tt> (value) signifie un nombre ou le résultat d'une fromule comme nombre

:'''WIDTH'''
:: <tt>'''CELLULE()'''</tt> renvoie la largeur de la colonne référencée. L'unité est le nombre de zéros (0) qui rentre dans la colonne avec le texte et la taille par défaut.

:'''PREFIX'''
:: <tt>'''CELLULE()'''</tt> renvoie un texte qui indique l'alignement de la cellule référencée, comme :
::: <tt>'</tt> = aligné à gauche ou justifié à gauche
::: <tt>'''"'''</tt> = aligné à droite
::: <tt>'''^'''</tt> = centré
::: <tt>'''\'''</tt> = répéter (actuellement inactif)

: '''PROTECT'''
:: <tt>'''CELLULE()'''</tt> renvoie le statut de la protection de cellule pour la cellule.
::: <tt>'''1'''</tt> = la cellule est protégée
::: <tt>'''0'''</tt> = la cellule n'est pas protégée

: '''FORMAT'''
:: <tt>'''CELLULE()'''</tt> renvoie le texte qui indique le format de nombre :
::: <tt>''','''</tt> = nombre avec séparateur de milliers
::: <tt>'''F'''</tt> = nombre sans séparateur de milliers
::: <tt>'''C'''</tt> = format monétaire
::: <tt>'''S'''</tt> = représentation exponentielle, par exemple <tt>'''1.234+E56'''</tt>
::: <tt>'''P'''</tt> = pourcentage
::: Dans les formats ci-dessus, le nombre de décimales après le séparateur est donné comme un numéro. Exemple : le format de nombre <tt>'''#,##0.0'''</tt> renvoie <tt>''',1'''</tt> et le format de nombre <tt>'''00.000%'''</tt> renvoie <tt>'''P3'''</tt>
::: <tt>'''D1'''</tt> = MMM-J-AA, MM-J-AA et les formats similaires
::: <tt>'''D2'''</tt> = JJ-MM
::: <tt>'''D3'''</tt> = MM-AA
::: <tt>'''D4'''</tt> = JJ-MM-AAAA HH:MM:SS
::: <tt>'''D5'''</tt> = MM-JJ
::: <tt>'''D6'''</tt> = HH:MM:SS AM/PM
::: <tt>'''D7'''</tt> = HH:MM AM/PM
::: <tt>'''D8'''</tt> = HH:MM:SS
::: <tt>'''D9'''</tt> = HH:MM
::: <tt>'''G'''</tt> = tous les autres formats
::: <tt>'''-'''</tt> (moins) à la fin = les nombres négatifs sont formatés en couleur
::: <tt>'''()'''</tt> (parenthèses) à la fin = il y a une parenthèse ouvrante dans le code du format

: '''COLOR'''
:: <tt>'''CELLULE()'''</tt> renvoie <tt>'''1'''</tt>, si les valeurs négatives ont été formatées en couleur, sinon <tt>'''0'''</tt>.

: '''PARENTHESES'''
:: <tt>'''CELLULE()'''</tt> renvoie <tt>'''1'''</tt> si le code du format contient une parenthèse ouvrante (, sinon <tt>'''0'''</tt>.

=== Issues: ===
* <tt>'''CELLULE()'''</tt> renvoie des informations capturées lors de l'actualisation de la cellule cible. Par exemple <tt>'''CELLULE("WIDTH"; D2)'''</tt> renvoie la largeur de la colonne D - mais si la colonne D est rétrécie par la suite, la valeur renvoyée ne sera pas actualisée jusqu'à ce que le contenu de la colonne D soit modifié.

=== Voir également : ===
'''[[Documentation/Fr/Calc:_fonction_TYPE|Fonction TYPE]]'''

'''[[Documentation/FR/Calc:_Fonctions_information|Fonctions d'information]]'''

Documentation/How Tos/Calc: CELL function

2008-10-13T19:04:56Z

Drking: /* Issues: */

__NOTOC__

== CELL ==
Returns information about the address, formatting or contents of a cell.

=== Syntax: ===
<tt>'''CELL(info_type; cell_ref)'''</tt>

: returns information about the cell <tt>'''cell_ref'''</tt>. The information returned depends on the (case insensitive) text string <tt>'''info_type'''</tt>, which can be set as follows:

: '''COL'''
:: <tt>'''CELL()'''</tt> returns the number of the referenced column. Example:

:: <tt>'''CELL("COL"; D2)'''</tt> returns <tt>'''4'''</tt>, as D is the fourth column.

: '''ROW'''
:: <tt>'''CELL()'''</tt> returns the number of the referenced row. Example:

:: <tt>'''CELL("ROW"; D2)'''</tt> returns <tt>'''2'''</tt>.

: '''SHEET'''
:: <tt>'''CELL()'''</tt> returns the number of the referenced sheet. Example:
:: <tt>'''CELL("SHEET"; Sheet3.D2)'''</tt> returns <tt>'''3'''</tt> assuming <tt>'''Sheet3'''</tt> is the third sheet.

: '''ADDRESS'''
:: <tt>'''CELL()'''</tt> returns the absolute address of the referenced cell, as text. Examples:

:: <tt>'''CELL("ADDRESS"; D2)'''</tt> returns <tt>'''$D$2'''</tt>.
:: <tt>'''CELL("ADDRESS"; Sheet3.D2)'''</tt> returns <tt>'''$Sheet3.$D$2'''</tt>.
:: <tt>'''CELL("ADDRESS";'X:\dr\test.ods'#$Sheet1.D2)'''</tt> returns <tt>''''file:///X:/dr/test.ods'#$Sheet1.$D$2'''</tt>.

: '''FILENAME'''
:: <tt>'''CELL()'''</tt> returns the file name and the sheet number of the referenced cell, as text. Examples:
:: <tt>'''CELL("FILENAME";D2)'''</tt> returns <tt>''''file:///X:/dr/myfile.ods'#$Sheet1'''</tt>, if the current document is <tt>'''X:\dr\myfile.ods'''</tt> and the formula is on Sheet1.
:: <tt>'''CELL("FILENAME";'X:\dr\myfile.ods'#$Sheet1.D2)'''</tt> returns <tt>''''file:///X:/dr/myfile.ods'#$Sheet1'''</tt>.

: '''COORD'''
:: <tt>'''CELL()'''</tt> returns the complete cell address in Lotus(TM) notation, as text. Examples:
:: <tt>'''CELL("COORD"; D2)'''</tt> returns <tt>'''$A:$D$2'''</tt>.
:: <tt>'''CELL("COORD"; Sheet3.D2)'''</tt> returns <tt>'''$C:$D$2'''</tt>.

: '''CONTENTS'''
:: <tt>'''CELL()'''</tt> returns the contents of the referenced cell, without any formatting. Example:
:: <tt>'''CELL("CONTENTS"; D2)'''</tt> returns the contents of cell D2 (or <tt>'''0'''</tt> if D2 is empty).

: '''TYPE'''
:: <tt>'''CELL()'''</tt> returns text that indicates the type of cell contents:
::: <tt>'''b'''</tt> (blank) signifies an empty cell
::: <tt>'''l'''</tt> (label) signifies text, or the result of a formula as text
::: <tt>'''v'''</tt> (value) signifies a number, or the result of a formula as a number

:'''WIDTH'''
:: <tt>'''CELL()'''</tt> returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.

:'''PREFIX'''
:: <tt>'''CELL()'''</tt> returns text that indicates the alignment of the referenced cell, as:
::: <tt>'</tt> = align left or left-justified
::: <tt>'''"'''</tt> = align right
::: <tt>'''^'''</tt> = centered
::: <tt>'''\'''</tt> = repeating (currently inactive)

: '''PROTECT'''
:: <tt>'''CELL()'''</tt> returns the status of the cell protection for the cell.
::: <tt>'''1'''</tt> = cell is protected
::: <tt>'''0'''</tt> = cell is not protected

: '''FORMAT'''
:: <tt>'''CELL()'''</tt> returns text that indicates the number format:
::: <tt>''','''</tt> = number with thousands separator
::: <tt>'''F'''</tt> = number without thousands separator
::: <tt>'''C'''</tt> = currency format
::: <tt>'''S'''</tt> = exponential representation, for example <tt>'''1.234+E56'''</tt>
::: <tt>'''P'''</tt> = percentage
::: In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format <tt>'''#,##0.0'''</tt> returns <tt>''',1'''</tt> and the number format <tt>'''00.000%'''</tt> returns <tt>'''P3'''</tt>
::: <tt>'''D1'''</tt> = MMM-D-YY, MM-D-YY and similar formats
::: <tt>'''D2'''</tt> = DD-MM
::: <tt>'''D3'''</tt> = MM-YY
::: <tt>'''D4'''</tt> = DD-MM-YYYY HH:MM:SS
::: <tt>'''D5'''</tt> = MM-DD
::: <tt>'''D6'''</tt> = HH:MM:SS AM/PM
::: <tt>'''D7'''</tt> = HH:MM AM/PM
::: <tt>'''D8'''</tt> = HH:MM:SS
::: <tt>'''D9'''</tt> = HH:MM
::: <tt>'''G'''</tt> = All other formats
::: <tt>'''-'''</tt> (Minus) at the end = negative numbers are formatted in color
::: <tt>'''()'''</tt> (brackets) at the end = there is an opening bracket in the format code

: '''COLOR'''
:: <tt>'''CELL()'''</tt> returns <tt>'''1'''</tt>, if negative values have been formatted in color, otherwise <tt>'''0'''</tt>.

: '''PARENTHESES'''
:: <tt>'''CELL()'''</tt> returns <tt>'''1'''</tt> if the format code contains an opening bracket (, otherwise <tt>'''0'''</tt>.

=== Issues: ===
* <tt>'''CELL()'''</tt> returns information captured when the target cell was last updated. For example <tt>'''CELL("WIDTH"; D2)'''</tt> returns the width of column D - but if column D is then made wider, the value returned will not update until the contents of D2 are changed. To ensure an update, use (for example) <tt>'''CELL("WIDTH"; D2) + 0*RAND()'''</tt>; this works because <tt>'''RAND()'''</tt> updates when the column width is changed.

=== See also: ===
'''[[Documentation/How_Tos/Calc: TYPE function|TYPE]]'''

'''[[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''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-10T04:51:32Z

Drking: /* YIELD */

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:''redemptionvalue'' - (''redemptionvalue'' * ''discountrate'' * ''days_to_maturity'' / ''days_in_year'').

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:''present_value_of_redemptionvalue_and_interest'' - ''accrued_interest''

Values are per 100, so the redemption capital value is simply 100.

The interest paid at redemption is 100 * ''rate'' * ''days_in_issue'' / ''days_in_year''.
The fraction ''days_in_issue'' / ''days_in_year'' is calculated using <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt> in Calc. Excel does not specify its method of calculation.

The sum paid at redemption is therefore:
:100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>

The present value of that sum is calculated as:
:(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>).

This takes no account of compounding. For example the present value of $150 received 5 years hence using a yield of 10% is calculated as 150/(1+0.1*5) = $100; with compounding that would be 150/(1+0.1)^5 = $93.14 approximately. It is important to understand this.

The accrued interest is calculated as 100 * ''rate'' * ''days_before_settlement'' / ''days_in_year'', that is:
:100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

Note that there are known issues with <tt>'''YEARFRAC'''</tt> (the length of a year in basis 1; basis 0 date adjustments) that affect both Excel and Calc.

This ''may'' be a standard method of calculation in the US. Its validity elsewhere is uncertain. The entire formula is :
:[(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>)] - 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

=== YIELD ===

We saw above that <tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest
which is:
:<math> \left (\sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}} \right ) + \left (\frac {redemptionvalue} {(1+p)^{periodstogo}} \right ) - \left (100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod} \right)</math>
where ''p'' is the yield per coupon period (= yield/frequency).

There is no general way to re-arrange this equation to solve for yield. Thus a starting guess for yield is chosen, and then repeatedly adjusted to bring the calculated value of PRICE close to the value specified as a parameter in the YIELD function.

=== YIELDDISC ===

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-10T04:45:04Z

Drking: YIELD

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:''redemptionvalue'' - (''redemptionvalue'' * ''discountrate'' * ''days_to_maturity'' / ''days_in_year'').

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:''present_value_of_redemptionvalue_and_interest'' - ''accrued_interest''

Values are per 100, so the redemption capital value is simply 100.

The interest paid at redemption is 100 * ''rate'' * ''days_in_issue'' / ''days_in_year''.
The fraction ''days_in_issue'' / ''days_in_year'' is calculated using <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt> in Calc. Excel does not specify its method of calculation.

The sum paid at redemption is therefore:
:100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>

The present value of that sum is calculated as:
:(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>).

This takes no account of compounding. For example the present value of $150 received 5 years hence using a yield of 10% is calculated as 150/(1+0.1*5) = $100; with compounding that would be 150/(1+0.1)^5 = $93.14 approximately. It is important to understand this.

The accrued interest is calculated as 100 * ''rate'' * ''days_before_settlement'' / ''days_in_year'', that is:
:100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

Note that there are known issues with <tt>'''YEARFRAC'''</tt> (the length of a year in basis 1; basis 0 date adjustments) that affect both Excel and Calc.

This ''may'' be a standard method of calculation in the US. Its validity elsewhere is uncertain. The entire formula is :
:[(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>)] - 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

=== YIELD ===

We saw above that <tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest
which is:
:<math> (\sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}) + (\frac {redemptionvalue} {(1+p)^{periodstogo}}) - (100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod})</math>
where ''p'' is the yield per coupon period (= yield/frequency).

There is no general way to re-arrange this equation to solve for yield. Thus a starting guess for yield is chosen, and then repeatedly adjusted to bring the calculated value of PRICE close to the value specified as a parameter in the YIELD function.

=== YIELDDISC ===

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-07T07:36:52Z

Drking: /* PRICEDISC */

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:''redemptionvalue'' - (''redemptionvalue'' * ''discountrate'' * ''days_to_maturity'' / ''days_in_year'').

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:''present_value_of_redemptionvalue_and_interest'' - ''accrued_interest''

Values are per 100, so the redemption capital value is simply 100.

The interest paid at redemption is 100 * ''rate'' * ''days_in_issue'' / ''days_in_year''.
The fraction ''days_in_issue'' / ''days_in_year'' is calculated using <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt> in Calc. Excel does not specify its method of calculation.

The sum paid at redemption is therefore:
:100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>

The present value of that sum is calculated as:
:(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>).

This takes no account of compounding. For example the present value of $150 received 5 years hence using a yield of 10% is calculated as 150/(1+0.1*5) = $100; with compounding that would be 150/(1+0.1)^5 = $93.14 approximately. It is important to understand this.

The accrued interest is calculated as 100 * ''rate'' * ''days_before_settlement'' / ''days_in_year'', that is:
:100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

Note that there are known issues with <tt>'''YEARFRAC'''</tt> (the length of a year in basis 1; basis 0 date adjustments) that affect both Excel and Calc.

This ''may'' be a standard method of calculation in the US. Its validity elsewhere is uncertain. The entire formula is :
:[(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>)] - 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-07T07:36:01Z

Drking: /* PRICEMAT */

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:redemptionvalue - (redemptionvalue * discountrate * days_to_maturity / days_in_year).

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:''present_value_of_redemptionvalue_and_interest'' - ''accrued_interest''

Values are per 100, so the redemption capital value is simply 100.

The interest paid at redemption is 100 * ''rate'' * ''days_in_issue'' / ''days_in_year''.
The fraction ''days_in_issue'' / ''days_in_year'' is calculated using <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt> in Calc. Excel does not specify its method of calculation.

The sum paid at redemption is therefore:
:100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>

The present value of that sum is calculated as:
:(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>).

This takes no account of compounding. For example the present value of $150 received 5 years hence using a yield of 10% is calculated as 150/(1+0.1*5) = $100; with compounding that would be 150/(1+0.1)^5 = $93.14 approximately. It is important to understand this.

The accrued interest is calculated as 100 * ''rate'' * ''days_before_settlement'' / ''days_in_year'', that is:
:100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

Note that there are known issues with <tt>'''YEARFRAC'''</tt> (the length of a year in basis 1; basis 0 date adjustments) that affect both Excel and Calc.

This ''may'' be a standard method of calculation in the US. Its validity elsewhere is uncertain. The entire formula is :
:[(100 + 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + ''yield'' * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>)] - 100 * ''rate'' * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-07T07:34:01Z

Drking: /* PRICEMAT */

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:redemptionvalue - (redemptionvalue * discountrate * days_to_maturity / days_in_year).

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:present_value_of_redemptionvalue_and_interest - accrued_interest

Values are per 100, so the redemption capital value is simply 100.

The interest paid at redemption is 100 * rate * days_in_issue / days_in_year.
The fraction days_in_issue / days_in_year is calculated using <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt> in Calc. Excel does not specify its method of calculation.

The sum paid at redemption is therefore:
:100 + 100 * rate * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>

The present value of that sum is calculated as:
:(100 + 100 * rate * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + yield * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>).

This takes no account of compounding. For example the present value of $150 received 5 years hence using a yield of 10% is calculated as 150/(1+0.1*5) = $100; with compounding that would be 150/(1+0.1)^5 = $93.14 approximately. It is important to understand this.

The accrued interest is calculated as 100 * rate * days_before_settlement / days_in_year, that is:
:100 * rate * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

Note that there are known issues with <tt>'''YEARFRAC'''</tt> (the length of a year in basis 1; basis 0 date adjustments) that affect both Excel and Calc.

This ''may'' be a standard method of calculation in the US. Its validity elsewhere is uncertain. The entire formula is :
:[(100 + 100 * rate * <tt>'''YEARFRAC(issuedate; maturitydate; basis)'''</tt>) / (1 + yield * <tt>'''YEARFRAC(settlementdate; maturitydate; basis)'''</tt>)] - 100 * rate * <tt>'''YEARFRAC(issuedate; settlementdate; basis)'''</tt>).

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: Derivation of Financial Formulas

2008-10-06T07:39:20Z

Drking: /* PRICEMAT */

== Derivation of Financial Formulas ==
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.

Most users will of course '''not''' wish to investigate further, and can ignore this page.

'''!! This page is currently in development !!'''

=== DB ===

This is a practical method. If the first year does not have 12 months, the total depreciation calculated is not exact; the method was originally useful because it is fairly easy to work out manually.

We wish to find a constant rate r, which when applied to the asset’s book value each year will give depreciation which reduces the asset’s value from c, the original cost, to s, the salvage value. We will assume the first year has 12 months, and the depreciation period (lifetime) is t years.

In the first year

Starting book value = c

Depreciation = cr

Remaining book value = c(1 - r)

In the second year

Starting book value = c(1 - r)

Depreciation = c(1 - r)r

Remaining book value = c(1 - r)(1 - r)

=c(1 - r)2

In the tth year

Final remaining book value = c(1 - r)t

So

s = c(1 - r)t

(s/c)1/t = (1 - r)

r = 1 - (s/c)1/t

The method is exact with whole years, but will slightly underestimate total depreciation if the first year has less than 12 months.

=== DDB ===

As with <tt>'''DB'''</tt>, the book value at the end of life is:

c(1 - r)t

or the salvage value s if that is greater, where by definition:
r = f/t (<tt>'''factor'''</tt> f and <tt>'''lifetime'''</tt> t).

The circumstances in which <tt>'''DDB'''</tt> will not depreciate to the salvage value are therefore:

s < c(1 - r)t

=>

s / c < (1 - f/t)t

=== SYD ===

With a depreciation period (lifetime) of t years, we'll find the depreciation in the yth year:

Depreciation reduces the asset’s value from c, the original cost, to s, the salvage value, so the loss of value is c - s.

The sum of the years' digits is 1 + 2 + ... t = t(t+1)/2 (a standard result).

The years taken backward are t, t-1, .... 2, 1, which is (t+1 - y).

The depreciation in year y is therefore

(c - s) * (t+1 - y) / [ t(t+1)/2 ]

= (c - s) * (t+1 - y) * 2/ [t(t+1)]

=== Time Value of Money ===

Present and future values

We can invest $100 at say 5% annual interest, and in a year's time it is worth $105. Thus money in the hand today is worth more than the same money given to us later, because if we have money today, we can invest it and get interest on it.

The value of an investment today is called the 'present value'. The value of an investment at some time in the future is called the 'future value'. In this simple example the present value is $100 and the future value $105, if you assume an interest rate of 5%.

The choice of interest rate when calculating present and future values is important. It is often a choice that you make - and gives a present or future value to you, based on that rate. The rate is sometimes called the 'required rate of return'.

We will calculate a future value ''f'' of a sum ''p'' that we have today. Let us say there is an alternative investment with a rate ''r'' which compounds annually (that is, at the end of each year the interest is calculated and added to the investment), and we choose to use this rate.

At first our investment is worth ''p''.

At the end of the 1st year it is worth ''p'' plus a year's interest

= p + pr = p(1 + r).

At the end of the 2nd year it is worth p(1 + r) plus a year's interest

= p(1 + r) + p(1 + r)r = p(1 + r)2.

...

At the end of the nth year it is worth p(1 + r)n.

So the future value after ''n'' years is:

f = p(1 + r)n.

The present value is therefore:

p = f / (1 + r)n.

We have considered annually compounded interest, but this formula applies to any period (months, quarters, etc) where the interest rate (r per period) is compounded each period.

Make sure that you understand the difference between compounded and un-compounded interest. A rate of 1% per month uncompounded on an investment of $100 simply gives $1 per month - $12 interest in a year. A rate of 1% per month, compounded monthly, gives 100*(1*0.01)12-100 = $12.68 in the first year, and more in the second year.

Also note that this model assumes that the rate stays the same.

=== EFFECTIVE; EFFECT_ADD; (EFFECT); NOMINAL; NOMINAL_ADD ===

An investment is currently worth p, and pays interest at a rate of r each period, compounded.

From above, at the end of the nth period, the value of the investment = p(1 + r)n.

If the investment has a nominal rate x, say per year, and pays n times during each year, the interest rate paid each period ( 1/n th of a year ) is r = x/n.

As the interest compounds each period, the value of the investment at the end of the (say) year will be:

p(1 + r)n = p(1 + x/n)n.

We're looking for an effective rate e that would increase the value to the same amount as a result of one single interest payment.

p(1 + e) = p(1 + x/n)n

therefore

e = (1 + x/n)n - 1

and

x = n((1 + e)1/n - 1)

=== Loans and Annuities ===

We will use a general model to describe both loans and annuities, where we borrow or invest a sum, we make or receive a number of regular payments, and at the end we pay or receive a final sum. Sometimes only two of these sums are needed - for example with a loan we might borrow the initial sum and then make monthly payments (there being no final sum). We define:

p = present value (the sum borrowed or invested)

f = future value (the sum to pay or be paid after n periods)

m = payment each period (does not change)

n = number of periods (a period may be a month, a year or whatever)

r = interest rate (fixed rate) per period (whatever length that is)

In the model, interest is calculated and applied to the balance each period.

We'll use a convention that a positive sum is money that we receive, and a negative sum is money that we pay. Thus if we take out a loan of $1000, paying regular instalments of $50, ''p'' is +$1000 and ''m'' is -$50; if we buy an annuity for $6000 which pays $30 regularly, ''p'' is -$6000 and ''m'' is +$30.

The sum of the future values (at the end of the term) of the cash flows will be zero. For example, I pay $100 into an account at 5% interest (''p''=-100). The future value of this (1 year in the future) is -105; the sum I withdraw from the account after 1 year is $105 (''f''=+105). The sum of future values is -105 + +105 which is zero.

The three cash flows are: the initial sum ''p'', the periodic payments ''m'' and the final sum ''f''.

:The future value of ''p'' is p(1+r)n (see Present and Future Values, above).

:There are ''n'' payments of ''m'':
::·When payments are made at the end of each period, the future value of the last payment will be ''m'' (it arises on the final date); the future value of the payment before that will be m(1+r)... ; the future value of the first payment will be m(1+r)n-1. Summing those future values we get m[1 + (1+r) + (1+r)2 ..... (1+r)n-1] = m[(1-(1+r)n)/(1-(1+r))] = m[(1-(1+r)x)/(-r)] = m[((1+r)n-1)/r], using standard result for a geometric series that 1+ a + a2 .... an-1 = (1+an)/(1-a).
::·When payments are made at the start of each period, the future value of the last payment will be m(1+r)... ; the future value of the first payment will be m(1+r)n. Summing those future values we get m[((1+r)n-1)/r](1+r).
::·Combining these, where ''t'' is the type of payment: ''t''=0 for end of period, ''t''=1 for start of period, the future value of the payments is m[((1+r)n-1)/r](1+rt).

:The future value of ''f'' is simply f, as it actually arises on our future date.

The equation for the sum of the 3 cash flows is therefore:

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

=== PV, FV, PMT, NPER, RATE ===

These functions solve the annuity equation given above

:p(1+r)n + m[((1+r)n-1)/r](1+rt) + f = 0

So:
:<tt>'''PV'''</tt>: p = (-m[((1+r)n-1)/r](1+rt) - f) / (1+r)n

:<tt>'''FV'''</tt>: f = -p(1+r)n - m[((1+r)n-1)/r](1+rt)

:<tt>'''PMT'''</tt>: m = (-p(1+r)n - f) / [[((1+r)n-1)/r](1+rt)] = (-p(1+r)n - f)r / [((1+r)n-1)(1+rt)]

:<tt>'''NPER'''</tt>: re-arrange the equation and take logs

:<tt>'''RATE'''</tt>: the equation is solved by iteration - there is no general theoretical alternative.

=== IMPT, PPMT ===

These calculate how much of each payment is interest (<tt>'''IPMT'''</tt>) and how much is repaid capital (<tt>'''PPMT'''</tt>). <tt>'''PPMT'''</tt> is simply the payment (<tt>'''PMT'''</tt>) less the interest (<tt>'''IPMT'''</tt>), so our task here is to calculate that interest.

The interest included in a payment is the outstanding balance at the start of the preceding period times the interest rate. Here the 'preceding period' is the period before the payment is made - that is, the current period if the payment is made at the end of the period, or the period before that if the payment is made at the start of the period.

We will now derive the relevant outstanding balances.

The annuity equation we found earlier expresses future values at the end of term, that is, at the end of period ''n''. We will apply that equation to preceding periods.

For payments made at the end of the period (''t''=0), going back one period simply removes one payment. For period ''x'' we have
:p(1+r)x + m[((1+r)x-1)/r](1+rt) + fx = 0

or
:p(1+r)x + m((1+r)x-1)/r + fx = 0

fx is the outstanding balance at that time - that is the end of period ''x''; we require the outstanding balance at the start of the period, that is fx-1 given by:
:p(1+r)x-1 + m((1+r)x-1-1)/r + fx-1 = 0

The interest is given by rfx-1, that is:

:'''-rp(1+r)x - m((1+r)x-1)'''.

For payments made at the start of the period (''t''=1), for the time up to and including period ''x'' (< ''n'') there is a payment at the very start of the term, followed by ''x'' payments at the end of the periods. We can use the annuity equation with an initial value of ''p'' + ''m'' (instead of ''p''), where payments are made at the end:

:(p+m)(1+r)x + m((1+r)x-1)/r + fx = 0

This gives us the value (outstanding balance) at the end of period ''x''. In fact the payment is made at the start of the period, and is calculated on the balance at the start of the period before that; that balance is therefore given by fx-2, where:

:(p+m)(1+r)x-2 + m((1+r)x-2-1)/r + fx-2 = 0

and the interest is given by rfx-2, that is:
:'''-r(p+m)(1+r)x-2 - m((1+r)x-2-1)'''.

=== ISPMT ===
We take out a loan. We will pay the capital off in fixed instalments every period. Each period we will also pay the interest due on the outstanding capital. The first instalment is at the start of the term. The last instalment is at the start of the last period.

n = number of periods (a period may be a month, a year or whatever)

p = principal (the capital sum borrowed)

r = interest rate (fixed rate) per period (not necessarily per month or year)

Repayment of capital each period (does not change) = p/n

At start of 1st period:

Capital outstanding = p - p/n = p(n-1)/n

Interest for the period = (p(n-1)/n) r

At start of 2nd period:

Capital outstanding = p(n-1)/n - p/n
= p(n-2)/n

Interest for the period = (p(n-2)/n) r

At the start of the xth period:

Capital outstanding = p(n-x)/n

Interest for the period = (p(n-x)/n) r

=pr(n-x)/n

=== INTRATE ===

''(to be written)''

=== PRICE ===

<tt>'''PRICE'''</tt> returns:
: present_value_of_coupon_payments + present_value_of_redemption_payment - accrued_coupon_interest.

The present value of the redemption payment is calculated as:
:<math> \frac {redemptionvalue} {(1+p)^{periodstogo}}</math>

where ''redemptionvalue'' is the redemption payment, ''p'' is the yield per coupon period (= yield/frequency) and ''periodstogo'' is the (possibly fractional) number of periods remaining after the settlement date.

''periodstogo'' is calculated as: number of whole periods + (number of days from settlement to the next coupon / number of days in the period containing the settlement date).

This appears to be a conventional way to calculate present value for partial periods, but it is worth noting that it could be seen as an estimate rather than exact. For example, $100 invested for a year at 10% yields (a 'redemption payment' of) $110. The present value of $110 halfway through the year calculates as 110/1.10.5 which is $104.88, yet we'd expect to get $105 for half a year at 10%.

The present value of the coupon payments is calculated as:
:<math> \sum_{k=1}^{periods} \frac {payment_k} {(1+p)^{periodstogo_k}}</math>

where ''payment'' is the coupon payment per $100 face value = 100*rate/frequency, ''periods'' is the number of whole periods remaining after settlement, ''periodstogok'' is the (possibly fractional) number of periods from settlement to that payment.

The accrued coupon interest is calculated per $100 face value as
:<math> 100*\frac {rate} {frequency} * \frac {daysuptosettlement} {daysinsettlementperiod}</math>

where ''daysuptosettlement'' is the number of days from the payment date just before settlement, and ''daysinsettlementperiod'' is the number of days in the period containing the settlement date.

'Number of days' is calculated throughout according to the calendar system ('basis') in use - see [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEDISC ===

<tt>'''PRICEDISC'''</tt> returns:
:redemptionvalue - (redemptionvalue * discountrate * days_to_maturity / days_in_year).

In other words the discounted amount is calculated proportionately and simply subtracted from the redemption value.

In basis 1, the definition of 'number of days in a year' is unclear - it may be 365 or 366, or (in Excel) somewhere in between. See [[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|Financial date systems]].

=== PRICEMAT ===
<tt>'''[[Documentation/How_Tos/Calc: PRICEMAT function|PRICEMAT]]'''</tt> returns:
:present_value_of_redemptionvalue_and_interest - accrued_interest

The redemption capital value is simply 100.

The interest paid at redemption is 100 * rate * days_in_issue / days_in_year

(to be continued)

=== YIELDMAT ===
The bond pays interest just once, at maturity. The yield is calculated from this straightforward formula:

''maturity _value'' / ''settlement_value'' = 1 + ''yield''*YEARFRAC(''settlement'';''maturity''),

where YEARFRAC(''settlement'';''maturity'') is the fraction: number_of_days_between_''settlement''_and_''maturity'' divided by the number_of_days_in_a_year.

Now:

''maturity _value'' = ''redemption_value'' + ''interest''

= 100 + 100*''rate''*YEARFRAC(''issue'';''maturity'')

= 100*(1+''rate''*YEARFRAC(''issue'';''maturity''))

where ''rate'' is the annual interest rate.

''settlement_value'' can be found from the equation for PRICEMAT:

''price'' = ''presentvalue_of_redemption_and_interest'' - ''accrued_interest''

= ''settlement_value'' - ''accrued_interest''

=> ''settlement_value'' = ''price'' + ''accrued_interest''

= ''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')

From above:

1 + ''yield''*YEARFRAC(''settlement'';''maturity'') = ''maturity _value'' / ''settlement_value''

=> ''yield'' = (''maturity _value'' / ''settlement_value'' - 1)/YEARFRAC(''settlement'';''maturity'')

= (100*(1+''rate''*YEARFRAC(''issue'';''maturity'')) / (''price'' + 100*''rate''*YEARFRAC(''issue'';''settlement'')) - 1)/YEARFRAC(''settlement'';''maturity'')

where all the YEARFRACs are calculated using the correct basis.

=== See also: ===
[[Documentation/How_Tos/Calc: Date & Time functions#Financial date systems|'''Financial date systems''']]

[[Documentation/How_Tos/Calc: Financial functions|'''Financial functions''']]

Documentation/How Tos/Calc: AVERAGE function

2008-10-05T09:14:24Z

Drking: /* Issues: */

__NOTOC__

== AVERAGE ==
Returns the average of the arguments, ignoring text.

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

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

=== Example: ===

<tt>'''AVERAGE(2; 6; 4)'''</tt>
: returns <tt>'''4'''</tt>, the average of the three numbers in the list.

<tt>'''AVERAGE(B1:B3)'''</tt>
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''1'''</tt>, <tt>'''3'''</tt>, and <tt>'''apple'''</tt> returns <tt>'''2'''</tt>, the average of <tt>'''1'''</tt> and <tt>'''3'''</tt>. Text is ignored.

=== See also: ===
[[Documentation/How_Tos/Calc: AVERAGEA function|'''AVERAGEA''']],
[[Documentation/How_Tos/Calc: GEOMEAN function|'''GEOMEAN''']],
[[Documentation/How_Tos/Calc: HARMEAN function|'''HARMEAN''']],
[[Documentation/How_Tos/Calc: MEDIAN function|'''MEDIAN''']],
[[Documentation/How_Tos/Calc: MODE function|'''MODE''']],
[[Documentation/How_Tos/Calc: TRIMMEAN function|'''TRIMMEAN''']],
[[Documentation/How_Tos/Calc: AVEDEV function|'''AVEDEV''']],
[[Documentation/How_Tos/Calc: SUM function|'''SUM''']],
[[Documentation/How_Tos/Calc: COUNT function|'''COUNT''']],
[[Documentation/How_Tos/Calc: DAVERAGE function|'''DAVERAGE''']]

[[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''']]

===Issues: ===
* Calc assumes that [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] are <tt>'''1'''</tt> (<tt>'''TRUE'''</tt>) and <tt>'''0'''</tt> (<tt>'''FALSE'''</tt>). Excel ignores logical values.

Documentation/How Tos/Calc: Logical functions

2008-10-04T21:06:42Z

Drking: /* Logical values overview */

==List of Calc Logical functions==

The logical functions operate on logical ('boolean') values, that is, '''TRUE''' or '''FALSE'''.

{| border="0" cellpadding="0" cellspacing="10" align="left"
|-valign="top"
|[[Documentation/How_Tos/Calc: AND function|'''AND''']]
|returns '''TRUE''' if all the arguments are '''TRUE'''.

|-valign="top"
|[[Documentation/How_Tos/Calc: FALSE function|'''FALSE''']]
|returns the logical value '''FALSE'''.

|-valign="top"
|[[Documentation/How_Tos/Calc: IF function|'''IF''']]
|returns one of two values, depending on a test condition.

|-valign="top"
|[[Documentation/How_Tos/Calc: NOT function|'''NOT''']]
|returns '''TRUE''' if the argument is '''FALSE''', and '''FALSE''' if the argument is '''TRUE'''.

|-valign="top"
|[[Documentation/How_Tos/Calc: OR function|'''OR''']]
|returns '''TRUE''' if any of the arguments are '''TRUE'''.

|-valign="top"
|[[Documentation/How_Tos/Calc: TRUE function|'''TRUE''']]
|returns the logical value '''TRUE'''.

|}
 


== Logical values overview ==

In Calc, logical values are represented by numbers: <tt>'''0'''</tt> is <tt>'''FALSE'''</tt> and <tt>'''1'''</tt> is <tt>'''TRUE'''</tt>. We may enter <tt>'''1'''</tt> in a cell, and then (selecting '''Format - Cells...''') choose the 'Boolean Value' format to display the number as <tt>'''TRUE'''</tt>.

Any Calc function that returns a logical result, actually returns the number <tt>'''0'''</tt> or <tt>'''1'''</tt>. Any Calc cell formula that returns a logical result, tells the cell to display <tt>'''FALSE'''</tt> or <tt>'''TRUE'''</tt> - if the cell has the default (General) format. For example <tt>'''=TRUE()'''</tt> returns the value 1, which is displayed as <tt>'''TRUE'''</tt>; if you then change the format to a numerical format, it displays <tt>'''1'''</tt>.

Any Calc function that tests for a logical result, actually tests if the value evaluates as the number <tt>'''0'''</tt>. <tt>'''0'''</tt> is taken as <tt>'''FALSE'''</tt> and anything else is taken as <tt>'''TRUE'''</tt>. For example where A1 contains <tt>'''apple'''</tt>, <tt>'''NOT(A1)'''</tt> returns <tt>'''TRUE'''</tt> because 'apple' evaluates as <tt>'''0'''</tt> = <tt>'''FALSE'''</tt>. Another example: <tt>'''NOT(57)'''</tt> returns <tt>'''FALSE'''</tt> because <tt>'''57'''</tt> evaluates as <tt>'''TRUE'''</tt>.

Excel in contrast has a separate type for logical values - they are not numbers, but are sometimes converted to numbers. Take care therefore, if you need compatibility:

*In Excel functions like <tt>'''SUM'''</tt> and <tt>'''AVERAGE'''</tt> ignore logical values; in Calc they are numbers and are therefore included. This is unlikely to cause difficulties, because you would not normally expect to try to add up logical values.
*''Advanced topic'': In Calc <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big")'''</tt> counts the number of big red items. In Excel the logical results returned by <tt>'''A1:A6="red"'''</tt> and <tt>'''B1:B6="big"'''</tt> are ignored by <tt>'''SUMPRODUCT'''</tt>; logical values are converted if a number is expected, so <tt>'''SUMPRODUCT(1*(A1:A6="red"); 1*(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(0+(A1:A6="red"); 0+(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(--(A1:A6="red"); --(B1:B6="big"))'''</tt> or <tt>'''SUMPRODUCT((A1:A6="red")*(B1:B6="big"))'''</tt> all work in both spreadsheets.

==See also==

[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]

Documentation/How Tos/Calc: SUMPRODUCT function

2008-10-04T18:18:38Z

Drking: /* Issues: */

__NOTOC__

== SUMPRODUCT ==
Returns the sum of the products of corresponding array elements.

=== Syntax: ===
<tt>'''SUMPRODUCT(array1; array2; ... array30)'''</tt>
: <tt>'''array1'''</tt> to <tt>'''array30'''</tt> are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.

:<tt>'''SUMPRODUCT'''</tt> returns [[Image:Calc_sumproduct_equation.png]] for the i elements in the arrays.

: You can use <tt>'''SUMPRODUCT'''</tt> to calculate the scalar product of two vectors.

:Advanced topic:

:<tt>'''SUMPRODUCT'''</tt> evaluates each parameter <tt>'''array1'''</tt> to <tt>'''array30'''</tt> as an [[Documentation/How_Tos/Using Arrays|array formula]], but does not need to be entered as an array formula. In other words it can be entered with the '''Enter''' key, rather than '''Cntrl-Shift-Enter'''. See the example below.

: <tt>'''SUMPRODUCT'''</tt> can also be used to sum cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' and the example below.

=== Example: ===
<tt>'''SUMPRODUCT(A1:B2; F1:G2)'''</tt>
:returns <tt>'''A1'''</tt>*<tt>'''F1'''</tt> + <tt>'''B1'''</tt>*<tt>'''G1'''</tt> + <tt>'''A2'''</tt>*<tt>'''F2'''</tt> + <tt>'''B2'''</tt>*<tt>'''G2'''</tt>.

Advanced topic:

<tt>'''SUMPRODUCT(ABS(A1:A6))'''</tt>
: when entered 'normally' (not as an [[Documentation/How_Tos/Using Arrays|array formula]]), returns the sum of the absolute values in cells <tt>'''A1:A6'''</tt>. <tt>'''SUMPRODUCT'''</tt> forces <tt>'''ABS(A1:A6)'''</tt> to be evaluated as an [[Documentation/How_Tos/Using Arrays|array formula]].

<tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt>
:returns the sum of cells in <tt>'''C1:C6'''</tt> whose corresponding entries in the A column are <tt>'''red'''</tt> and in the B column are <tt>'''big'''</tt>. This is not portable to Excel, as Excel ignores logical values.

<tt>'''SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)'''</tt>
:a version of <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> which is portable to Excel, as the logical values are converted to numbers during the multiplication.

=== See also: ===
'''[[Documentation/How_Tos/Calc: SUM function|SUM]]''',
'''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF]]''',
'''[[Documentation/How_Tos/Calc: SUMSQ function|SUMSQ]]''',
'''[[Documentation/How_Tos/Calc: SUMX2MY2 function|SUMX2MY2]]''',
'''[[Documentation/How_Tos/Calc: SUMX2PY2 function|SUMX2PY2]]''',
'''[[Documentation/How_Tos/Calc: SUMXMY2 function|SUMXMY2]]'''

'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''

[[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''']]

=== Issues: ===
*In Excel SUMPRODUCT ignores [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0.

Documentation/How Tos/Calc: SUM function

2008-10-04T18:18:21Z

Drking: /* Issues: */

__NOTOC__

== SUM ==
Sums the contents of cells.

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

: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges/arrays of numbers whose sum is to be calculated.

: <tt>'''SUM'''</tt> ignores any text or empty cell within a range or array.

: <tt>'''SUM'''</tt> can also be used to sum or count cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''.

=== Example: ===

<tt>'''SUM(2; 3; 4)'''</tt>

: returns <tt>'''9'''</tt>, because <tt>'''2'''</tt>+<tt>'''3'''</tt>+<tt>'''4'''</tt> = <tt>'''9'''</tt>.

<tt>'''SUM(B1:B3)'''</tt>

: (where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''1.1'''</tt>, <tt>'''2.2'''</tt>, <tt>'''3.3'''</tt>) returns <tt>'''6.6'''</tt>.

=== See also: ===
'''[[Documentation/How_Tos/Calc: SUBTOTAL function|SUBTOTAL]]''',
'''[[Documentation/How_Tos/Calc: DSUM function|DSUM]]''',
'''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF]]''',
'''[[Documentation/How_Tos/Calc: SUMSQ function|SUMSQ]]''',
'''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]'''

'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''

'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''

[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]

=== Issues: ===
*In Excel SUM ignores [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0.

[[fr:Documentation/FR/Calc:Fonction SOMME]]

Documentation/How Tos/Calc: PRODUCT function

2008-10-04T18:17:52Z

Drking: Issues

__NOTOC__

== PRODUCT ==
Multiplies all the numbers given as arguments and returns the product.

=== Syntax: ===
<tt>'''PRODUCT(number1; number2; ... number30)'''</tt>
: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges/arrays of numbers whose product is to be calculated.

: <tt>'''PRODUCT'''</tt> returns <tt>'''number1 * number2 * number3 * ........ '''</tt>

: <tt>'''PRODUCT'''</tt> ignores any text or empty cell within a range or array.

=== Example: ===
<tt>'''PRODUCT(2; 3; 4)'''</tt>
: returns <tt>'''24'''</tt> (<tt>'''2 * 3 * 4'''</tt>).

<tt>'''PRODUCT(A1:A2)'''</tt>
: where <tt>'''A1'''</tt> and <tt>'''A2'''</tt> contain <tt>'''3'''</tt> and <tt>'''5'''</tt>, returns <tt>'''15'''</tt> (<tt>'''3 * 5'''</tt>).

=== See also: ===
'''[[Documentation/How_Tos/Calc: FACT function|FACT]]''',
'''[[Documentation/How_Tos/Calc: SUM function|SUM]]'''

'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''

[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]

=== Issues: ===
*In Excel <tt>'''PRODUCT'''</tt> ignores [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] (that is, it treats them as 1). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0.

Documentation/How Tos/Calc: PRODUCT function

2008-10-04T18:11:02Z

Drking: /* Syntax: */

Documentation/How Tos/Calc: PRODUCT function

2008-10-04T18:10:02Z

Drking: /* Syntax: */

__NOTOC__

== PRODUCT ==
Multiplies all the numbers given as arguments and returns the product.

=== Syntax: ===
<tt>'''PRODUCT(number1; number2; ... number30)'''</tt>
: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges/arrays of numbers whose product is to be calculated.

: <tt>'''PRODUCT'''</tt> ignores any text or empty cell within a range or array.

: <tt>'''PRODUCT'''</tt> returns <tt>'''number1 * number2 * number3 * ........ '''</tt>

=== Example: ===
<tt>'''PRODUCT(2; 3; 4)'''</tt>
: returns <tt>'''24'''</tt> (<tt>'''2 * 3 * 4'''</tt>).

<tt>'''PRODUCT(A1:A2)'''</tt>
: where <tt>'''A1'''</tt> and <tt>'''A2'''</tt> contain <tt>'''3'''</tt> and <tt>'''5'''</tt>, returns <tt>'''15'''</tt> (<tt>'''3 * 5'''</tt>).

=== See also: ===
'''[[Documentation/How_Tos/Calc: FACT function|FACT]]''',
'''[[Documentation/How_Tos/Calc: SUM function|SUM]]'''

'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''

[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]

Documentation/How Tos/Calc: SUMPRODUCT function

2008-10-04T18:06:36Z

Drking: /* Issues: */

__NOTOC__

== SUMPRODUCT ==
Returns the sum of the products of corresponding array elements.

=== Syntax: ===
<tt>'''SUMPRODUCT(array1; array2; ... array30)'''</tt>
: <tt>'''array1'''</tt> to <tt>'''array30'''</tt> are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.

:<tt>'''SUMPRODUCT'''</tt> returns [[Image:Calc_sumproduct_equation.png]] for the i elements in the arrays.

: You can use <tt>'''SUMPRODUCT'''</tt> to calculate the scalar product of two vectors.

:Advanced topic:

:<tt>'''SUMPRODUCT'''</tt> evaluates each parameter <tt>'''array1'''</tt> to <tt>'''array30'''</tt> as an [[Documentation/How_Tos/Using Arrays|array formula]], but does not need to be entered as an array formula. In other words it can be entered with the '''Enter''' key, rather than '''Cntrl-Shift-Enter'''. See the example below.

: <tt>'''SUMPRODUCT'''</tt> can also be used to sum cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' and the example below.

=== Example: ===
<tt>'''SUMPRODUCT(A1:B2; F1:G2)'''</tt>
:returns <tt>'''A1'''</tt>*<tt>'''F1'''</tt> + <tt>'''B1'''</tt>*<tt>'''G1'''</tt> + <tt>'''A2'''</tt>*<tt>'''F2'''</tt> + <tt>'''B2'''</tt>*<tt>'''G2'''</tt>.

Advanced topic:

<tt>'''SUMPRODUCT(ABS(A1:A6))'''</tt>
: when entered 'normally' (not as an [[Documentation/How_Tos/Using Arrays|array formula]]), returns the sum of the absolute values in cells <tt>'''A1:A6'''</tt>. <tt>'''SUMPRODUCT'''</tt> forces <tt>'''ABS(A1:A6)'''</tt> to be evaluated as an [[Documentation/How_Tos/Using Arrays|array formula]].

<tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt>
:returns the sum of cells in <tt>'''C1:C6'''</tt> whose corresponding entries in the A column are <tt>'''red'''</tt> and in the B column are <tt>'''big'''</tt>. This is not portable to Excel, as Excel ignores logical values.

<tt>'''SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)'''</tt>
:a version of <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> which is portable to Excel, as the logical values are converted to numbers during the multiplication.

=== See also: ===
'''[[Documentation/How_Tos/Calc: SUM function|SUM]]''',
'''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF]]''',
'''[[Documentation/How_Tos/Calc: SUMSQ function|SUMSQ]]''',
'''[[Documentation/How_Tos/Calc: SUMX2MY2 function|SUMX2MY2]]''',
'''[[Documentation/How_Tos/Calc: SUMX2PY2 function|SUMX2PY2]]''',
'''[[Documentation/How_Tos/Calc: SUMXMY2 function|SUMXMY2]]'''

'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''

[[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''']]

=== Issues: ===
*In Excel SUMPRODUCT ignores logical values (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0.

Documentation/How Tos/Calc: SUM function

2008-10-04T18:05:27Z

Drking: Issues

__NOTOC__

== SUM ==
Sums the contents of cells.

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

: <tt>'''number1'''</tt> to <tt>'''number30'''</tt> are up to 30 numbers or ranges/arrays of numbers whose sum is to be calculated.

: <tt>'''SUM'''</tt> ignores any text or empty cell within a range or array.

: <tt>'''SUM'''</tt> can also be used to sum or count cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''.

=== Example: ===

<tt>'''SUM(2; 3; 4)'''</tt>

: returns <tt>'''9'''</tt>, because <tt>'''2'''</tt>+<tt>'''3'''</tt>+<tt>'''4'''</tt> = <tt>'''9'''</tt>.

<tt>'''SUM(B1:B3)'''</tt>

: (where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt> contain <tt>'''1.1'''</tt>, <tt>'''2.2'''</tt>, <tt>'''3.3'''</tt>) returns <tt>'''6.6'''</tt>.

=== See also: ===
'''[[Documentation/How_Tos/Calc: SUBTOTAL function|SUBTOTAL]]''',
'''[[Documentation/How_Tos/Calc: DSUM function|DSUM]]''',
'''[[Documentation/How_Tos/Calc: SUMIF function|SUMIF]]''',
'''[[Documentation/How_Tos/Calc: SUMSQ function|SUMSQ]]''',
'''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]'''

'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''

'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''

[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]

=== Issues: ===
*In Excel SUM ignores logical values (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0.

[[fr:Documentation/FR/Calc:Fonction SOMME]]