Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

Question

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.

I have a multiset of data described by rows of (count, value) pairs. e.g.

count,     value
     3,    1,
     7,    2,
     6,    3,
     2,    4,
     1,    5,
     8,    6,

This represents the multiset {1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4, 4, 5, 6, 6, 6, 6, 6, 6, 6, 6}

I'd like to perform some statistics on the final set, e.g. excel's AVERAGE() or MODE(). How do I do this? How do I 'expand' the (count,value) pairs into a set/array that the spreadsheet program can work on?

Currently I can only do the statistics on the values in the sheet, which obviously aren't correct.

A trivial python implementation of what I'm talking about is below.

set_desc = [
        #count, value
        (3, 1),
        (7, 2),
        (6, 3),
        (2, 4),
        (1, 5),
        (8, 6),
     ]

multiset = []
# [3] * 5 in python would make the list [3,3,3,3,3]
for (count, value) in set_desc:
    print "Addding", [value] * count
    multiset.extend([value] * count)


sorted_multiset = sorted(multiset)
i0 = (len(sorted_multiset)-1)/2
i1 = (len(sorted_multiset))/2
print "final values in multiset are", sorted_multiset
print "median value(s) lies at index", i0, i1
print ""

print "mean average is", float(sum(sorted_multiset, 0))/len(sorted_multiset)
print "median value is", float(sorted_multiset[i0] + sorted_multiset[i1])/2

Answer 1

A slightly convoluted way using Excel to expand the multiset using array formulas. The general idea is to create a new column (in a new sheet if wanted) containing the expanded array of values on which standard excel formulas can be applied.

1. Add two columns to your multiset definition giving start and end index of the expanded set (can easily be done with standard arithmetic)
2. Create a new column that uses ROW(cell) to determine the index in the set and retrieve the correct set element.
3. Copy/paste the array formula until the past the last set element (the formula will yield 0 when the last element has been passed)

Formula for the expanded column (remember to enter with Ctrl-Shift-Enter to make it an array formula)

=SUM((ROW(E1)>=$C$2:$C$7)*(ROW(E1)<=$D$2:$D$7)*$B$2:$B$7)

Resulting values

count value start end   expanded
3     1     1     3     1
7     2     4     10    1
6     3     11    16    1
2     4     17    18    2
1     5     19    19    2
8     6     20    27    2
                        2
                        2
                        2
                        2
                        3
                        3
                        3
                        3
                        3
                        3
                        4
                        4
                        5
                        6
                        6
                        6
                        6
                        6
                        6
                        6
                        6

Answer 2

To create your multiset in Column D, enter the following in D2:

=B2

Enter the following in D3 and fill down until you get a '#REF!' error:

=IF(COUNTIF(D$2:D2,D2)=INDEX($A$2:$A$7,MATCH(D2,$B$2:$B$7,0)),INDEX($B$2:$B$7,MATCH(D2,$B$2:$B$7,0)+1),D2)