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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 11, 2010
Messages
612
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

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

Well-known Member
Joined
May 11, 2010
Messages
612

ADVERTISEMENT

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

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
...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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 11, 2010
Messages
612
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:

Forum statistics

Threads
1,137,292
Messages
5,680,645
Members
419,923
Latest member
Kalthus

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
Top