Medium with sumif formula help

[alloakland1]

Hi, I have the following array formula, but it is not working. Any ideas would be much appreciated!
I press crtl,shift,enter but a #NUM error shows up.

=MEDIAN(IF('Sheet2'!A1:A5000=(CONCATENATE(a1,d2)),'Sheet3'!$BF1:$BF5000))/60

 

[Teeroy]

You haven't completed the IF statement, it has an expression for TRUE, but not FALSE so when it evaluates FALSE it will return FALSE which MEDIAN can't handle. I'm not sure that even putting a ",0' at the end of the if will help as the zeros will still be treated as members of the set for the median.

If it is a one off I would Autofilter the data to only show the condition then copy and paste the visible data to a new location to get the median.

 

[Trouttrap2]

Hi, I have the following array formula, but it is not working. Any ideas would be much appreciated!
I press crtl,shift,enter but a #NUM error shows up.

=MEDIAN(IF('Sheet2'!A1:A5000=(CONCATENATE(a1,d2)),'Sheet3'!$BF1:$BF5000))/60

It looks to me that you have too many parenthesis. You might try this version. You shouldn't need the false arguement in the IF statement because MEDIAN will ignore false returns in an array. {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!$BF1:$BF5000))/60}

CTL+SHIFT+ENTER the formula.

 

[alloakland1]

Hi, thank you for your help.

I put in this formula {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!$BF1:$BF5000))/60}, CTL+SHIFT+ENTER. This still gave me the #num error. any other ideas? I really appreciate your help. This is all new to me

 

[Trouttrap2]

Remove the quotes ' from the sheet reference. It should look like this:
=MEDIAN(IF(Sheet2!A1:A5000=CONCATENATE(a1,d2),Sheet3!$BF1:$BF5000))/60

If that doesn't work then walk through the formula using the formula evaluator. ALT+T+U+F

 

[Teeroy]

...because MEDIAN will ignore false returns in an array.

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.

 

[Aladin Akyurek]

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.

But it does.

 

[Aladin Akyurek]

Hi, thank you for your help.

I put in this formula {=MEDIAN(IF('Sheet2'!A1:A5000=CONCATENATE(a1,d2),'Sheet3'!$BF1:$BF5000))/60}, CTL+SHIFT+ENTER. This still gave me the #num error. any other ideas? I really appreciate your help. This is all new to me

A different point. How do you "know" Sheet2!A1:A5000 is associated with Sheet3!$BF1:$BF5000? That is, why are they on different sheets?

 

[Teeroy]

Aladin, point taken. When tested I passed arguments directly but after your post I double checked by reference and found something strange; when you put the arguments directly into the formula the FALSE isn't ignored, only when they are referenced.
i.e. =MEDIAN(FALSE,FALSE,FALSE,1,2,3) give 0.5 while =MEDIAN(C1:C6) referencing the same values gives 2 (in XL2003).

I confirmed on MSDN that this is expected but it is certainly not intuitive.

 

[Trouttrap2]

A quick check shows MEIDAN doesn't ignore false, it treats it as a zero which will still skew the result of the formula after you get it functional.

This is true if the formula is not array entered. If you CSE the formula, it ignores all the FALSE values; at least in my attempts. It won't treat it as zero.

For example: =MEDIAN({FALSE,FALSE,FALSE,1,2,3}) gives 2
=MEDIAN(FALSE,FALSE,FALSE,1,2,3) gives 0.5