Search code examples
excelaveragefrequencymedianmode

How to calculate average etc from table containing frequences of values?

In an Excel table, one column contains grades from 4 to 10. Another column contains the number of each grade. How do I calculate the median, mode, and average of the grades?

I know I can calculate those by listing each grade and then using appropriate functions. I would like to know if there are functions in Excel which calculate these statistics from a table like this.

Table

I tried functions AVERAGE, MEDIAN and MODE.SNGL. I expected them not to work and was right.

Solution

  • Assuming your data is in A2:B8 (excluding headers), generate this array:

    =LET(
    ζ,A2:B8,
    ξ,TAKE(ζ,,-1),
    κ,SEQUENCE(ROWS(ξ)),
    INDEX(TAKE(ζ,,1),XMATCH(SEQUENCE(SUM(ξ)),MMULT(N(κ>=TOROW(κ)),ξ),1))
)

    which you can then pass to your desired function(s).