How to calculate mean median and mode in excel 2016 Finding the Mean Enter the scores in one of the columns on the Excel spreadsheet (see the example below). The Microsoft Excel AGGREGATE function allows you to apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows. Result: Explanation: Excel uses the QUARTILE.EXC function to calculate the 1st quartile (Q 1 ), 2nd quartile (Q 2 or median… Article Summary X. Clear the Show Zero Values checkbox under Window Options. Can I have a median calculation for a cell (during pivottable browsing from excel) that would work similar to a sum or avg function (does not depend on another column but only to the filtered columns on the cell excluding blank values).