Calculating Median value across two sheets

Alison C

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.

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.

Cheers,

Genevieve

Alison C

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.

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?

Alison C

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