Median formula with multiple imbedded IF functions

[willow1985]

I am looking to find the Median of a data set but I only want the formula to consider certain cells.

The Median number I want found is in the column called [DTQ]
The only DTQ numbers I want it to consider are ones that are not labeled as Exclude in the [Customer Exlusions] Column, and have a Date Quoted between the two dates listed in E1 and F1

I wrote the below formula but I get a #NUM! error....

={ROUND(MEDIAN(IF(WO[Customer Exclusions]<>"Exclude",IF(WO[Date Quoted],">="&$F$1,IF(WO[Date Quoted],"<="&$E$1,WO[DTQ])))),0)}

Any help would be appreciated!

Thank you

Example.xlsx
	A	B	C	D	E	F
1	Date Quoted	DTQ	Customer Exclusions		2020-08-01	2020-08-20
2	2020-08-20	3
3
4	2020-08-20	4
5	2020-08-20	4
6			Exclude		Median:	#NUM!
7						5
8	2020-08-18	2
9	2020-08-18	2
10	2020-08-18	7
11	2020-08-13	2
12	2020-08-17	6
13	2020-08-18	7
14	2020-08-19	8	Exclude
15	2020-08-13	2
16	2020-08-20	10
17			Exclude
18	2020-08-18	9
19			Exclude
20	2020-08-17	11
21	2020-08-11	5
22	2020-08-18	14	Exclude
23	2020-07-13	5
24	2020-07-10	2
25	2020-07-14	6	Exclude
26	2020-07-14	6
27	2020-07-13	5
28	2020-07-10	2
29	2020-06-11	2
30	2020-06-17	8	Exclude
31	2020-06-11	2
Sheet1

Cell Formulas
Range	Formula
F6	F6	=ROUND(MEDIAN(IF(WO[Customer Exclusions]<>"Exclude",IF(WO[Date Quoted],">="&$F$1,IF(WO[Date Quoted],"<="&$E$1,WO[DTQ])))),0)
F7	F7	=ROUND(MEDIAN(B2:B5,B8:B13,B15:B16,B18,B20:B21),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
WOR	=Sheet1!$C$9	F6

Note: This is just an example, the original data set has over 22000 rows
Carla

 

[jasonb75]

Try changing ">="&$F$1 to >=$F$1 the double quote method is only used in countifs, sumifs, etc. Functions that don't have IFS in the name need to be done without.

edit:-

I just noticed that you have a couple of extra commas as well and your < and > should be the opposite way around once the formula is corrected, this should be right once it's array entered.

=ROUND(MEDIAN(IF(WO[Customer Exclusions]<>"Exclude",IF(WO[Date Quoted]<=$F$1,IF(WO[Date Quoted]>=$E$1,WO[DTQ])))),0)

 

[willow1985]

I get the following error:

 

[CA_Punit]

Try this

Book1
	A	B	C	D	E	F
1	Date Quoted	DTQ	Customer Exclusions		44044	44063
2	44063	3
3
4	44063	4
5	44063	4
6			Exclude		Median:	5
7						5
8	44061	2
9	44061	2
10	44061	7
11	44056	2
12	44060	6
13	44061	7
14	44062	8	Exclude
15	44056	2
16	44063	10
17			Exclude
18	44061	9
19			Exclude
20	44060	11
21	44054	5
Sheet1

Cell Formulas
Range	Formula
F6	F6	=ROUND(MEDIAN(IF((B2:B21)-IF(C2:C21="Exclude",B2:B21)>0,(B2:B21)-IF(C2:C21="Exclude",B2:B21),FALSE)),0)
F7	F7	=ROUND(MEDIAN(B2:B5,B8:B13,B15:B16,B18,B20:B21),0)

 

[willow1985]

Unfortunately I cannot set a range (B2:B21) - can this be a table reference instead? - WO[DTQ]

The original data is over 22000 rows long and can vary

 

[CA_Punit]

Sorry there is an issue with the above, If you have 0 in DTQ then it will ignore it

 

[jasonb75]

I get the following error:

Was that before or after I edited my earlier reply?

I noticed that some additional changes were needed after posting so went back and added the formula to it with the changes already made.

 

[willow1985]

Was that before or after I edited my earlier reply?

I noticed that some additional changes were needed after posting so went back and added the formula to it with the changes already made.

I didnt notice your update! Hurray it works!!!

Thank you so much!

 

[CA_Punit]

Yes you can consider whole table column Instead of cell reference
and Try This

Book1
	A	B	C	D	E	F
1	Date Quoted	DTQ	Customer Exclusions		01/08/2020	20/08/2020
2	20/08/2020	3			5
3
4	20/08/2020	4
5	20/08/2020	4
6			Exclude
7
8	18/08/2020	2
9	18/08/2020	2
10	18/08/2020	7
11	13/08/2020	2
12	17/08/2020	6
13	18/08/2020	7
14	19/08/2020	8	Exclude
15	13/08/2020	2
16	20/08/2020	10
17			Exclude
18	18/08/2020	9
19			Exclude
20	17/08/2020	11
21	11/08/2020	5
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(MEDIAN(IF((A2:A21>=E1)*(A2:A21<=F1)*(C2:C21<>"Exclude"),B2:B21)),0)