OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: DISTINCT Values

• To: office-comment@lists.oasis-open.org
• Date: Fri, 08 Jun 2007 00:19:35 +0300

```I strongly miss a function to return the number of DISTINCT values
existent in a given cell range. To my knowledge, this functionality is
missing in every spreadsheet application, although most of the research
involves such analysis.

I would be further interested  to perform some operations using these
distinct values.

Functions:
DISTINCT( 'cell_range', AS.TEXT = TRUE, IGNORE.CASE = TRUE)
returns number of distinct strings in the cell range

DISTINCT( 'cell_range', AS.TEXT = FALSE, TOLERANCE = 0)
returns number of different values
DISTINCT( 'cell_range', AS.TEXT = FALSE, TOLERANCE = 0.5, ORDER =
"ASCENDING")
returns number of different values; values within TOLERANCE are
considered EQUAL
- values are ranked first using the specified ORDER
- IF( x[i] is within TOLERANCE of x[i-1]), the 2 values are considered
equal
DISTINCT( 'cell_range', AS.TEXT = FALSE, TOLERANCE = '5%', ORDER =
"ASCENDING")
returns number of different values; values within TOLERANCE are
considered EQUAL
- values are ranked first using the specified ORDER
- IF( x[i] is within TOLERANCE of x[i-1]), the 2 values are considered
equal
- the absolute value for TOLERANCE is computed as x[i-1] * TOLERANCE

DISTINCT( 'cell_range', AS.TEXT = FALSE, 'cell_range2')
returns number of different values within 'cell_range'
- 'cell_range2' describes the bounds/intervals used for splitting the
initial data (i.e. for splitting 'cell_range')
- the values within 'cell_range2' are ranked
- IF any value from 'cell_range' < MIN('cell_range2') => this is first
group
- any value from 'cell_range' is within
RANKED(cell_range2)[1]
RANKED(cell_range2)[2] => next group
- ...
- any value from 'cell_range' > MAX('cell_range2') => this is the last
group

Of course, DISTINCT() is just one aspect of the analysis. Actually, I am
more interested in doing specific calculations based on these distinct
groups (like SUM(), COUNT(), ...). I will describe such calculations in
a later post.

Sincerely,