Re: Help needed with analysing a column of data

"Nigel" wrote...
>I have a column of numbers most of which are repeated several times eg
..
>I want to generate two columns of data from it - the first column is a 
>list of the discrete values in descending order, the second column is a 
>count of how many times that value occurs eg
>
>2.174  1
>0.706  1
>0.630  2
>0.551  9
>0.516  6
>0.324  1
>0.146  5
>0.141  11
>-0.011 3
>-0.491 3
>-1.102 3
>-1.662 4
>
>I'm not using Excel so a VB solution isn't any use to me, but I can 
>probably translate Excel macros without too much trouble.

So what are you using? Lotus 123 Release 9.0 or higher? If so, @LARGE, @COUNTIF
and @SUM would be all you needed. If the original column of data were named
LIST, use these formulas beginning in cell C1.

C1:  @LARGE($LIST,1)   [or @MAX($LIST)]

D1:  @COUNTIF($LIST,C1)

C2:  @LARGE($LIST,@SUM(D$1..D1,1))

D2:  @COUNTIF($LIST,C2)

Copy C2..D2 and paste in cells below as far down as needed. The formulas will
return ERR when the original data has been exhausted.

If you're not even using a spreadsheet, does your programming language provide
associative arrays? If so, maybe you could adapt this awk script.


!($1 in c) {
++n

for (i = 1; i <= n; ++i) {

if ($1 > d[i] && s == "") {

s = d[i]
d[i] = $1

} else if (s != "") {

t = d[i]
d[i] = s
s = t

} else if (i == n) {

d[i] = $1

} #end if

} #end for

s = ""

} #end action for pattern !($1 in c)

{  ++c[$1]  }

END {  for (i = 1; i <= n; ++i) printf("%8g  %4d\n", d[i], c[d[i]])  }

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.