Medium with sumif formula help

alloakland1

New Member
Joined
Dec 9, 2013
Messages
19
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
...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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,774
Messages
6,121,495
Members
449,034
Latest member
Raygers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top