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

*Subject*: **DISTINCT Values**

*From*:**Leonard Mada <discoleo@gmx.net>***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, Leonard Mada

**Follow-Ups**:**Re: [office-comment] DISTINCT Values***From:*Eike Rathke <erack@sun.com>

**Re: [office-comment] DISTINCT Values***From:*Patrick Durusau <patrick@durusau.net>

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