Median with criteria

aecross

Hi,

I am trying to figure out the median number of days a site is in construction IF it is a Tier I, II, or III.

I am referencing another sheet. Range 6 is referencing a column that has each site's construction duration. Range 7 is the column the lists the sites tier level.

=MEDIAN({REFRESH - MASTER TRACKING SHEET Range 6}, IF({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

I've tried a handful of variations, with and without the IF statement, but I've had no luck.

I appreciate any and all help!

Samuel Mueller

@aecross try using a collect formula

=median(collect({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

aecross

Hi Samuel,

I tried that but am still receiving an #INCORRECT ARGUMENT SET error.

Nick Korna

Hi @aecross ,

You can use the COLLECT function in combination with the MEDIAN here:

=MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6},{REFRESH - MASTER TRACKING SHEET Range 7},"Tier I")

If you're using the Tiers in another column, you can substitute the "Tier I" for the [Column ID]@row instead.

Hope this helps; if you've any questions etc. then just ask!

Samuel Mueller

When you chose the ranges you chose the entire columns correct, and both ranges are on the same sheet?

aecross

@Samuel Mueller, yes, that is correct.

Samuel Mueller

@aecross My best guess would be to double check your cross sheet references and make sure you selected the entire column for both. Incorrect argument set sometimes means you wrote your formula wrong (parenthesis in wrong place maybe) or your range lengths do not match. I've had cross sheet references reset before I hit save and I have to go back and update them.

In my test the syntax for the formula is correct in theory written as

=median(collect({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

aecross

@Samuel Mueller here is the formula i'm using: =MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I"))

When I am referencing, I am clicking the column name so the entire column is highlighted. Would it be an issue that some of the cells in both columns are blank? Or that Range 6 is a formula (cell formula, not column formula) and Range 7 is not?

Samuel Mueller

@aecross those things shouldn't matter, what do you get with the formula

=countifs({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") ?

Samuel Mueller

Also try this and let me know what you get:

=sumifs({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I")

Ipshita

Hello @aecross to add to excellent responses from @Samuel Mueller I recommend wrapping your formula in the =IFERROR statement - that always works for me!

Cheers,

Ipshita

aecross

=countifs({REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") - I got "82" for this.

=sumifs({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 7}, "Tier I") - i got "2692"

aecross

I am still receiving #INCORRECT ARGUMENT SET with the IFERROR...

=IFERROR(MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 8}, "Tier I")))

This is a tricky one, thank you all for your help!

Samuel Mueller

Well @aecross I am at a loss. the median/collect formula should work. I have the same concept set up in a test sheet with no issues. maybe try wrapping it in an iferror like @Ipshita suggests.

Samuel Mueller

It would be =IFERROR(MEDIAN(COLLECT({REFRESH - MASTER TRACKING SHEET Range 6}, {REFRESH - MASTER TRACKING SHEET Range 8}, "Tier I")),"")

with the ifferor

Samuel Mueller

also you switched to range 8 not range 7