Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
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)
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.