Details

    • Proposal:
      Hide

      Accepted for ODF 1.3 as per 6 June 2016

      Show
      Accepted for ODF 1.3 as per 6 June 2016

      Description

      ODF OpenFormula has

      6.9.3 DCOUNT
      Syntax: DCOUNT( Database D ; Field F ; Criteria C )

      6.9.4 DCOUNTA
      Syntax: DCOUNTA( Database D ; Field F ; Criteria C )

      For these two functions the Field argument can be omitted (empty) in
      LibreOffice Calc and Microsoft Excel, it should be defined as

      Syntax: DCOUNT( Database D ; [ Field F ] ; Criteria C )

      Semantics: If the Field argument is omitted, DCOUNT returns the count of
      all records that satisfy Criteria.

      Syntax: DCOUNTA( Database D ; [ Field F ] ; Criteria C )

      Semantics: If the Field argument is omitted, DCOUNTA returns the count
      of all records that satisfy Criteria.

      From Eike Rathke - https://lists.oasis-open.org/archives/office-comment/201605/msg00000.html

        Attachments

          Activity

          Hide
          mstahl Michael Stahl (Inactive) added a comment -

          Eike says INDEX is an existing function with optional non-last arguments

          http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#INDEX

          Show
          mstahl Michael Stahl (Inactive) added a comment - Eike says INDEX is an existing function with optional non-last arguments http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#INDEX
          Hide
          patrick Patrick Durusau added a comment -

          Other functions with an optional non-last argument (including Eike's index). I included functions where the last two arguments were optional, reasoning that the next to last argument could be omitted, yet the last argument be present.

          6.10.14 NETWORKDAYS
          Summary: Returns the whole number of work days between two dates.
          Syntax: NETWORKDAYS( DateParam Date1 ; DateParam Date2 [ ; [ DateSequence holidays ] [ ;
          LogicalSequence workdays ] ] )

          6.10.22 WORKDAY
          Summary: Returns the date serial number which is a specified number of work days before or
          after an input date.
          Syntax: WORKDAY( DateParam Date ; Number Offset [ ; [ DateSequence Holidays ] [ ;
          LogicalSequence Workdays ] ] )

          6.12.2 ACCRINT
          Summary: Calculates the accrued interest for securities with periodic interest payments.
          Syntax: ACCRINT( DateParam issue ; DateParam first ; DateParam settlement ; Number
          coupon ; Number par ; Integer frequency [ ; Basis basis = 0 [ ; Logical calc_method = TRUE() ] ] )

          6.12.20 FV
          Summary: Compute the future value (FV) of an investment.
          Syntax: FV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Pv = 0 ] [ ; Number
          PayType = 0 ] ] )

          6.12.23 IPMT
          Summary: Returns the amount of an annuity payment going towards interest.
          Syntax: IPMT( Number Rate ; Number Period ; Number Nper ; Number PV [ ; Number FV = 0 [ ;
          Number Type = 0 ] ] )

          6.12.29 NPER
          Summary: Compute the number of payment periods for an investment.
          Syntax: NPER( Number Rate ; Number Payment ; Number Pv [ ; [ Number Fv ] [ ; Number
          PayType ] ] )

          6.12.36 PMT
          Summary: Compute the payment made each period for an investment.
          Syntax: PMT( Number Rate ; Integer Nper ; Number Pv [ ; [ Number Fv = 0 ] [ ; Number PayType
          = 0 ] ] )

          6.12.37 PPMT
          Summary: Calculate the payment for a given period on the principal for an investment at a given
          interest rate and constant payments.
          Syntax: PPMT( Number Rate ; Integer Period ; Integer nPer ; Number Present [ ; Number Future
          = 0 [ ; Number Type = 0 ] ] )

          6.12.41 PV
          Summary: Compute the present value (PV) of an investment.
          Syntax: PV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Fv = 0 ] [ ; Number
          PayType = 0 ] ] )

          6.12.42 RATE
          Summary: Compute the interest rate per period of an investment.
          Syntax: RATE( Number Nper ; Number Payment ; Number Pv [ ; [ Number Fv = 0 ] [ ; [ Number
          PayType = 0 ] [ ; Number Guess = 0.1 ] ] ] )

          6.12.50 VDB
          Summary: Calculates the depreciation allowance of an asset with an initial value, an expected
          useful life, and a final value of salvage for a period specified, using the variable-rate declining
          balance method..
          Syntax: VDB( Number cost ; Number salvage ; Number lifeTime ; Number startPeriod ; Number
          endPeriod [ ; Number depreciationFactor = 2 [ ; Logical noSwitch = FALSE() ] ] )

          6.13.28 NUMBERVALUE
          Summary: Convert text to number, in a locale-independent way
          Syntax: NUMBERVALUE( Text X [ ; Text DecimalSeparator [ ; Text GroupSeparator ] ] )

          6.14.2 ADDRESS
          Summary: Returns a cell address (reference) as text
          Syntax: ADDRESS( Integer Row ; Integer Column [ ; Integer Abs = 1 [ ; Logical A1 = TRUE() [ ;
          Text Sheet ] ] ] )

          6.14.6 INDEX
          Summary: Returns a value using a row and column index value (and optionally an area index).
          Syntax: INDEX( ReferenceList|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ;
          Integer AreaNumber = 1 ] )

          6.14.11 OFFSET
          Summary: Modifies a reference's position and dimension.
          Syntax: OFFSET( Reference reference ; Integer rowOffset ; Integer columnOffset [ ; [ Integer
          newHeight ] [ ; [ Integer newWidth ] ] ] )

          6.15.4 IF
          Summary: Return one of two values, depending on a condition
          Syntax: IF( Logical Condition [ ; [ Any IfTrue ] [ ; [ Any IfFalse ] ] ] )

          6.16.29 EUROCONVERT
          Summary: Converts a Number, representing a value in one European currency, to an equivalent
          value in another European currency, according to the fixed conversion rates defined by the
          Council of the European Union.
          Syntax: EUROCONVERT( Number N ; Text From ; Text To [ ; Logical FullPrecision = FALSE() [ ;
          Integer TriangulationPrecision ] ] )

          6.17.1 CEILING
          Summary: Round a number N up to the nearest multiple of the second parameter, significance.
          Syntax: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] )

          6.17.3 FLOOR
          Summary: Round a number N down to the nearest multiple of the second parameter,
          significance.
          Syntax: FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] )

          6.18.7 BETADIST
          Summary: returns the value of the probability density function or the cumulative distribution
          function for the beta distribution.
          Syntax: BETADIST( Number x ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 [ ;
          Logical Cumulative = TRUE() ] ] ] )

          6.18.8 BETAINV
          Summary: returns the inverse of BETADIST(x;a;b;a;b;TRUE()).
          Syntax: BETAINV( Number p ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 ] ] )

          6.18.35 GROWTH
          Summary: Returns predicted values based on an exponential regression.
          Syntax: GROWTH( Array knownY [ ; [ Array knownX ] [ ; [ Array newX ] [ ; Logical Const =
          TRUE() ] ] ] )

          6.18.41 LINEST
          Summary: Returns the parameters of the (simple or multiple) linear regression equation for the
          given data and, optionally, statistics on this regression.
          Syntax: LINEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE() [ ; Logical Stats =
          FALSE() ] ] ] )

          6.18.42 LOGEST
          Summary: Returns the parameters of an exponential regression equation for the given data
          obtained by linearizing this intrinsically linear response function and returns, optionally, statistics
          on this regression.
          Syntax: LOGEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE() [ ; Logical Stats =
          FALSE() ] ] ] )

          6.18.43 LOGINV
          Summary: returns the inverse of LOGNORMDIST(x;Mean;StandardDeviation,TRUE()).
          Syntax: LOGINV( Number p [ ; Number Mean = 0 [ ; Number StandardDeviation = 1 ] ] )

          6.18.44 LOGNORMDIST
          Summary: returns the value of the probability density function or the cumulative distribution
          function for the lognormal distribution with the mean and standard deviation given.
          Syntax: LOGNORMDIST( Number x [ ; Number m = 0 [ ; Number s = 1 [ ; Logical Cumulative =
          TRUE() ] ] ] )

          6.18.79 TREND
          Summary: Returns predicted values based on a simple or multiple linear regression.
          Syntax: TREND( Array knownY [ ; [ Array knownX ] [ ; [ Array newX ] [ ; Logical Const =
          TRUE() ] ] ] )

          6.20.10 FIXED
          Summary: Round the number to a specified number of decimals and format the result as a text.
          Syntax: FIXED( Number N [ ; Integer D = 2 [ ; Logical OmitSeparators = FALSE() ] ] )

          Show
          patrick Patrick Durusau added a comment - Other functions with an optional non-last argument (including Eike's index). I included functions where the last two arguments were optional, reasoning that the next to last argument could be omitted, yet the last argument be present. 6.10.14 NETWORKDAYS Summary: Returns the whole number of work days between two dates. Syntax: NETWORKDAYS( DateParam Date1 ; DateParam Date2 [ ; [ DateSequence holidays ] [ ; LogicalSequence workdays ] ] ) 6.10.22 WORKDAY Summary: Returns the date serial number which is a specified number of work days before or after an input date. Syntax: WORKDAY( DateParam Date ; Number Offset [ ; [ DateSequence Holidays ] [ ; LogicalSequence Workdays ] ] ) 6.12.2 ACCRINT Summary: Calculates the accrued interest for securities with periodic interest payments. Syntax: ACCRINT( DateParam issue ; DateParam first ; DateParam settlement ; Number coupon ; Number par ; Integer frequency [ ; Basis basis = 0 [ ; Logical calc_method = TRUE() ] ] ) 6.12.20 FV Summary: Compute the future value (FV) of an investment. Syntax: FV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Pv = 0 ] [ ; Number PayType = 0 ] ] ) 6.12.23 IPMT Summary: Returns the amount of an annuity payment going towards interest. Syntax: IPMT( Number Rate ; Number Period ; Number Nper ; Number PV [ ; Number FV = 0 [ ; Number Type = 0 ] ] ) 6.12.29 NPER Summary: Compute the number of payment periods for an investment. Syntax: NPER( Number Rate ; Number Payment ; Number Pv [ ; [ Number Fv ] [ ; Number PayType ] ] ) 6.12.36 PMT Summary: Compute the payment made each period for an investment. Syntax: PMT( Number Rate ; Integer Nper ; Number Pv [ ; [ Number Fv = 0 ] [ ; Number PayType = 0 ] ] ) 6.12.37 PPMT Summary: Calculate the payment for a given period on the principal for an investment at a given interest rate and constant payments. Syntax: PPMT( Number Rate ; Integer Period ; Integer nPer ; Number Present [ ; Number Future = 0 [ ; Number Type = 0 ] ] ) 6.12.41 PV Summary: Compute the present value (PV) of an investment. Syntax: PV( Number Rate ; Number Nper ; Number Payment [ ; [ Number Fv = 0 ] [ ; Number PayType = 0 ] ] ) 6.12.42 RATE Summary: Compute the interest rate per period of an investment. Syntax: RATE( Number Nper ; Number Payment ; Number Pv [ ; [ Number Fv = 0 ] [ ; [ Number PayType = 0 ] [ ; Number Guess = 0.1 ] ] ] ) 6.12.50 VDB Summary: Calculates the depreciation allowance of an asset with an initial value, an expected useful life, and a final value of salvage for a period specified, using the variable-rate declining balance method.. Syntax: VDB( Number cost ; Number salvage ; Number lifeTime ; Number startPeriod ; Number endPeriod [ ; Number depreciationFactor = 2 [ ; Logical noSwitch = FALSE() ] ] ) 6.13.28 NUMBERVALUE Summary: Convert text to number, in a locale-independent way Syntax: NUMBERVALUE( Text X [ ; Text DecimalSeparator [ ; Text GroupSeparator ] ] ) 6.14.2 ADDRESS Summary: Returns a cell address (reference) as text Syntax: ADDRESS( Integer Row ; Integer Column [ ; Integer Abs = 1 [ ; Logical A1 = TRUE() [ ; Text Sheet ] ] ] ) 6.14.6 INDEX Summary: Returns a value using a row and column index value (and optionally an area index). Syntax: INDEX( ReferenceList|Array DataSource ; [ Integer Row ] [ ; [ Integer Column ] ] [ ; Integer AreaNumber = 1 ] ) 6.14.11 OFFSET Summary: Modifies a reference's position and dimension. Syntax: OFFSET( Reference reference ; Integer rowOffset ; Integer columnOffset [ ; [ Integer newHeight ] [ ; [ Integer newWidth ] ] ] ) 6.15.4 IF Summary: Return one of two values, depending on a condition Syntax: IF( Logical Condition [ ; [ Any IfTrue ] [ ; [ Any IfFalse ] ] ] ) 6.16.29 EUROCONVERT Summary: Converts a Number, representing a value in one European currency, to an equivalent value in another European currency, according to the fixed conversion rates defined by the Council of the European Union. Syntax: EUROCONVERT( Number N ; Text From ; Text To [ ; Logical FullPrecision = FALSE() [ ; Integer TriangulationPrecision ] ] ) 6.17.1 CEILING Summary: Round a number N up to the nearest multiple of the second parameter, significance. Syntax: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) 6.17.3 FLOOR Summary: Round a number N down to the nearest multiple of the second parameter, significance. Syntax: FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) 6.18.7 BETADIST Summary: returns the value of the probability density function or the cumulative distribution function for the beta distribution. Syntax: BETADIST( Number x ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 [ ; Logical Cumulative = TRUE() ] ] ] ) 6.18.8 BETAINV Summary: returns the inverse of BETADIST(x;a;b;a;b;TRUE()). Syntax: BETAINV( Number p ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 ] ] ) 6.18.35 GROWTH Summary: Returns predicted values based on an exponential regression. Syntax: GROWTH( Array knownY [ ; [ Array knownX ] [ ; [ Array newX ] [ ; Logical Const = TRUE() ] ] ] ) 6.18.41 LINEST Summary: Returns the parameters of the (simple or multiple) linear regression equation for the given data and, optionally, statistics on this regression. Syntax: LINEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE() [ ; Logical Stats = FALSE() ] ] ] ) 6.18.42 LOGEST Summary: Returns the parameters of an exponential regression equation for the given data obtained by linearizing this intrinsically linear response function and returns, optionally, statistics on this regression. Syntax: LOGEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE() [ ; Logical Stats = FALSE() ] ] ] ) 6.18.43 LOGINV Summary: returns the inverse of LOGNORMDIST(x;Mean;StandardDeviation,TRUE()). Syntax: LOGINV( Number p [ ; Number Mean = 0 [ ; Number StandardDeviation = 1 ] ] ) 6.18.44 LOGNORMDIST Summary: returns the value of the probability density function or the cumulative distribution function for the lognormal distribution with the mean and standard deviation given. Syntax: LOGNORMDIST( Number x [ ; Number m = 0 [ ; Number s = 1 [ ; Logical Cumulative = TRUE() ] ] ] ) 6.18.79 TREND Summary: Returns predicted values based on a simple or multiple linear regression. Syntax: TREND( Array knownY [ ; [ Array knownX ] [ ; [ Array newX ] [ ; Logical Const = TRUE() ] ] ] ) 6.20.10 FIXED Summary: Round the number to a specified number of decimals and format the result as a text. Syntax: FIXED( Number N [ ; Integer D = 2 [ ; Logical OmitSeparators = FALSE() ] ] )
          Hide
          patrick Patrick Durusau added a comment -

          04 Dec 2017

          Show
          patrick Patrick Durusau added a comment - 04 Dec 2017
          Hide
          patrick Patrick Durusau added a comment -

          OpenDocument-v1.3-wd05-part4-openformula.odt

          Notice that the semantics in both cases were appended to the semantics IF the Field was specified. That wasn't explicit in the original proposal.

          Show
          patrick Patrick Durusau added a comment - OpenDocument-v1.3-wd05-part4-openformula.odt Notice that the semantics in both cases were appended to the semantics IF the Field was specified. That wasn't explicit in the original proposal.

            People

            • Assignee:
              patrick Patrick Durusau
              Reporter:
              patrick Patrick Durusau
            • Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: