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

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:
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
``````
-
I have two dataset questions for you: first, are there a finite number of values to be considered and if not, are there upper/lower bounds to the values? – dav Apr 19 '12 at 16:56

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
``````
-
Sorry for delay; away from excel. I marked this correct as I could edit this one better than the other answer, not that I understand either. Both work, of course. This answer has the problem of not giving a good end value, but that can be worked out from the start/end values. It also handles a initial value of 0 :). Thanks for the answer. – Pod May 10 '12 at 14:56

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)
``````
-
Sorry for delay; away from excel. I marked the other answer correct as I could edit it better, not that I understand either. Both work, of course. This answer has the problem of not working if the initial (count, value) pair has a value of 0, though the same pair will work elsewhere in the sequence. Thanks for the anwer. – Pod May 10 '12 at 14:58