Calculating Median value across two sheets

Hello! I am working with two sheets. One sheet contains all active requests and the other contains all fully completed requests. Both sheets contain a column for "# days to complete" Task A and also the Year + Quarter that that task was completed. I'd like to find the median days to complete Task A per quarter.

Any suggestions for the best formula to do this?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alison C

    There is a MEDIAN function you can use to evaluate a range, and you can combine it with the COLLECT function to filter down based on criteria (ex. Task A and Quarter). However, since you're looking at two sheets you'll need to find two different Median values - one for Sheet A and one for Sheet B.

    See: MEDIAN function / COLLECT Function / Cross-sheet formulas

    Here's an example of how you'd write the cross-sheet formula for one sheet:

    =MEDIAN(COLLECT({Days to Complete}, {Task Names}, "Task A", {Year and Quarter}, "Year and Quarter"))

    Let me know if this makes sense! If not, it would be helpful to see screen captures of each sheet, but please block out sensitive data.



  • Thank you @Genevieve P. I was thinking of the COLLECT function but I wasn't sure if it will give the true median of the entire data set or will this give me an average of the two medians?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alison C

    You will need to write two formulas, one for each data set. The first =MEDIAN(COLLECT will give you the Median of one sheet. The second =MEDIAN(COLLECT will give you the Median for the other sheet.

    If you wanted the Median for the entire set, you would need to combine all that data into one sheet/column in order to evaluate it... perhaps one large total sheet? Then you could use a Report to split out the Active / Completed requests?

  • Hi @Genevieve P. looks like I have some negotiating to do with the team. Thanks for confirming!