Search code examples
excelmedianmode

Median and mode in excel using multiple columns

I am working on a spreadsheet for my statistics class. It contains a column filled with a quantity and a column filled with a frequency for each quantity. Using these two columns and several formulas, the mean, mode, median, coefficient variation, quartiles, interquartile range, minimum and maximum values and standard deviation can be found.

The issue is that to shorten the list of quantities, the use of the frequencies column comes in. Take the quantity list of : 10, 12, 14, 15, 18, 18, 10.

Notice that 18 and 10 have been repeated. Instead of repeating those quantities in my quantities column, I increment the frequency of that quantity from 1 to 2. The reason for doing it this way is due to very large lists of quantity data. Meaning I might have 5000 quantities that could potentially be reduced to 20 quantities where each quantity will have a larger frequency.

The issue is that the built-in Median and Mode function use only 1 column or row and do not account for the frequency of each quantity.

My question is this: Is there a way to use both columns to calculate the median and mode? Below is an example of what I was explaining. Additionally, my column containing quantities and frequencies may not be filled from start to end, meaning there maybe empty entries. 

Quantity  |  Frequency
  10      |     4
  12      |     6
  11      |     3
  15      |     1
  18      |     10

VS.

Quantity
10
10
10
10
12
12
12
12
12
12
11
11
11
15
18
18
18
18
18
18
18
18
18
18
Solution

  • If one has Office 365 with the dynamic array formula use:

    =MEDIAN(INDEX(A2:A6,MATCH(SEQUENCE(SUM(B2:B6),,0),SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))

    and 

    =MODE.SNGL(INDEX(A2:A6,MATCH(SEQUENCE(SUM(B2:B6),,0),SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))

    enter image description here

    If not then this array formula:

    =MEDIAN(INDEX(A2:A6,MATCH(ROW($ZZ1:INDEX($ZZ:$ZZ,SUM(B2:B6)))-1,SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))

    and

    =MODE.SNGL(INDEX(A2:A6,MATCH(ROW($ZZ1:INDEX($ZZ:$ZZ,SUM(B2:B6)))-1,SUMIF(OFFSET(B1,0,,ROW(B2:B6)-MIN(ROW(B2:B6))+1,),"<>"))))

    Being array formula, then need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here