Dueling Excel - "MODE from Frequency Table": Podcast #1446

[shopite]

An option for the median: running total in A (A5=0, A14=53 in this case).

Then:

G6-G8 = 0-2

F6 = VLOOKUP(($A$14+G6)/2,$A$5:$C$1­3,3) (fill F7,F8)

E6 = IF(ISEVEN($A$14)=TRUE(),(F6+F8­)/2,F7)

[cmvpjones11]

mike, i'm stunned you didn't drop an insane (only decipherable using inline F9 evaluation) formula on us. Rather than “unwind” the values and since the values in column B were already sorted, I used a rolling total count in column A. So that C16 would be =IF(AND(SUM($A$16:A16)>=SUM($A­$16:$A$24)/2,SUM($A15:A$16)<SU­M($A$16:$A$24)/2),B16,"").

Then in the answer (highlighted) cell, you'd add a simple min function over the new range created in column C.

[ExcelIsFun]

@cmvpjones11 , VERY cool formula solution!!